或许你不知的ORACLE秘密 系列一

Oracle 11g introduces Case-sensitive passwords for database authentication. Along with this if you wish to change the password (temporarily) and reset it back to old , you will find that password field in dba_users is empty. Prior to 11g we could use following technique to change/restore password

SQL> create user liu identified by liu;User created.

SQL> grant create session to liu;Grant succeeded.

SQL> conn sys as sysdbaEnter password:Connected.

SQL> select username,password from dba_users where username='LIU';

USERNAME                           PASSWORD

------------------------------ ------------------------------

LIU                                       9DEC0D889E8E9A6B

SQL> alter user amit identified by abc;

User altered.

SQL> conn amit/abc

Connected.

SQL> conn sys as sysdba

Enter password:

Connected.

SQL> alter user LIU identified by values'9DEC0D889E8E9A6B';

User altered.

SQL> conn liu/liu

Connected.

In 11g if you query password field, itwill return NULL.

SQL> select username,password fromdba_users where username='LIU';

USERNAME                      PASSWORD

------------------------------------------------------------

LIU

Let’s first see Case-sensitive passwordfeature in 11g and then steps to change/restore passwords

SYS@orcl>create user LIU IDENTIFIED BYLIU;

用户已创建。

SYS@orcl>GRANT CONNECT TO LIU;

授权成功。

SYS@orcl>conn liu/liu

ERROR:

ORA-01017: invalid username/password;logon denied

警告:您不再连接到 ORACLE。

@>CONN LIU/LIU

已连接。

LIU@orcl>

This behavior is controlled by“sec_case_sensitive_logon”initialization paramter. If the value is true then it will enforce casesensitive passwords

LIU@orcl>conn / as sysdba

已连接。

SYS@orcl>SHO PARAMETER PFILE

NAME                                TYPE       VALUE

----------------------------------------------- ------------------------------

spfile                              string    \opt\DBHOME_1\DATABASE\SPFILE

                                               ORCL.ORA

SYS@orcl>sho parameter sec_case_sensi

NAME                                TYPE       VALUE

----------------------------------------------- ------------------------------

sec_case_sensitive_logon            boolean    TRUE

SYS@orcl>alter system setsec_case_sensitive_logon=false;

系统已更改。

SYS@orcl>conn liu/liu

已连接。

LIU@orcl>alter system setsec_case_sensitive_logon=true;

alter system setsec_case_sensitive_logon=true

*

第 1行出现错误:

ORA-01031:权限不足

LIU@orcl>conn / as sysdba

已连接。

SYS@orcl>alter system setsec_case_sensitive_logon=true;

系统已更改。

SYS@orcl>conn liu/LIU;

已连接。

LIU@orcl>conn liu/liu

ERROR:

ORA-01017: invalid username/password; logondenied

警告:您不再连接到 ORACLE。

Now to reset the password in 11g, we needto query spare4 column in user$ table

@>conn / as sysdba

已连接。

SYS@orcl>select spare4 from user$ wherename='LIU';

SPARE4

----------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------

S:813731A84040EA2C4DF1545B869495ECA28C81486A11E5E19344F88BA312

SYS@orcl>ALTER USER LIU IDENTIFIED BYABC;

用户已更改。

SYS@orcl>CONN LIU/ABC

已连接。

LIU@orcl>CONN / AS SYSDBA

已连接。

SYS@orcl>ALTER USER LIU IDENTIFIEDBY VALUES'S:813731A84040EA2C4DF1545B869495ECA28C81486A11E5E19344F88BA312';

用户已更改。

SYS@orcl>CONN LIU/ABC

ERROR:

ORA-01017: invalid username/password;logon denied

警告:您不再连接到 ORACLE。

@>CONN / AS SYSDBA

已连接。

SYS@orcl>conn LIU/LIU

已连接。

LIU@orcl>

As per Metalink Note429465.1 , view DBA_USERS has new column PASSWORD_VERSIONS rendered as follows:

decode(length(u.password),16,'10G',NULL)||NVL2(u.spare4, '11G ' ,NULL)

for example:

SYS@orcl>SELECT USERNAME,PASSWORD_VERSIONSFROM DBA_USERS WHERE USERNAME='LIU';

USERNAME                      PASSWORD

------------------------------ --------

LIU                           11G

SYS@orcl>SELECT USERNAME,PASSWORD_VERSIONSFROM DBA_USERS WHERE USERNAME IN ('SYS','DNA','LIU');

USERNAME                      PASSWORD

------------------------------ --------

SYS                           10G 11G

DNA                           10G 11G

LIU                           11G

In this case it means both old andnew-style hash values are available for the users--SYS,DNA, the new hash valueis stored in the USER$.SPARE4 column, as long as this remains NULL it means thepassword has not been changed since the migration and the user will have theold case insensitive password.

SYS@orcl>CREATE USER LIU2 IDENTIFIED BYLIU2;

用户已创建。

SYS@orcl>SELECTUSERNAME,PASSWORD_VERSIONS FROM DBA_USERS WHERE USERNAME IN('SYS','DNA','LIU','LIU2');

USERNAME                      PASSWORD

------------------------------ --------

SYS                           10G 11G

LIU                           11G

LIU2                          10G 11G

DNA                           10G 11G

As I had reset passwordusing only spare4 string, password will be case -sensitive irrespective ofsetting for sec_case_sensitive_logon parameter value

Update

When resetting the password, we need toalso query password column from user$ column if we wish to use case-insensitivefeature in future. i.e In my above example I used only spare4 column value toreset the password. Now if I set sec_case_sensitive_logon=false , I will not beable to connect.

SYS@orcl>CONN LIU/Liu

ERROR:

ORA-01017: invalid username/password;logon denied

警告:您不再连接到 ORACLE。

@>conn LIU/LIU

已连接。

LIU@orcl>CONN / AS SYSDBA

已连接。

SYS@orcl>ALTER SYSTEM SETSEC_CASE_SENSITIVE_LOGON=FALSE;

系统已更改。

SYS@orcl>SHO PARAMETER SEC_CASE

NAME                                TYPE       VALUE

----------------------------------------------- ------------------------------

sec_case_sensitive_logon            boolean    FALSE

SYS@orcl>CONN LIU/LIU

ERROR:

ORA-01017: invalid username/password;logon denied

警告:您不再连接到 ORACLE。

@>CONN LIU/liu

ERROR:

ORA-01017: invalid username/password;logon denied

In case we wish to useboth, we need to setidentified by values ‘S:spare4;password’. As I didnot usepassword field while resetting, I find that password field in user$ is empty.To correct it, I had to change the password again.

SYS@orcl>select password,spare4 fromuser$ where name='LIU'; 

PASSWORD

------------------------------

SPARE4

------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------

S:813731A84040EA2C4DF1545B869495ECA28C81486A11E5E19344F88BA312

SYS@orcl>alter system setsec_case_sensitive_logon=true;

系统已更改。

SYS@orcl>

SYS@orcl>alter user liu identified byabcabc;

用户已更改。

SYS@orcl>select password,spare4 fromuser$ where name='LIU';

PASSWORD

------------------------------

SPARE4

------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------

622BF185A48AEFD1

S:FF0C2DBE8CBFCCECF290452C0389A9117101E1025B47504F9CEE356AF0EF

SYS@orcl>alter user liu identified byvalues'S:FF0C2DBE8CBFCCECF290452C0389A9117101E1025B47504F9CEE356AF0EF;622BF185A48

AEFD1';

用户已更改。

SYS@orcl>selectusername,password_versions from dba_users where username like 'LIU%';

USERNAME                      PASSWORD

------------------------------ --------

LIU2                          10G 11G

LIU                           10G 11G

SYS@orcl>sho parameter sec_case

NAME                                TYPE       VALUE

----------------------------------------------- ------------------------------

sec_case_sensitive_logon            boolean    TRUE

SYS@orcl>conn LIU/abcabc

已连接。

LIU@orcl>conn LIU/ABCABC

ERROR:

ORA-01017: invalid username/password; logondenied

警告:您不再连接到 ORACLE。

@>conn / as sysdba

已连接。

SYS@orcl>alter system setsec_case_sensitive_logon=false;

系统已更改。

SYS@orcl>conn liu/ABCABC

已连接。

LIU@orcl>