Author:  Van Buren

Sometimes it happens that we need to switch from one database user to another. We may need to switch from one database user to another on local database, or we may need to log in to user account without knowing the password in order to make some change or we may need to change a number of users from the script without including their passwords. In this article we will give you some great tips that will help you switch to another Oracle user and back.

Oracle IDENTIFIED BY VALUES Clause:

Oracle IDENTIFIED BY VALUES Clause is a commonly known but undocumented feature of Oracle CREATE USER and ALTER USER commands. Generally you use below command to create a user by specifying password.

CREATE USER MYUSER IDENTIFIED BY "myPassword"

Oracle stores one way hash of password in PASSWORD column of DBA_USERS table and you can create the user by using this hash value as follows

CREATE USER MYUSER IDENTIFIED BY VALUES 'F5238775A9CDA5F7'

This feature is especially useful when you want to migrate from one server to another by preventing while preventing each user re-enter their passwords in the new server.

Switch Oracle Users and Back:

Below script can help you alter user password, make the changes you need and then reset the password back to another user. This is useful when you want o log into user account without knowing the password for making changes such as granting privilege on a user's table to another user.

Set all users to given password:

Below script will generate password change script as setMyPassword.sql that can be used to set all users to password MYPASSWORD.

SPOOL setMyPassword.sql
SELECT 'ALTER USER ' || USERNAME || ' IDENTIFIED BY MYPASSWORD;' 
FROM DBA_USERS;
SPOOL OFF

setMyPassword.sql will contain the script as follows

ALTER USER SYS IDENTIFIED BY MYPASSWORD;
ALTER USER SYSTEM IDENTIFIED BY MYPASSWORD;
ALTER USER ELAN IDENTIFIED BY MYPASSWORD;
ALTER USER DES IDENTIFIED BY MYPASSWORD;

Log In with New Password:

Now we can log in to user account by using below commands and make the changes as required.

CONNECT SYS/MYPASSWORD
----make changes

CONNECT SYSTEM/MYPASSWORD
----make changes

CONNECT ELAN/MYPASSWORD
----make changes

CONNECT DES/MYPASSWORD
----make changes

Switch Back Users:

Below script will generate password reset script as resetMyPassword.sql that can be used to switch back all users to their original passwords.

SPOOL resetMyPassword.sql
SELECT 'ALTER USER ' || USERNAME || ' IDENTIFIED BY VALUES ''' || PASSWORD || ''';'
FROM DBA_USERS;
SPOOL OFF

resetMyPassword.sql will contain the script as follows

ALTER USER SYS IDENTIFIED BY VALUES 'F93D38A08E9EF8A4';
ALTER USER SYSTEM IDENTIFIED BY VALUES 'D901BF511FD627B8';
ALTER USER ELAN IDENTIFIED BY VALUES 'BB2DFB5D306FF07F';
ALTER USER DES IDENTIFIED BY VALUES 'A59EBAB264D123EE';

Now we will immediately run the resetMyPassword.sql script to put the passwords back.

One thing that you should keep in mind is that users will not be able to log in to their account while the script is running until the passwords are successfully reset to their original values. Also remember that passwords are case-sensitive in Oracle 11g so you can use SEC_CASE_SENSITIVE_LOGON to enables or disable the case sensitivity of password in the database. SEC_CASE_SENSITIVE_LOGON is modifiable dynamically by alter system set command. Setting its values to TRUE makes database logon passwords case-sensitive and setting its FALSE makes the database logon passwords case-insensitive. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Speed Up Oracle Data Loading!!
   Oracle10g Wait Event Tuning
   Exceptional Tips for Exceptions in Oracle
   Efficient Tips for Oracle RAC Listener!!