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.
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.