修改oracle密码策略

-- 查看当前用户使用的 profile 及口令策略setlinesize400col profilefora20 col resource_namefora30 col limitfora40 SELECT profile, resource_name, limit FROM dba_profiles WHERE resource_name IN('PASSWORD_LIFE_TIME','PASSWORD_GRACE_TIME','PASSWORD_REUSE_TIME','PASSWORD_REUSE_MAX','PASSWORD_LOCK_TIME','FAILED_LOGIN_ATTEMPTS','PASSWORD_VERIFY_FUNCTION')ORDER BY profile, resource_name;-- 口令有效期180天 ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME180;-- 宽限期7天，期间登录会提示"口令即将过期，请修改"ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME7;-- 创建定时任务，每天检查过期超过7天的账户并锁定 BEGIN DBMS_SCHEDULER.CREATE_JOB(job_name=>'LOCK_EXPIRED_ACCOUNTS', job_type=>'PLSQL_BLOCK', job_action=>' BEGIN FOR u IN (SELECT username FROM dba_users WHERE account_status = ''EXPIRED(GRACE)''AND expiry_date<SYSDATE -7)LOOP EXECUTE IMMEDIATE''ALTERUSER"'' || u.username || ''"ACCOUNT LOCK'';END LOOP;END;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=DAILY;BYHOUR=2', enabled => TRUE, comments => '锁定口令过期超过7天未修改的账户' ); END; / -- 连续失败 5 次后锁定账户 ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 5; -- 锁定 1 分钟后自动解锁（单位：天，1/1440 = 1分钟） ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME 1/1440; -- 确认所有策略已生效 SELECT resource_name, limit FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_name IN ('FAILED_LOGIN_ATTEMPTS', 'PASSWORD_LOCK_TIME', 'PASSWORD_LIFE_TIME', 'PASSWORD_GRACE_TIME');RESOURCE_NAME LIMIT -------------------------- ----- FAILED_LOGIN_ATTEMPTS5PASSWORD_LOCK_TIME .000694(即1/1440)PASSWORD_LIFE_TIME180PASSWORD_GRACE_TIME7-- 创建专用 Profile CREATE PROFILE SECURE_PROFILE LIMIT PASSWORD_LIFE_TIME180PASSWORD_GRACE_TIME7FAILED_LOGIN_ATTEMPTS5PASSWORD_LOCK_TIME1/1440;-- 分配给目标用户 ALTERUSERtarget_user PROFILE SECURE_PROFILE;ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX UNLIMITED;