How many times has that command saved a DBA from the chore of polling developers and users for passwords in order to test and troubleshoot urgent issues…?
Well with the introduction of enhanced security in Oracle Database 11g, that simple script that could effectively ’su’ to another user, then reset their password back will no longer work……
This classic ’su.sql’ script recorded the ‘password’ column value from DBA_USERS for the user in question, then temporarily changed the user’s password, logged in as the user, and then immediately reset the password back to the original value, leaving the DBA connected as the user in question.
Here is that old script (N.B. this is still very useful for pre-11g databases!) :-
N.B. Wordpress auto-formatting will helpfully spoil any attempt to cut-paste straight into putty - so you may need to replace the ‘quotes’ :o)
——————————————————————–
– su.sql
–
– Script to log onto another user’s account - requires to be run under a user with the DBA role
–
– Parameters to pass: name of the target user - e.g. @su.sql <USERNAME>
–
set head off
set feed off
set verify off
set pages 0
set termout off
spool user.sql
select ‘alter user ‘||username||’ identified by values ”’||password||”’;’ from dba_users where username=upper(’&&1′); spool off
alter user &&1 identified by temppswd;
connect &&1/temppswd
@user.sql
set head on
set feed on
set verify on
set pages 24
set termout on
——————————————————————–
In Oracle 11g, the password field of the DBA_USERS view is no longer populated. Instead we must adapt the script to utilise two columns from SYS.USER$; the old DES encrypted password field and also a new SHA-1 encrypted hash:
——————————————————————————
– su_11g.sql
–
– 11g Script to log onto another user’s account - requires to be run under a user with
– the SYSDBA role
–
– Parameters to pass: name of the target user - e.g. @su.sql <USERNAME>
–
set head off
set feed off
set verify off
set pages 0
set termout off
spool user11g.sql
select ‘alter user ‘||name||’ identified by values ”’||spare4||’;'||password||”’;’
from sys.user$
where name=upper(’&&1′);
spool off
alter user &&1 identified by temppswd;
connect &&1/temppswd
@user11g.sql
set head on
set feed on
set verify on
set pages 24
set termout on
0
Back to top