Oracle 的密碼政策，你知道多少？

今天突然有客戶問我一個問題，資料庫要添加一個監控使用者，想做一個會話數的限制，這裡做了一個小測試，平日維護的時候也需要關注一下資料庫的資源限制。

<roidb1:orcl1:/home/oracle>$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 18:16:19 2017  

Copyright (c) 1982, 2013, Oracle.  All rights reserved.  

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> show parameter resource_limit

NAME                                 TYPE        VALUE

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

resource_limit                       boolean     FALSE      --預設值    

SQL>

SQL> set linesize 160

SQL> select * from dba_profiles order by 1,3,2;

PROFILE                        RESOURCE_NAME                 RESOURCE LIMIT

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

DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED

DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED

DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED

DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED

DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED

DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED

DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED

DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED

DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED

DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10

DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7

PROFILE                        RESOURCE_NAME                 RESOURCE LIMIT

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

DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180

DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1

DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

MONITORING_PROFILE             COMPOSITE_LIMIT                  KERNEL   DEFAULT

MONITORING_PROFILE             CONNECT_TIME                     KERNEL   DEFAULT

MONITORING_PROFILE             CPU_PER_CALL                     KERNEL   DEFAULT

MONITORING_PROFILE             CPU_PER_SESSION                  KERNEL   DEFAULT

MONITORING_PROFILE             IDLE_TIME                        KERNEL   DEFAULT

MONITORING_PROFILE             LOGICAL_READS_PER_CALL           KERNEL   DEFAULT

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

MONITORING_PROFILE             LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT

MONITORING_PROFILE             PRIVATE_SGA                      KERNEL   DEFAULT

MONITORING_PROFILE             SESSIONS_PER_USER                KERNEL   DEFAULT

MONITORING_PROFILE             FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED

MONITORING_PROFILE             PASSWORD_GRACE_TIME              PASSWORD DEFAULT

MONITORING_PROFILE             PASSWORD_LIFE_TIME               PASSWORD DEFAULT

MONITORING_PROFILE             PASSWORD_LOCK_TIME               PASSWORD DEFAULT

MONITORING_PROFILE             PASSWORD_REUSE_MAX               PASSWORD DEFAULT

MONITORING_PROFILE             PASSWORD_REUSE_TIME              PASSWORD DEFAULT

MONITORING_PROFILE             PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT

32 rows selected.

測試1： FAILED_LOGIN_ATTEMPTS=10 是否是生效

SQL> create user roidba identified by roidba;

User created.

SQL> grant connect,resource,dba to roidba;

Grant succeeded.

SQL> exit 

省略..................經過十次登陸...........

<roidb1:orcl1:/home/oracle>$sqlplus roidba/roidbaa

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 18:26:37 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:

ORA-28000: the account is locked

Enter user-name: 

ORA-01017: invalid username/password; logon denied

實驗證明不管 resource_limit 是否為true，和密碼相關的限制都是生效，其他和密碼相關的大家可以自己測試。

DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10     --密碼輸入十次都是錯誤，使用者鎖定

DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1      --鎖定一天以後自動解鎖

DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180    --密碼生命周期180天，之後密碼失效

DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7      --寬限延續期，寬限期内登陸會有提示。

安裝完資料庫，一般會把password_life_time設定為unlimited。

SQL> alter profile default limit password_life_time unlimited;

Profile altered.

繼續測試2：

SQL> create profile sess limit   

  2  SESSIONS_PER_USER 2;

Profile created.

SQL> alter user roidba profile sess;

User altered.

SQL> alter system set resource_limit=true;

System altered.

打開三個視窗，前兩個都順利登陸，第三個出現以下報錯。

<roidb1:orcl1:/home/oracle>$sqlplus roidba/roidba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 18:48:24 2017

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

小夥伴們，不要光看不練，花個十分二十分鐘動手操作一下哈！

本文轉自 roidba 51CTO部落格，原文連結：http://blog.51cto.com/roidba/1966441，如需轉載請自行聯系原作者