Change user/schema password temporarily

Friday, November 14, 2008

We usually don't have the knowledge about user passwords in database.
Only we could get is encrypted passwords from views below:

Version < 11g


SQL> select username,password from dba_users;


Version 11g
SQL> select name,password from sys.user$;


But what if we get ourselves in situation that we have to login as one of the users, do some work, and leave the current password as it was after we finish our work.

Here is simple demo case.




First I'll create test user and grant him create session privilege.
SQL> create user testusr identified by current_pwd;

User created.

SQL> grant create session to testusr;

Grant succeeded.

SQL> connect testusr/current_pwd
Connected.


Next I'll search for encrypted version of password and copy it.
SQL> connect system
Enter password: ******
Connected.
SQL>
SQL>
SQL> select name, password
2  from sys.user$
3  where name = 'TESTUSR';

NAME                           PASSWORD
------------------------------ ------------------------------
TESTUSR                        1C52E1C5D7306B70



Now I'll change current password, login using new one and do my work.
SQL> alter user testusr identified by new_password;

User altered.

SQL>
SQL> connect testusr/new_password
Connected.
--
--> Doing some work!


After I finish my work, I'll just return old password using encrypted version of password that I copied before.
SQL> connect system
Enter password: ******
Connected.
SQL>
SQL>
SQL> alter user testusr identified by values '1C52E1C5D7306B70';

User altered.



Just to show you that I correctly returned old password.
SQL> conn testusr/current_pwd
Connected.


Clean up.
SQL> connect system
Enter password: ******
Connected.
SQL> drop user testusr;

User dropped.


Share/Bookmark

0 comments:

Post a Comment