Groby Computers Ltd - Leicester
Oracle Database Administration
Logging in as a user without knowing their password.
Problem:
========
It happens from time-to-time that a DBA needs to login as a user but does not know their password.
In 9i the process was:
SQL> create table save_pw as select password from dba_users where username = 'SCOTT';
SQL> alter user SCOTT identified by TIGER;
SQL> connect SCOTT/TIGER
Then when done the old password could be restored using ...
SQL> select * from save_pw;
PASSWORD
------------
A412C0B112B7
SQL> alter user SCOTT identified by values 'A412C0B112B7';
Nowadays, that is to say from 10gR2 there is a method that doesn't rely on changing the users' password
and won't affect or be affected by the users' password history.
SQL> connect / as sysdba
Connected.
SQL> create user su identified by "proxy.123";
SQL> grant create session to su;
SQL> alter user scott grant connect through su;
SQL> connect su[scott]/"proxy.123"
Connected.
SQL> show user
USER is "SCOTT"
Page Updated Thu Oct 13 21:43:39 BST 2011
