Simon Krenger

One problem that occasionally pops up is that a DBA needs to recreate a user with a password that he does not know. This happened to me when I had to create a schema with the same password on a development database. After I set an initial password for the developer, he exclaimed that he wanted the same password that he had for the schema X, but he did not want to tell me the password (don’t ask here, that’s a completely other story).

One way to do this is to use ALTER USER ... IDENTIFIED BY VALUES. Using the excellent article on the ALTER USER commands from Laurent Schneider, we can generate a dynamic SQL query to set a password without knowing the password itself!

Here is the query to read the password from sys.user$ from Laurents article:

SQL> alter user u identified by values 'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934DABBB809A4;18FE58AECB6217DB';

So using dynamic SQL, we can construct the following example to recreate the user with a password that we do not know:

SQL> SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='SIMON';

'ALTERUSER'||NAME||'IDENTIFIEDBYVALUES'''||SPARE4||';'||PASSWORD||''';'
----------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER USER SIMON IDENTIFIED BY VALUES 'S:ADEB92DE98CCE3A01033BFE530092B43AD1AE394220C93BA4ED3813C05C6;B0334ACB686B0325';