Monday, 18 April 2016

Oracle: Save schema password before refresh or any other purpose

One can save password for a schema using method below:
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 ='MONKEY';

See my experiment for clarity :
------------------------------------------------------------------------------------------------------------------------
12:04:13 SQL> 12:04:13 SQL> 12:04:13 SQL> conn / as sysdba
Connected.
----create the user
12:04:21 SQL> grant connect,resource to monkey identified by monkey123;

Grant succeeded.

Elapsed: 00:00:03.84
12:04:27 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 ='MONKEY';12:05:05   2  12:05:05   3  12:05:05   4  12:05:05   5  12:05:05   6

OLD_PASSWORD
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter user "MONKEY" identified by values '41A09E4762B188D3';

Elapsed: 00:00:01.71
12:05:08 SQL> alter user monkey identified by man;

User altered.

Elapsed: 00:00:00.02
12:05:50 SQL> alter user "MONKEY" identified by values '41A09E4762B188D3';

User altered.

Elapsed: 00:00:00.00

12:06:29 SQL> conn / as sysdba
Connected.
12:06:42 SQL> drop user monkey;

User dropped.

Elapsed: 00:00:07.48
12:07:00 SQL> grant connect,resource to monkey identified by monkey;

Grant succeeded.

Elapsed: 00:00:00.05
12:07:12 SQL> alter user "MONKEY" identified by values '41A09E4762B188D3';

User altered.

Elapsed: 00:00:00.01
12:07:20 SQL> conn monkey/monkey123
Connected.
12:07:30 SQL> conn / as sysdba
Connected.
06:10:37 SQL> drop user monkey;

User dropped.

Elapsed: 00:00:01.04
06:10:50 SQL> grant connect, resource to chimp;
grant connect, resource to chimp
                           *
ERROR at line 1:
ORA-01917: user or role 'CHIMP' does not exist


Elapsed: 00:00:00.02
06:11:18 SQL> grant connect, resource to chimp identified by chimp;

Grant succeeded.

Elapsed: 00:00:00.11
06:11:38 SQL> alter user chimp identified by values '41A09E4762B188D3';

User altered.

Elapsed: 00:00:00.03
06:12:06 SQL> conn chimp/monkey123;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
-----Monkeys PASSWD doenot work for CHIMP.
06:12:18 SQL> conn / as sysdba
Connected.
06:12:31 SQL> drop user chimp;

User dropped.

Elapsed: 00:00:00.21
----------------------------Enjoy!!!----------------

No comments:

Post a Comment