Oracle用户密码系列
👉 本文共约4756个字，系统预计阅读时间或需18分钟。
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，\~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)
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
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)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED
EXPIRED & LOCKED
EXPIRED & LOCKED状态表示用户密码过期且同时处于锁定状态
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 | SYS@lhrdb> select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS OPEN 2016-12-07 15:02:56 SYS@lhrdb> SYS@lhrdb> alter user lhrsys password expire; User altered. SYS@lhrdb> select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS EXPIRED 2016-12-02 15:11:12 SYS@lhrdb> alter user lhrsys account lock; User altered. SYS@lhrdb> select username, account_status,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE ------------------------------ -------------------------------- ------------------- LHRSYS EXPIRED & LOCKED 2016-12-01 16:51:38 SYS@lhrdb> alter user lhrsys account unlock; 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:51:38 SYS@lhrdb> alter user lhrsys identified by lhr; User altered. SYS@lhrdb> select username, account_status,EXPIRY_DATE from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE ------------------------------ -------------------------------- ------------------- LHRSYS OPEN |
EXPIRED & LOCKED(TIMED)
EXPIRED & LOCKED(TIMED)状态表示用户密码过期后，错误密码尝试次数超过PROFILE中的FAILED_LOGIN_ATTEMPTS的限制
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 | SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_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-02 10:07:27 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 / as sysdba Connected. SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS EXPIRED & LOCKED(TIMED) 2016-12-02 10:07:27 2016-12-02 10:09:03 SYS@lhrdb> SYS@lhrdb> alter user lhrsys account unlock; User altered. SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS EXPIRED 2016-12-02 10:07:27 SYS@lhrdb> alter user lhrsys identified by lhr; User altered. SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS OPEN SYS@lhrdb> |
EXPIRED(GRACE) & LOCKED
EXPIRED(GRACE) & LOCKED状态表示用户在密码过期后的有效期内被DBA手工锁定。
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 | 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:39:20 LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-07 14:39:17 LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-07 14:39:25 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:39:20 LHRSYS@lhrdb> conn lhrsys/lhr ERROR: ORA-28002: the password will expire within 3 days 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:39:54 LHRSYS@lhrdb> alter user lhrsys account lock; User altered. SYS@lhrdb> select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS EXPIRED(GRACE) & LOCKED 2016-12-10 14:39:54 2016-12-07 14:40:20 LHRSYS@lhrdb> alter user lhrsys account unlock; User altered. 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:39:54 LHRSYS@lhrdb> alter user LHRSYS identified by lhr; User altered. 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-12 14:40:46 LHRSYS@lhrdb> |
EXPIRED(GRACE) & LOCKED(TIMED)
EXPIRED(GRACE) & LOCKED(TIMED)状态表示用户在密码过期后的有效期内，失败登录次数超过PROFILE中的FAILED_LOGIN_ATTEMPTS的限制。
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 | SYS@lhrdb> select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS OPEN 2016-12-07 14:50:06 LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-02 14:50:13 修改系统日期： [root@orcltest ~]# date '12071450' Wed Dec 7 14:50:00 CST 2016 [root@orcltest ~]# 查询： LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-07 14:50:03 LHRSYS@lhrdb> select sysdate from dual; SYSDATE ------------------- 2016-12-07 14:50:12 SYS@lhrdb> select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS OPEN 2016-12-07 14:50:06 LHRSYS@lhrdb> conn lhrsys/lhr ERROR: ORA-28002: the password will expire within 3 days Connected. SYS@lhrdb> select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS EXPIRED(GRACE) 2016-12-10 14:50:21 LHRSYS@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 @> conn / as sysdba Connected. SYS@lhrdb> select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS EXPIRED(GRACE) & LOCKED(TIMED) 2016-12-10 14:50:21 2016-12-07 14:53:30 SYS@lhrdb> alter user lhrsys account unlock; User altered. SYS@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:50:21 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:55:04 |
在不知道用户密码的情况下如何更改密码
在Oracle中，若用户的密码变为锁定状态（LOCKED、LOCKED(TIMED)）则DBA直接执行ALTER USER用户名 ACCOUNT UNLOCK就可以解锁了。但是，如果用户的状态变成过期状态（EXPIRED、EXPIRED(GRACE)），则DBA必须要更改用户的密码账户才能重新使用。但有些时候，因为各种原因并不知道原密码的明文是什么，这时候可以有如下2种办法来更新密码。
用原密码的密文来更改密码
在Oracle 10g中，DBA_USERS视图的PASSWORD字段提供了密码的密文形式，而在Oracle 11g中，该字段被弃用了，内容为空，但是在基表USER\$中的PASSWORD字段依然有记录密文形式，所以可以通过如下的形式来获取密码的密文形式：
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'; |
另外，可以通过DBMS_METADATA.GET_DDL包或者expdp、exp命令来获取创建用户的语句从而获取密码的密文形式：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SYS@lhrdb> set long 9999 SYS@lhrdb> SELECT DBMS_METADATA.GET_DDL('USER', 'LHRSYS') DDL_SQL FROM DUAL; DDL_SQL -------------------------------------------------------------------------------- CREATE USER "LHRSYS" IDENTIFIED BY VALUES 'S:853EA80BAE11F79D6946453F38059E30313FE84C96AE2EE4F3AA35A648BD;F809740420A44EFC' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" SYS@lhrdb> SYS@lhrdb> |
获取了密码的密文后就可以用如下的命令来修改了，注意：使用密文的命令中多了一个values关键字：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SYS@lhrdb> SYS@lhrdb> alter user LHRSYS identified by values 'F809740420A44EFC'; User altered. SYS@lhrdb> CONN LHRSYS/lhr@192.168.59.129/lhrdb Connected. LHRSYS@192.168.59.129/lhrdb> conn / as sysdba Connected. SYS@lhrdb> alter user LHRSYS identified by values 'S:853EA80BAE11F79D6946453F38059E30313FE84C96AE2EE4F3AA35A648BD;F809740420A44EFC'; User altered. SYS@lhrdb> CONN LHRSYS/lhr@192.168.59.129/lhrdb Connected. LHRSYS@192.168.59.129/lhrdb> |
这种情况下，虽然我们不知道原密码是什么，但可以用它的密文来更改密码，这样，在不知道原密码的情况下，既保持了密码不改变，又可以把expired的状态更改掉。
在MOS The Impact of PASSWORD_LIFE_TIME Database Profile Parameter Default to 180 Days on Network Charging and Control (文档 ID 1543668.1)中搜到了如下的命令也可以直接获取密码：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT SQLTEXT FROM (SELECT NAME, 'alter user ' || NAME || ' identified by values ''' || PASSWORD || ''';' SQLTEXT FROM USER$ WHERE SPARE4 IS NULL AND PASSWORD IS NOT NULL UNION SELECT NAME, 'alter user ' || NAME || ' identified by values ''' || SPARE4 || ';' || PASSWORD || ''';' SQLTEXT FROM USER$ WHERE SPARE4 IS NOT NULL AND PASSWORD IS NOT NULL) WHERE NAME = 'LHRSYS'; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SYS@lhrdb> SELECT SQLTEXT 2 FROM (SELECT NAME, 3 'alter user ' || NAME || ' identified by values ''' || 4 PASSWORD || ''';' SQLTEXT 5 FROM USER$ 6 WHERE SPARE4 IS NULL 7 AND PASSWORD IS NOT NULL 8 UNION 9 SELECT NAME, 10 'alter user ' || NAME || ' identified by values ''' || SPARE4 || ';' || 11 PASSWORD || ''';' SQLTEXT 12 FROM USER$ 13 WHERE SPARE4 IS NOT NULL 14 AND PASSWORD IS NOT NULL) 15 WHERE NAME = 'LHRSYS'; SQLTEXT ------------------------------------------ alter user LHRSYS identified by values 'S:853EA80BAE11F79D6946453F38059E30313FE84C96AE2EE4F3AA35A648BD;F809740420A44EFC'; |
直接更新USER$基表
不管用户的状态是什么，通过更新USER\$表可以让用户处于OPEN状态：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS EXPIRED 2016-12-02 10:40:09 SYS@lhrdb> UPDATE USER$ SET ASTATUS=0 WHERE NAME='LHRSYS'; 1 row updated. SYS@lhrdb> commit;<<<<<<<<<------及时提交 Commit complete. SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = 'LHRSYS'; USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE ------------------------------ -------------------------------- ------------------- ------------------- LHRSYS OPEN SYS@lhrdb> |
user$.lCOUNT列记录了失败的登陆次数
登录失败，lcount加1；只要成功登录后，lcount栏位就会置0。
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 | SYS@lhrdb> select NAME,LCOUNT from user$ a WHERE a.NAME='LHRSYS'; NAME LCOUNT ------------------------------ ---------- LHRSYS 0 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 / as sysdba Connected. SYS@lhrdb> select NAME,LCOUNT from user$ a WHERE a.NAME='LHRSYS'; NAME LCOUNT ------------------------------ ---------- LHRSYS 2 SYS@lhrdb> SYS@lhrdb> CONN LHRSYS/lhr Connected. LHRSYS@lhrdb> conn / as sysdba Connected. SYS@lhrdb> select NAME,LCOUNT from user$ a WHERE a.NAME='LHRSYS'; NAME LCOUNT ------------------------------ ---------- LHRSYS 0 SYS@lhrdb> |
另外，审计表也记录了登陆失败的信息：
1 2 3 4 5 6 | SELECT d.username,d.timestamp,d.action_name,d.os_username,d.terminal FROM DBA_AUDIT_TRAIL D WHERE D.RETURNCODE = 1017 AND D.USERNAME = 'LHRSYS' ORDER BY d.timestamp DESC ; |
11g密码区分大小写--sec_case_sensitive_logon
| Property | Description |
|---|---|
| Parameter type | Boolean |
| Default value | true |
| Modifiable | ALTER SYSTEM |
| Range of values | true | false |
| Basic | No |
从Oracle 11g开始，密码区分大小写，采用参数sec_case_sensitive_logon控制，该参数默认为TRUE。
SEC_CASE_SENSITIVE_LOGON enables or disables password case sensitivity in the database.
Values:
true：Database logon passwords are case sensitive.
false：Database logon passwords are not case sensitive.
注意：从Oracle 12c开始，参数SEC_CASE_SENSITIVE_LOGON已经废弃，无论其值设置为TRUE还是FALSE，用户密码均区分大小写。
密码延迟验证
从11g开始，如果一个用户使用不正确的密码尝试登录数据库，那么随着登录失败次数的增加，每次登录验证前延迟等待的时间也会增加。
通过设置EVENTS 28401可以屏蔽密码延迟验证：
1 2 | SQL> ALTER SYSTEM SET EVENT = '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE； |
设置该事件后重启数据库即可。
1 2 3 4 5 6 7 8 9 10 11 | [oracle@orcltest ~]$ oerr ora 28401 28401, 00000, "Event to disable delay after three failed login attempts" // *Document: NO // *Cause: N/A // *Action: Set this event in your environment to disable the login delay // which will otherwise take place after three failed login attempts. // *Note: THIS IS NOT A USER ERROR NUMBER/MESSAGE. THIS DOES NOT NEED TO BE // TRANSLATED OR DOCUMENTED. [oracle@orcltest ~]$ |
哪些用户密码没有被修改过
Oracle在11g中对于安全方面进行了很大的改进，比如增加了密码大小写验证，增加了密码复杂度的验证等等。在Oracle 11g中还提供了一个视图DBA_USERS_WITH_DEFPWD用来指出那些用户的密码没有被修改过，仍然是数据库默认密码。Oracle并不是简单的监测是否密码被修改，而是检查密码是否修改为别的值，如果新密码和旧密码保持一致，那么即使密码被修改，这个用户仍然在DBA_USERS_WITH_DEFPWD视图中。
1 2 | SELECT * FROM DBA_USERS_WITH_DEFPWD; |
密码复杂性校验
脚本位置：$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [oracle@orcltest ~]$ ll $ORACLE_HOME/rdbms/admin/utlpwdmg.sql -rw-r--r-- 1 oracle oinstall 11555 Aug 13 2006 /u02/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlpwdmg.sql [oracle@orcltest ~]$ SYS@lhrdb> @?/rdbms/admin/utlpwdmg.sql Function created. Profile altered. Function created. SYS@lhrdb> |
该脚本中有如下的一段：
1 2 3 4 5 6 7 8 9 10 | ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 180 PASSWORD_GRACE_TIME 7 PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION verify_function_11G; |
更改之后查看：
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 | SYS@lhrdb> SELECT * FROM Dba_Profiles d WHERE d.profile='DEFAULT'; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED DEFAULT CPU_PER_SESSION KERNEL UNLIMITED DEFAULT CPU_PER_CALL KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED DEFAULT IDLE_TIME KERNEL UNLIMITED DEFAULT CONNECT_TIME KERNEL UNLIMITED DEFAULT PRIVATE_SGA KERNEL UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 16 rows selected. SYS@lhrdb> create user lhrpwd identified by lhr; create user lhrpwd identified by lhr * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20001: Password length less than 8 SYS@lhrdb> SYS@lhrdb> alter profile default limit PASSWORD_VERIFY_FUNCTION null;<<<<<<<<<------取消复杂性验证 Profile altered. SYS@lhrdb> create user lhrpwd identified by lhr; User created. SYS@lhrdb> alter profile default limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;<<<<<<<------启用密码复杂性验证 Profile altered. SYS@lhrdb> |
resource_limit
官方文档资料：
To create a profile, you must have the CREATE PROFILE system privilege.
To specify resource limits for a user, you must:
•Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. This parameter does not apply to password resources. Password resources are always enabled.
•Create a profile that defines the limits using the CREATE PROFILE statement
•Assign the profile to the user using the CREATE USER or ALTER USER statement
- 用户所有拥有的PROFILE中有关密码的限制永远生效，不受限制。
- 用户所有拥有的PROFILE中有关资源的限制与resource_limit参数的设置有关，当为TRUE时生效，当为FALSE时（默认值是FALSE）无效。在Oracle 12c之前该参数默认为FALSE，而从12c开始，该参数默认为TRUE。
