使用Oracle自带profile以及函数设定密码规则

$ sqlplus / as sysdbaSQL > select profile,resource_name,resource_type,limit from dba_profiles where profile=(select PROFILE from dba_users where username=upper('&user_name'));

$ sqlplus / as sysdbaSQL > @?/rdbms/admin/utlpwdmg.sql 

l   最小长度8

l   不能与用户名相同或相似

l   不能是用户名倒序。

l   不能与前密码超过3个字符相同

l   至少包含一个“\”、“数字”以及“字符”

$ sqlplus / as sysdbaSQL> select 'alter profile ' || profile ||       ' limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;'  from dba_profiles group by profile; 'ALTERPROFILE'||PROFILE||'LIMITPASSWORD_VERIFY_FUNCTIONVERIFY_FUNCTION;'--------------------------------------------------------------------------------------------alter profile MONITORING_PROFILE limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;    # 使用需要修改profile相应语句即可 SYS@honor1 > alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION; Profile altered. SYS@honor1 > alter user hr identified by 111111;         # 规则已经生效，简单密码已经无法使用alter user hr identified by 111111*ERROR at line 1:ORA-28003: password verification for the specified password failedORA-20003: Password should contain at least one \digit, one character and one punctuation 

SYS@honor1 > CREATE PROFILE new_profile LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED CONNECT_TIME UNLIMITED IDLE_TIME 600LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED COMPOSITE_LIMIT UNLIMITED PRIVATE_SGA UNLIMITED FAILED_LOGIN_ATTEMPTS UNLIMITEDPASSWORD_LIFE_TIME UNLIMITEDPASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_LOCK_TIME 1PASSWORD_GRACE_TIME 10PASSWORD_VERIFY_FUNCTION verify_function;Profile created. SYS@honor1 > alter user hr profile new_profile; User altered. SYS@honor1 > alter user hr identified by 111111;alter user hr identified by 111111*ERROR at line 1:ORA-28003: password verification for the specified password failed   # 新规则已经生效ORA-20003: Password should contain at least one \digit, one character and one punctuation 

SYS@honor1 > alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION null; Profile altered. SYS@honor1 > alter user hr identified by 111111;     # 可以看到密码规则已经失效 User altered.

SYS@honor1 > alter user hr profile default;         # 如果原来使用了自定义profile则输入原来名称 User altered. SYS@honor1 > alter user hr identified by 111111;  # 可以看到密码规则已经失效 User altered.

4. 密码有效期

SYS@honor1 > alter profile DEFAULT limit PASSWORD_LIFE_TIME 90;      # 修改为90天 Profile altered. SYS@honor1 > select profile,resource_name,resource_type,limit from dba_profiles where profile='DEFAULT';  Caution: 设定较短有效期，一定要注意密码有效期，及时在有效期前修改密码，防止密码过期导致应用连接数据库失败。

5. 同一密码再次使用间隔

SYS@honor1 > alter profile default limit PASSWORD_REUSE_TIME 365; Profile altered. SYS@honor1 > select profile,resource_name,resource_type,limit from dba_profiles where profile='DEFAULT';

6. 同一密码可被使用次数

SYS@honor1 > alter profile default limit PASSWORD_REUSE_MAX 5; Profile altered. SYS@honor1 > select profile,resource_name,resource_type,limit from dba_profiles where profile='DEFAULT';

7. 回退上述设置

SYS@honor1 > alter profile DEFAULT limit PASSWORD_LIFE_TIME 180;SYS@honor1 > alter profile default limit PASSWORD_REUSE_TIME UNLIMITED;SYS@honor1 > alter profile default limit PASSWORD_REUSE_MAX UNLIMITED;