A Word on Passwords...

Posted at Monday, June 06, 2005
I've been getting a lot of questions lately about preserving passwords in Oracle, especially after cloning or "refreshing" environments (i.e. prod to test). In most cases, "refreshing" aims to make environments relevant data wise. For many reasons - not the least of which is security - authentication information, such as passwords, should not be propagated to dev/test environments.

To deal with this challenge, I normally run a script on the target environment that extracts all the current passwords in the form of a dynamically generated SQL script. I then clone the database as appropriate. After words, I run the script I created to reset the users' passwords to their original values, overwriting the cloned, production values.

The following script extracts the passwords from a database (edit as appropriate):

set pages 0;
set feedback off;
spool &file_name;
select 'ALTER USER '||chr(34)||username||chr(34)||' IDENTIFIED BY VALUES
from dba_users;
spool off;

After the clone, call the script:

sqlplus "/ as sysdba" @file_name