oracle 開啟複雜密碼驗證

ORACLE 11g開啟複雜密碼驗證

SQL> set line 200

SQL> 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

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

16 rows selected.

注意跑完這個指令碼會初始化profile,之前設定的unlimited會變成預設的

SQL> @?/rdbms/admin/utlpwdmg.sql

Function created.

Grant succeeded.

Profile altered.

Function created.

Grant succeeded.

SQL> 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

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

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.

SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;

Profile altered.

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

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

16 rows selected.

跑完指令碼後會建立2個關於密碼驗證的函式VERIFY_FUNCTION_11G，VERIFY_FUNCTION

ORACLE 12C開啟複雜密碼驗證

CDB執行

SQL> set line 200

SQL> col PROFILE for a30

SQL> col LIMIT for a30

SQL> col RESOURCE_NAME for a30

SQL> SELECT * FROM Dba_Profiles d WHERE d.profile='DEFAULT';

PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP

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

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED NO NO NO

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED NO NO NO

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED NO NO NO

DEFAULT CPU_PER_CALL KERNEL UNLIMITED NO NO NO

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED NO NO NO

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED NO NO NO

DEFAULT IDLE_TIME KERNEL UNLIMITED NO NO NO

DEFAULT CONNECT_TIME KERNEL UNLIMITED NO NO NO

DEFAULT PRIVATE_SGA KERNEL UNLIMITED NO NO NO

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 NO NO NO

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO NO NO

PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP

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

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED NO NO NO

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED NO NO NO

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL NO NO NO

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 NO NO NO

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 NO NO NO

DEFAULT INACTIVE_ACCOUNT_TIME PASSWORD UNLIMITED NO NO NO

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

或者

alter profile default limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;

需要在PDB執行

SQL> alter session set container=PDB1;

會話已更改。

SQL> set line 200

SQL> col PROFILE for a30

SQL> col LIMIT for a30

SQL> col RESOURCE_NAME for a30

SQL> SELECT * FROM Dba_Profiles d WHERE d.profile='DEFAULT';

PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP

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

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED NO NO NO

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED NO NO NO

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED NO NO NO

DEFAULT CPU_PER_CALL KERNEL UNLIMITED NO NO NO

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED NO NO NO

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED NO NO NO

DEFAULT IDLE_TIME KERNEL UNLIMITED NO NO NO

DEFAULT CONNECT_TIME KERNEL UNLIMITED NO NO NO

DEFAULT PRIVATE_SGA KERNEL UNLIMITED NO NO NO

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 NO NO NO

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO NO NO

PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP

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

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED NO NO NO

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED NO NO NO

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL NO NO NO

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 NO NO NO

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 NO NO NO

DEFAULT INACTIVE_ACCOUNT_TIME PASSWORD UNLIMITED NO NO NO

已選擇 17 行。

SQL> create user test identified by test;

使用者已建立。

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

配置檔案已更改

SQL> alter user test identified by test;

alter user test identified by test

*

第 1 行出現錯誤:

ORA-28003: 指定口令的口令驗證失敗 ORA-20001:

Password length less than 8

SQL> alter user test identified by "test123!@#";

alter user test identified by "test123!@#"

*

第 1 行出現錯誤:

ORA-28003: 指定口令的口令驗證失敗 ORA-20002:

Password contains the username

SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;

配置檔案已更改

SQL> alter user test identified by "test123!@#" ;

使用者已更改。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31481651/viewspace-2661966/，如需轉載，請註明出處，否則將追究法律責任。

oracle 開啟複雜密碼驗證

相關文章