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