或许你不知的ORACLE秘密系列一
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了或许你不知的ORACLE秘密系列一，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7424字，纯文字阅读大概需要11分钟。
内容图文
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/l
LIU 9DEC0D889E8E9A6BSQL> 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>
内容总结
以上是互联网集市为您收集整理的或许你不知的ORACLE秘密系列一全部内容，希望文章能够帮你解决或许你不知的ORACLE秘密系列一所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。