— 设置账户锁定策略
ALTER PROFILE app_user_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1;– 为用户分配配置文件
ALTER USER app_user PROFILE app_user_profile;– 手动锁定用户账户
ALTER USER app_user ACCOUNT LOCK;– 解锁用户账户
ALTER USER app_user ACCOUNT UNLOCK;– 查看用户账户状态
SELECT username, account_status, lock_date
FROM dba_users
WHERE username = ‘FGAPP_USER’;

— 查看被锁定的用户
SELECT username, account_status, lock_date
FROM dba_users
WHERE account_status LIKE ‘%LOCKED%’;– 重置用户密码并解锁
ALTER USER app_user IDENTIFIED BY new_password ACCOUNT UNLOCK;– 修改账户锁定策略
ALTER PROFILE app_user_profile LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 2;

— 查看用户账户状态
SELECT username,
account_status,
lock_date,
created,
expiry_date
FROM dba_users
WHERE username = ‘FGAPP_USER’;– 查看配置文件的账户锁定设置
SELECT profile,
resource_name,
limit
FROM dba_profiles
WHERE resource_type = ‘PASSWORD’
AND profile = ‘APP_USER_PROFILE’
AND resource_name IN (‘FAILED_LOGIN_ATTEMPTS’, ‘PASSWORD_LOCK_TIME’);

— 1. 创建配置文件
SQL> CREATE PROFILE secure_account_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 2
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 7;Profile created.

— 2. 为用户分配配置文件
SQL> ALTER USER app_user PROFILE secure_account_profile;SQL> ALTER USER sysadmin PROFILE secure_account_profile;– 3. 查看配置文件设置
SQL> SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile = ‘SECURE_ACCOUNT_PROFILE’
AND resource_type = ‘PASSWORD’;PROFILE RESOURCE_NAME LIMIT
———————– ————————- ——————–
SECURE_ACCOUNT_PROFILE FAILED_LOGIN_ATTEMPTS 3
SECURE_ACCOUNT_PROFILE PASSWORD_LIFE_TIME 90
SECURE_ACCOUNT_PROFILE PASSWORD_REUSE_TIME UNLIMITED
SECURE_ACCOUNT_PROFILE PASSWORD_REUSE_MAX UNLIMITED
SECURE_ACCOUNT_PROFILE PASSWORD_VERIFY_FUNCTION NULL
SECURE_ACCOUNT_PROFILE PASSWORD_LOCK_TIME 2
SECURE_ACCOUNT_PROFILE PASSWORD_GRACE_TIME 7

— 1. 查看被锁定的用户
SQL> SELECT username, account_status, lock_date
FROM dba_users
WHERE account_status LIKE ‘%LOCKED%’;USERNAME ACCOUNT_STATUS LOCK_DATE
———- ——————– ———
TEST_USER LOCKED 2026-03-31
FGAPP_USER EXPIRED & LOCKED 2026-03-30

— 2. 分析锁定原因
— 对于TEST_USER，可能是密码错误次数过多
— 对于APP_USER，可能是密码过期后未及时更新

— 3. 处理被锁定的账户
— 解锁并重置密码
SQL> ALTER USER test_user IDENTIFIED BY NewPass123! ACCOUNT UNLOCK;User altered.

— 解锁密码过期的用户
SQL> ALTER USER app_user IDENTIFIED BY NewPass456! ACCOUNT UNLOCK;User altered.

— 1. 手动锁定用户账户
SQL> ALTER USER test_user ACCOUNT LOCK;User altered.

— 2. 验证账户状态
SQL> SELECT username, account_status
FROM dba_users
WHERE username = ‘TEST_USER’;USERNAME ACCOUNT_STATUS
———- ——————–
TEST_USER LOCKED

— 3. 手动解锁用户账户
SQL> ALTER USER test_user ACCOUNT UNLOCK;User altered.

— 4. 验证账户状态
SQL> SELECT username, account_status
FROM dba_users
WHERE username = ‘TEST_USER’;USERNAME ACCOUNT_STATUS
———- ——————–
TEST_USER OPEN