Many a time as a DBA you have to run scripts and do things into an application schema, having to remember and store a huge number of account passwords (and keep looking them up) is a real pain, so how can we deal with this in a better way.
There seem to be four main approaches that I've seen adopted to try and avoid having to do this - these are:
1) Hardcode everything with schema names - simple enough to understand , every single DDl/DML statement that is being run has the schema name in front of every object reference - therefore can be run as any user with appropriate rights - no need to know the password at all.
2) Set the current_schema to be the schema you want to deploy to - similar to 1), however hardcoding is not needed a simple alter session command is run by the DBA before running any scripts this then automatically prepends that schema name on to every object reference.
3) change the schema password temporarily - for the duration of the deployment the original encrypted password is copied, the password changed to some temporary value and then changed back to the encrypted version
4) Use proxy accounts - this allows using a special connect syntax and when you have been given the correct rights a direct login as another user using your own password.
Now the problem with 1) and 2) which may not be immediatly apparent is that you are not actually logged in as the application account. This means a few things will not work - for example:
a) database links cannot be created in the application schema
b) rename of objects cannot be done
c) use of the 'USER' function returns the logged on user not the application user
So other than the simplest use of deployments 1 + 2 are ruled out.
Option 3) was my preferred approach back from oracle 7 days onwards, you temporarily become the application user and then switch the password back. This works pretty well but is a bit fiddly and you could always make a mistake - it also means that during the deployment no-one other than you can connect (which could be a bad or good thing depending what your setup is and if the deployment is meant to be 'online')
Option 4) is by far the best but seems to be one of the least used features in my experience, it's been around since 9.2 believe it or not - i think many people may have dismissed it as some sort of middleware pass through use thing and not actually realised it's usefulness.
Here is a quick example of how it works.
SYS@DB>create user demo identified by demo;
User created.
SYS@DB>grant create session to demo;
Grant succeeded.
SYS@DB>create user deployer identified by deployer;
User created.
SYS@DB>grant create session to deployer;
Grant succeeded.
SYS@DB>alter user demo grant connect through deployer;
User altered.
SYS@DB>conn deployer[demo]/deployer
Connected.
DEMO@DB>sho user
USER is "DEMO"
DEMO@DB>
So here we can see that the account deployer has been allowed to connect as 'demo' using the special [] syntax in the connect string as it has the 'connect through privilege.
I really recommend this as the best way of addressing this issue, it's simple, doesn't involve storing passwords anywhere can deploy anything and can't break the system while deployments are being done.
Comments
Post a Comment