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@&&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)
@rollout1.sql
@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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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
