Backup and reset the user password in Oracle 12c

Reset the user password in Oracle different version 10g, 11g, 12c with password versions

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';
USERNAME  PASSWORD_VERSIONS
--------  -----------------
TEST      11G 12C

Script for Backup the user password for all version

---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;

General 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:9F949D01E7E6F09B0AE4519B5FFDF5ABCD6FD137C76A27A2C1441C635D7517787B3FB4C969D5DA10F40AA16A0C3B0AC94AAA81DB1A7458A64525CEC8A45C66FAD67459A487C2505F8377E659EB43ED9B

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.

 --Execute for set the 11g password 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

--Execute for set the 11g,12c password but it modified version parameter also.
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:
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.