合 Oracle用户密码系列
- 前言部分
- 导读和注意事项
- 本文简介
- 使用profile管理用户口令
- 修改密码为永不过期
- ACCOUNT_STATUS的九种状态
- 锁定状态
- LOCKED
- LOCKED(TIMED)
- 过期状态
- EXPIRED
- EXPIRED(GRACE)
- 组合状态
- EXPIRED & LOCKED
- EXPIRED & LOCKED(TIMED)
- EXPIRED(GRACE) & LOCKED
- EXPIRED(GRACE) & LOCKED(TIMED)
- 在不知道用户密码的情况下如何更改密码
- 用原密码的密文来更改密码
- 直接更新USER$基表
- user$.lCOUNT列记录了失败的登陆次数
- 11g密码区分大小写--sec_case_sensitive_logon
- 密码延迟验证
- 哪些用户密码没有被修改过
- 密码复杂性校验
- resource_limit
前言部分
导读和注意事项
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,\~O(∩_∩)O\~:
① 用户的9种状态含义(重点)
② 如何解锁账户
③ 如何修改密码无效状态
④ 如何获取密码的密文,如何利用密文修改用户的密码(重点)
⑤ 如何查询失败的登陆次数
⑥ 11g 密码大小写问题
⑦ 11g 密码延迟验证
⑧ 密码复杂性校验
本文简介
客户的一个账户密码过期了,但是客户设置了永不过期,问到我为什么。我当时觉得设置了永不过期那肯定是生效的,只是这个部分的内容忘得差不多了,当时还想到可能是resource_limit这个参数没有设置为TRUE的缘故,后来查了官方文档才知道并不是这个原因。于是下决心把这部分的内容系统学习一下,自己总结的内容分享给大家。
使用profile管理用户口令
Oracle用户的状态是由密码来决定的,而Oracle中的密码是由PROFILE来配置的。PROFILE是口令限制、资源限制的命令集合。当建立数据库时,Oracle会自动建立名称为DEFAULT的PROFILE。当创建用户而没有指定PROFILE选项时,Oracle就会将DEFAULT分配给用户。
通过如下的命令可以查出与密码相关的PROFILE的值:
1 2 3 4 5 | SELECT * FROM DBA_PROFILES D WHERE D.PROFILE = 'DEFAULT' AND (D.RESOURCE_NAME LIKE '%PASSWORD%' OR D.RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS'); |
每个参数的含义如下所示:
- FAILED_LOGIN_ATTEMPTS 设定登录到Oracle数据库时可以失败的次数。一旦某用户尝试登录数据库的达到该值时,该用户的帐户就被锁定,只能由DBA能解锁。
- PASSWORD_LIFE_TIME 设定口令的有效时间(天数),一旦超过这一时间,必须重新设口令。缺省为180天。
- PASSWORD_REUSE_TIME 许多系统不许用户重新启用过去用过的口令。该资源项设定了一个失效口令要经过多少天,用户才可以重新使用该口令,缺省为UNLIMITED。
- PASSWORD_REUSE_MAX 重新启用一个先前用过的口令前必须对该口令进行重新设置的次数(重复用的次数)。
- PASSWORD_LOCK_TIME 设定帐户被锁定的天数(当登录失败达到FAILED_LOGIN_ATTEMPTS时)。
- PASSWORD_GRACE_TIME 设定在口令失效前,给予的重新设该口令的宽限天。当口令失效之后回,在登录时会出现警告信息显示该天数。如果没有在宽限天内修改口令,口令将失效。
- PASSWORD_VERITY_FUNCTION 该资源项允许调用一个PL/SQL来验证口令。Oracle已提供该应用的脚本,为\$ORACLE_HOME/rdbms/admin/utlpwdmg.sql。但是,用户可以制定自己的验证脚本。该参数的设定就是PL/SQL函数的名称,缺省为NULL。
修改密码为永不过期
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | SYS@lhrdb> select username, account_status,EXPIRY_DATE,profile from dba_users where username = 'LHRSYS'; USERNAM ACCOUNT_STATUS EXPIRY_DATE PROFILE ------- ------------------------------- ------------------- ------------------------------ LHRSYS OPEN 2016-12-07 15:20:36 TESTPROFILE SYS@lhrdb> alter user lhrsys password expire; User altered. SYS@lhrdb> select username, account_status,EXPIRY_DATE,profile from dba_users where username = 'LHRSYS'; USERNAM ACCOUNT_STATUS EXPIRY_DATE PROFILE ------- ------------------------------- ------------------- ------------------------------ LHRSYS EXPIRED 2016-12-02 16:36:24 TESTPROFILE SYS@lhrdb> ALTER PROFILE TESTPROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED; Profile altered. SYS@lhrdb> select username, account_status,EXPIRY_DATE,profile from dba_users where username = 'LHRSYS'; USERNAM ACCOUNT_STATUS EXPIRY_DATE PROFILE ------- ------------------------------- ------------------- ------------------------------ LHRSYS EXPIRED 2016-12-02 16:36:24 TESTPROFILE SYS@lhrdb> SELECT NB.PASSWORD FROM USER$ NB WHERE NB.NAME ='LHRSYS'; PASSWORD ------------------------------ F809740420A44EFC SYS@lhrdb> ALTER USER LHRSYS IDENTIFIED BY VALUES 'F809740420A44EFC'; User altered. SYS@lhrdb> select username, account_status,EXPIRY_DATE,profile from dba_users where username = 'LHRSYS'; USERNAM ACCOUNT_STATUS EXPIRY_DATE PROFILE ------- ------------------------------- ------------------- ------------------------------ LHRSYS OPEN TESTPROFILE SYS@lhrdb> |
ACCOUNT_STATUS的九种状态
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SYS@lhrdb> SELECT * FROM USER_ASTATUS_MAP; STATUS# STATUS ---------- -------------------------------- 0 OPEN 1 EXPIRED 2 EXPIRED(GRACE) 4 LOCKED(TIMED) 8 LOCKED 5 EXPIRED & LOCKED(TIMED) 6 EXPIRED(GRACE) & LOCKED(TIMED) 9 EXPIRED & LOCKED 10 EXPIRED(GRACE) & LOCKED 9 rows selected. |
以上九种可以分为两大类:1.基本状态;2.组合状态。
前五种是基本状态:
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
后四种是组合状态:
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED
规律是这样的:后四种的组合状态可以通过状态号STATUS#获得它是哪两种状态的组合,例如10=2+8(10 EXPIRED(GRACE) & LOCKED = 2 EXPIRED(GRACE) + 8 LOCKED)。因此只要了解基本状态的含义其他便可无师自通。
这五种基本状态又可以分为三类:1.正常状态;2.锁定状态;3.密码过期状态。
1)OPEN表示用户处于正常状态。
2)用户被锁定状态,LOCKED和LOCKED(TIMED)两种状态都属于锁定状态
用户被锁定一般分为两种:一种是DBA显式的通过SQL语句对用户进行锁定;另外一种是被动的锁定,例如默认情况下如果密码输入错误超过10次(这个限制是由PROFILE中的FAILED_LOGIN_ATTEMPTS控制的,该信息可以通过DBA_PROFILES视图查询),用户将被锁定。
锁定状态
LOCKED
显式锁定LHRSYS用户LOCKED状态演示
1 2 3 4 5 6 7 8 9 10 | SELECT D.USERNAME, D.ACCOUNT_STATUS, D.LOCK_DATE, D.EXPIRY_DATE, D.PROFILE, NVL(D.PASSWORD, (SELECT NB.PASSWORD FROM USER$ NB WHERE NB.NAME = D.USERNAME)) PASSWORD FROM DBA_USERS D WHERE D.USERNAME = 'LHRSYS'; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | SYS@lhrdb> ALTER USER LHRSYS ACCOUNT LOCK; User altered. SYS@lhrdb> conn lhrsys/lhr ERROR: ORA-28000: the account is locked Warning: You are no longer connected to ORACLE. @> conn / as sysdba Connected. SYS@lhrdb> select username, account_status,d.lock_date from dba_users d where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS LOCK_DATE ------------------------------ -------------------------------- ------------------- LHRSYS LOCKED 2016-12-02 09:33:50 SYS@lhrdb> ALTER USER LHRSYS ACCOUNT UNLOCK; User altered. SYS@lhrdb> select username, account_status,d.lock_date from dba_users d where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS LOCK_DATE ------------------------------ -------------------------------- ------------------- LHRSYS OPEN |
LOCKED(TIMED)
输入10次错误密码后被动锁定LOCKED(TIMED)状态演示
尝试输入10次错误密码后再次查询用户状态。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | SYS@lhrdb> SELECT * FROM Dba_Profiles d WHERE d.profile='DEFAULT' AND D.resource_name LIKE '%FAILED_LOGIN_ATTEMPTS%' ; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 SYS@lhrdb> CONN LHRSYS/XXXX@192.168.59.129/lhrdb ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. @> CONN LHRSYS/XXXX@192.168.59.129/lhrdb ERROR: ORA-01017: invalid username/password; logon denied @> CONN LHRSYS/XXXX@192.168.59.129/lhrdb ERROR: ORA-01017: invalid username/password; logon denied @> CONN LHRSYS/XXXX@192.168.59.129/lhrdb ERROR: ORA-01017: invalid username/password; logon denied @> CONN LHRSYS/XXXX@192.168.59.129/lhrdb ERROR: ORA-01017: invalid username/password; logon denied @> CONN LHRSYS/XXXX@192.168.59.129/lhrdb ERROR: ORA-01017: invalid username/password; logon denied @> CONN LHRSYS/XXXX@192.168.59.129/lhrdb ERROR: ORA-01017: invalid username/password; logon denied @> CONN LHRSYS/XXXX@192.168.59.129/lhrdb ERROR: ORA-01017: invalid username/password; logon denied @> CONN LHRSYS/XXXX@192.168.59.129/lhrdb ERROR: ORA-01017: invalid username/password; logon denied @> CONN LHRSYS/XXXX@192.168.59.129/lhrdb ERROR: ORA-01017: invalid username/password; logon denied @> CONN LHRSYS/XXXX@192.168.59.129/lhrdb ERROR: ORA-28000: the account is locked<<<<<<<<<------超过10次后用户被锁定 @> CONN / AS SYSDBA Connected. SYS@lhrdb> select username, account_status,d.lock_date from dba_users d where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS LOCK_DATE ------------------------------ -------------------------------- ------------------- LHRSYS LOCKED(TIMED) 2016-12-02 09:37:20 SYS@lhrdb> SYS@lhrdb> SYS@lhrdb> ALTER USER LHRSYS ACCOUNT UNLOCK; User altered. SYS@lhrdb> select username, account_status,d.lock_date from dba_users d where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS LOCK_DATE ------------------------------ -------------------------------- ------------------- LHRSYS OPEN |
过期状态
用户密码过期状态,EXPIRED和EXPIRED(GRACE)两种状态都属于密码过期状态。
EXPIRED
密码是否过期是通过修改PROFILE中的PASSWORD_LIFE_TIME实现的,密码过期后还可以使用的天数是通过PROFILE中的PASSWORD_GRACE_TIME控制的。
关于密码过期我们也可以使用SQL显式的去完成,简单演示一下。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | SYS@lhrdb> select username, account_status,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE ------------------------------ -------------------------------- ------------------- LHRSYS OPEN SYS@lhrdb> alter user lhrsys password expire; User altered. SYS@lhrdb> select username, account_status,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE ------------------------------ -------------------------------- ------------------- LHRSYS EXPIRED 2016-12-01 16:29:01 SYS@lhrdb> conn lhrsys/lhr ERROR: ORA-28001: the password has expired Changing password for lhrsys New password: Retype new password: Password changed Connected. LHRSYS@lhrdb> conn / as sysdba Connected. SYS@lhrdb> select username, account_status,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE ------------------------------ -------------------------------- ------------------- LHRSYS OPEN |
下面通过修改系统的日期来演示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | SYS@lhrdb> SELECT D.USERNAME, 2 D.ACCOUNT_STATUS, 3 D.LOCK_DATE, 4 D.EXPIRY_DATE, 5 D.PROFILE, 6 NVL(D.PASSWORD, 7 (SELECT NB.PASSWORD FROM USER$ NB WHERE NB.NAME = D.USERNAME)) PASSWORD 8 FROM DBA_USERS D 9 WHERE D.USERNAME = 'LHRSYS'; USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE PROFILE PASSWORD ------------------------------ -------------------------------- ------------------- ------------------- ------------------------------ ------------------------------ LHRSYS OPEN DEFAULT F809740420A44EFC SYS@lhrdb> create profile TESTPROFILE LIMIT password_life_time 5 password_grace_time 0;<<<<<<<<<------这里将password_grace_time设置为0 Profile created. SYS@lhrdb> alter user LHRSYS profile TESTPROFILE; User altered. SYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DATE ------------------------------ -------------------------------- ------------------------------ ------------------- LHRSYS OPEN TESTPROFILE 2016-12-07 14:50:36 --修改系统时间 [root@orcltest ~]# date '12071450' Wed Dec 7 14:50:00 CST 2016 [root@orcltest ~]# 系统查询: SYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-07 14:50:19 SYS@lhrdb> conn LHRSYS/lhr Connected. LHRSYS@lhrdb> conn LHRSYS/lhr ERROR: ORA-28001: the password has expired Changing password for LHRSYS New password: Password unchanged Warning: You are no longer connected to ORACLE. @> conn / as sysdba Connected. SYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DATE ------------------------------ -------------------------------- ------------------------------ ------------------- LHRSYS EXPIRED TESTPROFILE 2016-12-12 10:36:06 SYS@lhrdb> alter user LHRSYS identified by lhr; User altered. SYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DATE ------------------------------ -------------------------------- ------------------------------ ------------------- LHRSYS OPEN TESTPROFILE 2016-12-12 14:52:54 |
EXPIRED(GRACE)
当设置了PASSWORD_GRACE_TIME以后,第一次成功登录后到口令到期后有多少天时间可改变口令,在这段时间内,帐户被提醒修改口令并可以正常登陆,account_status显示为EXPIRED(GRACE)。expired(grace) 与 locked(timed)是由系统的profile来进行控制的。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 | SYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DATE ------------------------------ -------------------------------- ------------------------------ ------------------- LHRSYS OPEN TESTPROFILE 2016-12-07 14:09:09 SYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-02 14:09:32 SYS@lhrdb> ALTER PROFILE TESTPROFILE LIMIT password_grace_time 3; Profile altered. SYS@lhrdb> SELECT * 2 FROM DBA_PROFILES D 3 WHERE D.PROFILE = 'TESTPROFILE' 4 AND (D.RESOURCE_NAME LIKE '%PASSWORD%' OR 5 D.RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS'); PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- TESTPROFILE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT TESTPROFILE PASSWORD_LIFE_TIME PASSWORD 5 TESTPROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT TESTPROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT TESTPROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT TESTPROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT TESTPROFILE PASSWORD_GRACE_TIME PASSWORD 3 7 rows selected. SYS@lhrdb> --修改系统时间 [root@orcltest ~]# date '12071408'<<<<<<<<<------14:09过期,我们设置到14:08分 Wed Dec 7 14:08:00 CST 2016 [root@orcltest ~]# 系统查询: LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-07 14:08:03 LHRSYS@lhrdb> conn lhrsys/lhr Connected. LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-07 14:09:06 LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-07 14:09:09 LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-07 14:09:11<<<<<<<<<------已过了密码有效期 LHRSYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DATE ------------------------------ -------------------------------- ------------------------------ ------------------- LHRSYS OPEN TESTPROFILE 2016-12-07 14:09:09 <<<<<<<<<------但该用户的状态未改变,下面尝试第一次登陆 LHRSYS@lhrdb> conn lhrsys/lhr ERROR: ORA-28002: the password will expire within 3 days<<<<<<<<<------第一次登陆后报错,但用户依然可以登陆,且EXPIRY_DATE已经变化 Connected. LHRSYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DATE ------------------------------ -------------------------------- ------------------------------ ------------------- LHRSYS EXPIRED(GRACE) TESTPROFILE 2016-12-10 14:09:34 <<<<<<<<<------再次查询状态,变为了EXPIRED(GRACE) 再次调整日期: [root@orcltest ~]# date '12081430' Thu Dec 8 14:30:00 CST 2016 LHRSYS@lhrdb> LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-08 14:30:12 LHRSYS@lhrdb> conn lhrsys/lhr ERROR: ORA-28002: the password will expire within 2 days<<<<<<<<<------变为了2天 Connected. LHRSYS@lhrdb> 继续更改日期: [root@orcltest ~]# date '12101409' Sat Dec 10 14:09:00 CST 2016 [root@orcltest ~]# 查询: LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-10 14:09:07 LHRSYS@lhrdb> conn lhrsys/lhr ERROR: ORA-28002: the password will expire within 0 days<<<<<<<<<------变为了0天 Connected. LHRSYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DATE ------------------------------ -------------------------------- ------------------------------ ------------------- LHRSYS EXPIRED(GRACE) TESTPROFILE 2016-12-10 14:09:34 LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-10 14:09:39<<<<<<<<<------GRACE日期已过 LHRSYS@lhrdb> conn lhrsys/lhr ERROR: ORA-28001: the password has expired Changing password for lhrsys New password: Password unchanged Warning: You are no longer connected to ORACLE. @> conn / as sysdba Connected. SYS@lhrdb> SYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DATE ------------------------------ -------------------------------- ------------------------------ ------------------- LHRSYS EXPIRED TESTPROFILE 2016-12-10 14:09:34 SYS@lhrdb> alter user LHRSYS identified by lhr; User altered. SYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DATE ------------------------------ -------------------------------- ------------------------------ ------------------- LHRSYS OPEN TESTPROFILE 2016-12-15 14:13:08 SYS@lhrdb> |
组合状态
关于四种组合状态的解释
因为锁定的两种状态(LOCKED和LOCKED(TIMED))和密码过期的两种状态(EXPIRED和EXPIRED(GRACE))之间没有关系。因此他们之间可以任意组合,2×2=4,因此有四种组合状态:
5 EXPIRED & LOCKED(TIMED)