Sometimes, we need to sign-on as a specific user to understand the exact nature of their problem.While it is easy to alter the user ID to make a new password, this is an inconvenience to the end-user because they have to re-set a new password . However, as DBA we can extract the encrypted password from the dba_users views, save it, and re-set the password after we have finished with testing .
For example, assume that we need to sign-on as HARRY user and test their Oracle privileges:
We perform the following steps:1.) Extract the encrypted password
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 = 'HARRY';
OLD_PASSWORD
--------------------------------------------------------------------
alter user "HARRY" identified by values '15EC3EC6EAF863C' ;
2.) Change HARRY’s password and sign-on for testing and perform all testing
SQL> alter user HARRY identified by harry;
User altered .
SQL> conn harry/harry@noida
Connected
3.) Reset the same Old Password
When we have completed the testing we can set-back the original encrypted password using the output from the below query as
SQL> alter user "HARRY" identified by values '15EC3EC6EAF863C' ;
Enjoy :-)
No comments:
Post a Comment