检查用户的生命周期sql

Inkcup

#检查用户密码的过期设置
SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

-- 修改 DEFAULT PROFILE
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

#查看检查即将过期的用户
SELECT
    username,
    account_status,
    expiry_date,
    CASE
        WHEN expiry_date IS NULL THEN 'nerver'
        WHEN expiry_date > sysdate + 7 THEN 'safe'
        WHEN expiry_date BETWEEN sysdate AND sysdate + 7 THEN '7days_exp'
        WHEN expiry_date < sysdate THEN 'expired'
    END as status,
    profile
FROM dba_users
WHERE account_status NOT LIKE '%LOCKED%'
ORDER BY expiry_date;