Backup and reset the user password in Oracle 12c

Backup and Reset the Password in Oracle different version 10g, 11g, 12c

Backup the password in Oracle from 12c, we need to know the version of backup supported by Oracle.

Check Version of Password in Oracle for user

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS WHERE USERNAME = 'TEST';

PASSWORD_VERSIONS
-----------------
11G 12C

Backup for all version of DB Password script

In 10g:
SELECT 'ALTER USER '||username||' IDENTIFIED BY VALUES '''||password||'';' end FROM dba_users;

Onward 11g:
SELECT 'ALTER USER '||a.username||' IDENTIFIED BY VALUES '''||b.spare4||''';'  FROM dba_users a,sys.user$ b WHERE b.name = a.username;

For generally Query included all password
As per version, it has both password for 11g and 12c as shown in following output (S or T parameter)

SQL> SELECT spare4 FROM sys.user$ WHERE name = 'TEST';

SPARE4
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S:02747A5C466B6E08BEA690283B6D6677F3A2CA17703E80A013BB1F0A3D51;T:9F949D01E7E6F09B0AE4519B5FFDF5ABCD6FD137C76A27A2C1441C635D7517787B3FB4C969D5DA10F40AA16A0C3B0AC94AAA81DB1A7458A64525CEC8A45C66FAD67459A
487C2505F8377E659EB43ED9B

For reset both 11g and 12c password with ALTER command
Keep in mind if you take complete backup then restore complete value in ALTER command, if you specify only one S or T then only that Oracle eliminate second version as shown in example below:
In my environment TEST user supported for 11g and 12c then output comes with S or T both value.

-- For both 11g and 12c password:
User altered.
SQL> alter user test identified by values 'S:02747A5C466B6E08BEA690283B6D6677F3A2CA17703E80A013BB1F0A3D51;T:9F949D01E7E6F09B0AE4519B5FFDF5ABCD6FD137C76A27A2C1441C635D7517787B3FB4C969D5DA10F40AA16A0C3B0AC94AAA81DB1A7458A64525CEC8A45C66FAD67459A487C2505F8377E659EB43ED9B';

Note: If you reset with one of the password instead of both of them then Oracle support only that version S: is used for 11g version.

--For specify one of them it execute successfully but it modified version parameter also.
SQL> alter user test identified by values 'S:02747A5C466B6E08BEA690283B6D6677F3A2CA17703E80A013BB1F0A3D51';
User altered.

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS WHERE USERNAME = 'TEST';

USERNAME             PASSWORD_VERSIONS
-------------------- -----------------
TEST                 11G

SQL> alter user test identified by values 'S:02747A5C466B6E08BEA690283B6D6677F3A2CA17703E80A013BB1F0A3D51;T:9F949D01E7E6F09B0AE4519B5FFDF5ABCD6FD137C76A27A2C1441C635D7517787B3FB4C969D5DA10F40AA16A0C3B0AC94AAA81DB1A7458A64525CEC8A45C66FAD67459A487C2505F8377E659EB43ED9B';
User altered.

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS WHERE USERNAME = 'TEST';

USERNAME             PASSWORD_VERSIONS
-------------------- -----------------
TEST                 11G 12C

Get more password detail in Oracle version wise
SPARE4 column of SYS.USER$ table has mre information
S: Password is used for 11g Version
T: password string is used for 12c onwards.
PASSWORD column used in SYS.USER$ for 10G environment.
For 10g, Find the Password Value:

SELECT password pwd_10g FROM sys.user$ WHERE name = 'TEST';
PWD_10G
----------------
AEB6397C8E7598A7

For 11g, Find the Password Value:

SELECT REGEXP_SUBSTR(spare4, 'S:[^;]+') pwd_11g FROM sys.user$ WHERE name = 'TEST';
PWD_11G
--------------------------------------------------------------
S:02747A5C466B6E08BEA690283B6D6677F3A2CA17703E80A013BB1F0A3D51

For 12c, find the password value:

SELECT REGEXP_SUBSTR(spare4, 'T:[^;]+') pwd_12c FROM sys.user$ WHERE name = 'TEST';
PWD_12C
--------------------------------------------------------------------------------
T:9F949D01E7E6F09B0AE4519B5FFDF5ABCD6FD137C76A27A2C1441C635D7517787B3FB4C969D5DA10F40AA16A0C3B0AC94AAA81DB1A7458A64525CEC8A45C66FAD67459A487C2505F8377E659EB43ED9B

Generate Script for particular user

select u.username
,'alter user '||u.username||' identified by values '''||s.spare4||''';' cmd
from dba_users u
join sys.user$ s
on u.user_id = s.user#
where u.username = upper('&username');

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.