重庆思庄oracle技术分享-检查用户的生命周期sql

#检查用户密码的过期设置
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;