Re: scheduler job_creator is DBA user account

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