修改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_nameLOCK_EXPIRED_ACCOUNTS, job_typePLSQL_BLOCK, job_action BEGIN FOR u IN (SELECT username FROM dba_users WHERE account_status EXPIRED(GRACE)AND expiry_dateSYSDATE -7)LOOP EXECUTE IMMEDIATEALTERUSER || u.username || ACCOUNT LOCK;END LOOP;END;, start_date SYSTIMESTAMP, repeat_interval FREQDAILY;BYHOUR2, 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;