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.

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