12c密码加固

关于Oracle12c 密码加固金融行业都是有要求的，这里做一个测试记录。

1、创建一个用户SYS@orcl1> create user roidba identified by roidba;create user roidba identified by roidba*ERROR at line 1:ORA-28003: password verification for the specified password failedORA-20001: Password same as or similar to user  --出现报错，提示密码验证失败。2、查看profileSYS@orcl1> select * from dba_profiles;PROFILE            RESOURCE_NAME                  RESOURCE_TYPE    LIMIT           COMMON------------------ ------------------------------ ---------------- --------------- ------DEFAULT            COMPOSITE_LIMIT                KERNEL           UNLIMITED       NODEFAULT            SESSIONS_PER_USER              KERNEL           UNLIMITED       NODEFAULT            CPU_PER_SESSION                KERNEL           UNLIMITED       NODEFAULT            CPU_PER_CALL                   KERNEL           UNLIMITED       NODEFAULT            LOGICAL_READS_PER_SESSION      KERNEL           UNLIMITED       NODEFAULT            LOGICAL_READS_PER_CALL         KERNEL           UNLIMITED       NODEFAULT            IDLE_TIME                      KERNEL           UNLIMITED       NODEFAULT            CONNECT_TIME                   KERNEL           UNLIMITED       NODEFAULT            PRIVATE_SGA                    KERNEL           UNLIMITED       NODEFAULT            FAILED_LOGIN_ATTEMPTS          PASSWORD         10              NODEFAULT            PASSWORD_LIFE_TIME             PASSWORD         180             NODEFAULT            PASSWORD_REUSE_TIME            PASSWORD         UNLIMITED       NODEFAULT            PASSWORD_REUSE_MAX             PASSWORD         UNLIMITED       NODEFAULT            PASSWORD_VERIFY_FUNCTION       PASSWORD         VERIFY_FUNCTION NODEFAULT            PASSWORD_LOCK_TIME             PASSWORD         1               NODEFAULT            PASSWORD_GRACE_TIME            PASSWORD         7               NOORA_STIG_PROFILE   COMPOSITE_LIMIT                KERNEL           DEFAULT         NOORA_STIG_PROFILE   SESSIONS_PER_USER              KERNEL           DEFAULT         NOORA_STIG_PROFILE   CPU_PER_SESSION                KERNEL           DEFAULT         NOORA_STIG_PROFILE   CPU_PER_CALL                   KERNEL           DEFAULT         NOORA_STIG_PROFILE   LOGICAL_READS_PER_SESSION      KERNEL           DEFAULT         NOORA_STIG_PROFILE   LOGICAL_READS_PER_CALL         KERNEL           DEFAULT         NOORA_STIG_PROFILE   IDLE_TIME                      KERNEL           15              NOORA_STIG_PROFILE   CONNECT_TIME                   KERNEL           DEFAULT         NOORA_STIG_PROFILE   PRIVATE_SGA                    KERNEL           DEFAULT         NOORA_STIG_PROFILE   FAILED_LOGIN_ATTEMPTS          PASSWORD         3               NOORA_STIG_PROFILE   PASSWORD_LIFE_TIME             PASSWORD         60              NOORA_STIG_PROFILE   PASSWORD_REUSE_TIME            PASSWORD         365             NOORA_STIG_PROFILE   PASSWORD_REUSE_MAX             PASSWORD         10              NOORA_STIG_PROFILE   PASSWORD_VERIFY_FUNCTION       PASSWORD         ORA12C_STRONG_V NOERIFY_FUNCTION     --使用了12c密码验证策略ORA_STIG_PROFILE   PASSWORD_LOCK_TIME             PASSWORD         UNLIMITED       NOORA_STIG_PROFILE   PASSWORD_GRACE_TIME            PASSWORD         5               NO32 rows selected.SYS@orcl1> 

3、密码验证策略要求

ora12c_strong_verify_function Function Password RequirementsThe ora12c_strong_verify_function function fulfills the Department of Defense Database Security Technical Implementation Guide requirements.This function checks for the following requirements when users create or modify passwords:The password must contain at least 2 upper case characters, 2 lower case characters, 2 numeric characters, and 2 special characters. These special characters are as follows:--要求至少2个大写，两个小写，2个数字，2个特殊字符' ~ ! @ # $ % ^ & * ( ) _ - + = { } [ ] \ / < > , . ; ? ' : | (space) The password must differ from the previous password by at least 4 characters.The following internal checks are also applied:The password contains no fewer than nine characters and does not exceed 30 characters.The password does not contain the double-quotation character ("). It can be surrounded by double-quotation marks, however.

4、根据要求重新创建一个用户

SYS@orcl1> create user roidba identified by "FXlv12!@";User created.SYS@orcl1> grant connect to roidba;Grant succeeded.SYS@orcl1> conn roidba/"FXlv12!@"Connected.ROIDBA@orcl1>

5、取消密码复杂度验证

SYS@orcl1> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;Profile altered.SYS@orcl1> alter user roidba identified by roidba;User altered.SYS@orcl1> 

6、设置密码复杂度验证操作过程

Enabling Password Complexity VerificationThe utlpwdmg.sql script can be customized to enable password complexity verification.Log in to SQL*Plus with administrative privileges.For example:CONNECT SYSTEMEnter password: passwordRun the utlpwdmg.sql script (or your modified version of this script) to create the password complexity functions in the SYS schema.@$ORACLE_HOME/rdbms/admin/utlpwdmg.sqlGrant any users who must use this function the EXECUTE privilege on it.For example:GRANT pmsith EXECUTE ON ora12c_strong_verify_function;In the default profile or the user profile, set the PASSWORD_VERIFY_FUNCTION setting to either the sample password complexity function in the utlpwdmg.sql script, or to your customized function. Use one of the following methods:Log in to SQL*Plus with administrator privileges and use the CREATE PROFILE or ALTER PROFILE statement to enable the function. Ensure that you have the EXECUTE privilege on the function.For example, to update the default profile to use the ora12c_strong_verify_function function:ALTER PROFILE default LIMIT  PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;