Changing Oracle password in 11g using ALTER USER IDENTIFIED BY VALUES

In 11g the Oracle the hash password is no longer stored in DBA_USERS, it is stored in SYS.USER$ table in the column “PASSWORD” and “SPARE4”. So there are different ways password can be set depending on if “PASSWORD” and “SPARE4” are set in SYS.USER$ and what you want PASSWORD_VERSIONS to be.

If only “SPARE4” is used to set the password the password version is 11g and in SYS.USER$ password value becomes NULL after ALTER statement
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
——————————————————————————–
F894844C34402B67
S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G 11G

SQL> alter user scott identified by values 'S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346';

User altered.

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
11G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
———————————————————————–

S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346

If both password and SPARE4 is used to restore the password, the PASSWORD_VERSIONS is “10G 11G”, to set this the IDENTIFIED BY VALUES is passed in as “SPARE4;PASSWORD” from sys.user$

SQL> alter user scott identified by values 'S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B;F894844C34402B67';

User altered.

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G 11G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
——————————————————————–
F894844C34402B67
S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B

If the value from PASSWORD is used from SYS.USER$ the password version is set to 10g and column SPARE4 is set to NULL
SQL> alter user scott identified by values 'F894844C34402B67';

User altered.

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
————————————————————————-
F894844C34402B67

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s