Friday, 21 July 2017

Oracle : Retain password of schema

Some time we need to change password for schema for temp reasons, here is is a method how to deal with it:

07:01:58 SQL> 07:01:58 SQL> select
'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password
from dba_users
where username ='&username';07:02:11   2  07:02:11   3  07:02:11   4
Enter value for username: SCOTT
old   4: where username ='&username'
new   4: where username ='SCOTT'

OLD_PASSWORD
------------------------------------------------------------------------------------------------------------
alter user "SCOTT" identified by values 'F894844C34402B67';

07:02:17 SQL> conn scott/tiger
Connected.
07:03:42 SQL> alter user scott identified by lion;

User altered.

07:03:59 SQL> conn scott/lion
Connected.

07:04:31 SQL> alter user "SCOTT" identified by values 'F894844C34402B67';

User altered.

07:04:52 SQL> conn scott/lion
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
07:05:03 SQL> conn scott/tiger
Connected.
07:05:12 SQL>

Using this method one can actually maintain passwords for safe keeping without actually breaking security and recover from accidental password changed.
Note: Method doesn't work across databases. No method so far to decipher password so far.