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

Advertisements

Oracle passwords in 11g

In 11g Oracle supports password to be case sensitive. But the password can be case insensitive depending on value of “PASSWORD_VERSIONS” and “sec_case_sensitive_logon”.

In 11g dba_users view has a new column called “PASSWORD_VERSIONS”, it can have 3 possible values “10G”, “10G 11G”, “11G”. Below shows what they mean along with parameter “sec_case_sensitive_logon”.

Scenario when the value of password_versions in DBA_USERS is “10G 11G”, when the value “10G 11G” the column “PASSWORD” and “SPARE4” has a value
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

-- When the value of PASSWORD_VERSIONS is 10G 11G and sec_case_sensitive_login is TRUE, Oracle matches the case of the password.
SQL> show parameter sec_case_sensitive_logon;
NAME TYPE VALUE
———————————— ———– ——————-
sec_case_sensitive_logon boolean TRUE

SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect scott/tiger
Connected.
SQL>

-- When the value of PASSWORD_VERSIONS is 10G 11G and sec_case_sensitive_login is FALSE the password entered does not have to be case-sensitive.
SQL> alter system set sec_case_sensitive_logon=FALSE;

System altered.

SQL> connect scott/TIGER
Connected.

Scenario when password_versions is 11G – In this scenario the password is case-sensitive irrespective what the value of sec_case_sensitive_logon so the password is always case-sensitive. In this case the column “PASSWORD” is NULL and SPARE4 is not NULL
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

-- Shows example when sec_case_sensitive_logon is TRUE and password versions is 11G the password is case-sensitive irrespective the value of “sec_case_sensitive_logon”.
SQL> alter system set sec_case_sensitive_logon=TRUE;

System altered.

SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied

-- Shows example when set sec_case_sensitive_logon is FALSE when password versions is 11G that it only works when password is case-sensitive
SQL> alter system set sec_case_sensitive_logon=FALSE;

System altered.

SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect scott/tiger
Connected.

Scenario when password_versions is 10G – In this scenario the password is not case-sensitive. In this case in sys.user$ “PASSWORD” is NOT NULL and SPARE4 is NULL.
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

-- Shows the password is not case sensitive so changing the parameter sec_case_sensitive_logon has no effect when password is entered for SCOTT
SQL> alter system set sec_case_sensitive_logon=true;

System altered.

SQL> connect scott/TIGER
Connected.

SQL> show parameter sec_case_sensitive_logon;

NAME TYPE VALUE
———————————— ———– ——-
sec_case_sensitive_logon boolean FALSE
SQL> connect scott/TIGER
Connected.

ORA-28221: REPLACE not specified

When a user name has a profile that has a password verify function set and the user tries to change the password using “ALTER USER” without the REPLACE clause, and also the user doesn’t have ALTER USER privilege Oracle will generate the “ORA-28221: REPLACE not specified” error message. So the user needs to include the “REPLACE” clause.

Example:
ALTER USER <username> IDENTIFIED BY <new password> REPLACE <old password>