Re: scheduler job_creator is DBA user account

Thanks Chris I like that idea and it is already very close to what we do currently so i think we can implement that with a few modifications.

On Thu, Mar 9, 2023 at 3:06 PM Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> One thing we did was coach our developers to include a connect string in
> their rollouts.
>
> Then we used a centralized rollout user called "RELEASE_USER" (made
> up/redacted ID) that _ALL_ rollouts were deployed as. Then if their
> rollout included scheduler jobs, they knew to add the proxy login id to the
> connect string. (Note the caveat below however)
>
> So at the top of all the rollout scripts would be a :
>
> CONNECT release_ddl_user/&&db_password_at_&&db_id
>
> The variables for DB_PASSWORD and DB_ID were included in an ENV file the
> DBAs had in their deployment environment that defined those variables so
> developers didn't have the passwords, just the variables. Our ENV file
> controlled setting those variables to appropriate values as part of the
> deployment.
>
> The BIG caveat to this is that all deployments had to use to use proper
> schema<.> (schema dot) notation for the objects referenced and a lot of
> developers get spoiled and don't like having to do that even though its
> safer/cleaner.
>
> So for example lets say we had JIRA-12345 ticket that had a rollout. We'd
> pull it down in git.
>
> In our deployment directory would be a .sql file that would define the
> variable values. Then you would just append the ROLLOUT.SQL names to the
> bottom of that .sql script and run the master.sql script that sets the
> variable values, and then proceeds to run the rollout.sql scripts.
>
>
> Example master.sql
> DEFINE db_id=ORCLDB1
> DEFINE db_password=THX1138Rulez! (made up password :-p)
> _at_rollout1.sql
> _at_rollout2.sql
>
>
> Then you just run master.sql. The deployment user is always RELEASE_USER
> and sometimes it might be RELEASE_USER[APP_USER] if they were releasing new
> jobs.
>
> HTH
> Chris
>
>
> On Thu, Mar 9, 2023 at 4:16 PM Jeff Chirco <backseatdba_at_gmail.com> wrote:
>
>> Thanks for the proxy idea, forgot about that. This could work to clean up
>> our existing jobs but it doesn't help with our somewhat automated rollouts.
>> Currently the developers give us a script we run in production which points
>> to multiple scripts for each object that compiles multiple packages and
>> creates jobs. So everything gets run under our accounts. It is not fully
>> automated yet but we plan to at some point.
>>
>> On Thu, Mar 9, 2023 at 1:34 PM Chris Taylor <
>> christopherdtaylor1994_at_gmail.com> wrote:
>>
>>> Use a proxy account:
>>>
>>> ALTER USER <APP USER> grant connect through <DBA USER> ;
>>>
>>> Then when you connect , you connect as:
>>>
>>> DBA_USER[APP_USER]/DBA_USER_PASSWORD (where the account you're actually
>>> logging into is the "APP_USER" inside the brackets.
>>>
>>> Then any operation you run is run as the APP_USER and you don't need to
>>> know the password for the APP_USER that should own the jobs.
>>>
>>> HTH
>>> Chris
>>>
>>>
>>> On Thu, Mar 9, 2023 at 2:54 PM Jeff Chirco <backseatdba_at_gmail.com>
>>> wrote:
>>>
>>>> We have a lot of custom applications in our database all under locked
>>>> schema accounts. When we create scheduler jobs, the owner is the schema but
>>>> since the command gets run as a DBA account logged in the JOB_CREATOR is
>>>> the DBA that ran it. Which then when you look at active sessions while the
>>>> job is running it is that DBA user. Is there a way to have the JOB_CREATOR
>>>> be the schema instead? We dropped a DBA account then found out that all
>>>> the jobs he created for other schemas became disabled. Trying to avoid
>>>> this in the future.
>>>>
>>>> Thanks,
>>>> Jeff
>>>>
>>>

--
http://www.freelists.org/webpage/oracle-l