Most DBAs have to execute scripts as the schema owner for them to work correctly. While the traditional method of logging in as that user works fine most of the time. There are times when one does not know the password or have access to the secure location containing the password. Being the DBA we have some options, we can change the password, but this could break other programs. We can retrieve the password hash and change the password execute the scripts and return the password back to the hash value.
If we are using Oracle 10.2 or higher we have the option to use the proxy option. we can grant our DBA account privileges to the schema account and not have to know the password.
alter user schemaowner grant connect through dbauser;
Then the DBA can connect to the schema user by
sqlplus> connect dbauser[schemaowner]@mydatabase
Performing a show user at the SQL*Plus prompt will return
show user
schemaowner
This should be the preferred option for DBA access when needing to run scripts for patches and upgrades when access to the password is not available.
In Oracle 11g the password hash is not viewable through the dba_users view, so using the has value trick is soon to be phased out and is not a great practice anyway.
No comments:
Post a Comment