— 设置密码过期策略
ALTER PROFILE app_user_profile LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 7
PASSWORD_VERIFY_FUNCTION verify_password;– 为用户分配配置文件
ALTER USER app_user PROFILE app_user_profile;– 查看用户密码状态
SELECT username, account_status, expiry_date, profile
FROM dba_users
WHERE username = ‘FGAPP_USER’;

— 检查密码即将过期的用户
SELECT username, profile,
expiry_date,
TRUNC(expiry_date – SYSDATE) as days_left
FROM dba_users
WHERE expiry_date IS NOT NULL
AND expiry_date – SYSDATE BETWEEN 1 AND 7
ORDER BY days_left;– 重置用户密码
ALTER USER app_user IDENTIFIED BY new_password;– 解锁已过期的用户
ALTER USER app_user ACCOUNT UNLOCK;

— 查看用户密码状态
SELECT username,
account_status,
expiry_date,
profile,
created,
lock_date,
expiry_date – SYSDATE as days_until_expiry
FROM dba_users
WHERE username = ‘FGAPP_USER’;– 查看配置文件的密码设置
SELECT profile,
resource_name,
limit
FROM dba_profiles
WHERE resource_type = ‘PASSWORD’
AND profile = ‘APP_USER_PROFILE’;

— 1. 创建配置文件
SQL> CREATE PROFILE secure_password_profile LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 7
PASSWORD_VERIFY_FUNCTION verify_password
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1;Profile created.

— 2. 为用户分配配置文件
SQL> ALTER USER app_user PROFILE secure_password_profile;SQL> ALTER USER sysadmin PROFILE secure_password_profile;– 3. 查看用户密码状态
SQL> SELECT username, account_status, expiry_date,
TRUNC(expiry_date – SYSDATE) as days_until_expiry
FROM dba_users
WHERE username IN (‘FGAPP_USER’, ‘SYSADMIN’);USERNAME ACCOUNT_STATUS EXPIRY_DATE DAYS_UNTIL_EXPIRY
———- ——————– ———— —————–
FGAPP_USER OPEN 2026-06-30 90
SYSADMIN OPEN 2026-06-30 90

— 1. 创建密码过期监控脚本
SQL> SET LINESIZE 120
SQL> COLUMN username FORMAT A20
SQL> COLUMN profile FORMAT A20
SQL> COLUMN account_status FORMAT A20
SQL> COLUMN days_left FORMAT 999

SQL> SELECT username,
profile,
account_status,
TRUNC(expiry_date – SYSDATE) as days_left
FROM dba_users
WHERE expiry_date IS NOT NULL
AND expiry_date – SYSDATE BETWEEN 1 AND 7
ORDER BY days_left;USERNAME PROFILE ACCOUNT_STATUS DAYS_LEFT
——————– ——————– ——————– ———
TEST_USER SECURE_PASSWORD_PROF OPEN 3
FGAPP_USER SECURE_PASSWORD_PROF OPEN 5

— 2. 发送密码过期提醒（示例）
— 可以通过邮件或其他方式发送提醒

— 1. 查看用户密码状态
SQL> SELECT username, account_status, expiry_date
FROM dba_users
WHERE username = ‘FGAPP_USER’;USERNAME ACCOUNT_STATUS EXPIRY_DATE
———- ——————– ————
FGAPP_USER EXPIRED 2026-03-31

— 2. 重置用户密码
SQL> ALTER USER app_user IDENTIFIED BY NewPass123!;User altered.

— 3. 验证密码状态
SQL> SELECT username, account_status, expiry_date
FROM dba_users
WHERE username = ‘FGAPP_USER’;USERNAME ACCOUNT_STATUS EXPIRY_DATE
———- ——————– ————
FGAPP_USER OPEN 2026-06-29

— 4. 处理密码过期且锁定的用户
SQL> SELECT username, account_status
FROM dba_users
WHERE account_status LIKE ‘%EXPIRED%LOCKED%’;USERNAME ACCOUNT_STATUS
———- ——————–
TEST_USER EXPIRED & LOCKED

— 5. 解锁并重置密码
SQL> ALTER USER test_user ACCOUNT UNLOCK;SQL> ALTER USER test_user IDENTIFIED BY NewPass456!;User altered.