— 创建密码验证函数
CREATE OR REPLACE FUNCTION verify_password (
username IN VARCHAR2,
password IN VARCHAR2,
old_password IN VARCHAR2
) RETURN BOOLEAN AS
min_length NUMBER := 8;has_uppercase BOOLEAN := FALSE;has_lowercase BOOLEAN := FALSE;has_number BOOLEAN := FALSE;has_special BOOLEAN := FALSE;BEGIN
— 检查密码长度
IF LENGTH(password) < min_length THEN RETURN FALSE;END IF;-- 检查密码复杂性 FOR i IN 1..LENGTH(password) LOOP IF REGEXP_LIKE(SUBSTR(password, i, 1), '[A-Z]') THEN has_uppercase := TRUE;ELSIF REGEXP_LIKE(SUBSTR(password, i, 1), '[a-z]') THEN has_lowercase := TRUE;ELSIF REGEXP_LIKE(SUBSTR(password, i, 1), '[0-9]') THEN has_number := TRUE;ELSIF REGEXP_LIKE(SUBSTR(password, i, 1), '[^a-zA-Z0-9]') THEN has_special := TRUE;END IF;END LOOP;IF NOT (has_uppercase AND has_lowercase AND has_number) THEN RETURN FALSE;END IF;RETURN TRUE;END;/

— 创建包含密码策略的配置文件
CREATE PROFILE secure_password_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 10
PASSWORD_VERIFY_FUNCTION verify_password
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7;– 为用户分配配置文件
ALTER USER app_user PROFILE secure_password_profile;– 查看密码策略设置
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile = ‘SECURE_PASSWORD_PROFILE’
AND resource_type = ‘PASSWORD’;

— 密码策略参数说明
— FAILED_LOGIN_ATTEMPTS: 失败登录尝试次数，超过后账户锁定
— PASSWORD_LIFE_TIME: 密码有效期（天）
— PASSWORD_REUSE_TIME: 密码重用时间（天）
— PASSWORD_REUSE_MAX: 密码重用前必须更改的次数
— PASSWORD_VERIFY_FUNCTION: 密码验证函数
— PASSWORD_LOCK_TIME: 账户锁定时间（天）
— PASSWORD_GRACE_TIME: 密码过期后的宽限期（天）

— 修改密码策略
ALTER PROFILE secure_password_profile LIMIT
PASSWORD_LIFE_TIME 60
FAILED_LOGIN_ATTEMPTS 5;

— 1. 创建密码验证函数
SQL> CREATE OR REPLACE FUNCTION verify_password (
username IN VARCHAR2,
password IN VARCHAR2,
old_password IN VARCHAR2
) RETURN BOOLEAN AS
min_length NUMBER := 10;has_uppercase BOOLEAN := FALSE;has_lowercase BOOLEAN := FALSE;has_number BOOLEAN := FALSE;has_special BOOLEAN := FALSE;BEGIN
— 检查密码长度
IF LENGTH(password) < min_length THEN RETURN FALSE;END IF;-- 检查密码复杂性 FOR i IN 1..LENGTH(password) LOOP IF REGEXP_LIKE(SUBSTR(password, i, 1), '[A-Z]') THEN has_uppercase := TRUE;ELSIF REGEXP_LIKE(SUBSTR(password, i, 1), '[a-z]') THEN has_lowercase := TRUE;ELSIF REGEXP_LIKE(SUBSTR(password, i, 1), '[0-9]') THEN has_number := TRUE;ELSIF REGEXP_LIKE(SUBSTR(password, i, 1), '[^a-zA-Z0-9]') THEN has_special := TRUE;END IF;END LOOP;IF NOT (has_uppercase AND has_lowercase AND has_number AND has_special) THEN RETURN FALSE;END IF;RETURN TRUE;END;/Function created. -- 2. 创建安全配置文件 SQL> CREATE PROFILE enterprise_secure_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 10
PASSWORD_VERIFY_FUNCTION verify_password
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7;Profile created.

— 3. 为用户分配配置文件
SQL> ALTER USER app_user PROFILE enterprise_secure_profile;SQL> ALTER USER sysadmin PROFILE enterprise_secure_profile;– 4. 验证密码策略
SQL> SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile = ‘ENTERPRISE_SECURE_PROFILE’
AND resource_type = ‘PASSWORD’;PROFILE RESOURCE_NAME LIMIT
———————– ————————- ——————–
ENTERPRISE_SECURE_PROF FAILED_LOGIN_ATTEMPTS 3
ENTERPRISE_SECURE_PROF PASSWORD_LIFE_TIME 90
ENTERPRISE_SECURE_PROF PASSWORD_REUSE_TIME 365
ENTERPRISE_SECURE_PROF PASSWORD_REUSE_MAX 10
ENTERPRISE_SECURE_PROF PASSWORD_VERIFY_FUNCTION VERIFY_PASSWORD
ENTERPRISE_SECURE_PROF PASSWORD_LOCK_TIME 1
ENTERPRISE_SECURE_PROF PASSWORD_GRACE_TIME 7

— 1. 查看当前默认密码策略
SQL> SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile = ‘DEFAULT’
AND resource_type = ‘PASSWORD’;PROFILE RESOURCE_NAME LIMIT
———- ————————- ——————–
DEFAULT FAILED_LOGIN_ATTEMPTS 10
DEFAULT PASSWORD_LIFE_TIME UNLIMITED
DEFAULT PASSWORD_REUSE_TIME UNLIMITED
DEFAULT PASSWORD_REUSE_MAX UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
DEFAULT PASSWORD_LOCK_TIME UNLIMITED
DEFAULT PASSWORD_GRACE_TIME 7

— 2. 修改默认密码策略
SQL> ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME 180
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7;Profile altered.

— 3. 验证修改
SQL> SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile = ‘DEFAULT’
AND resource_type = ‘PASSWORD’;PROFILE RESOURCE_NAME LIMIT
———- ————————- ——————–
DEFAULT FAILED_LOGIN_ATTEMPTS 5
DEFAULT PASSWORD_LIFE_TIME 90
DEFAULT PASSWORD_REUSE_TIME 180
DEFAULT PASSWORD_REUSE_MAX UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
DEFAULT PASSWORD_LOCK_TIME 1
DEFAULT PASSWORD_GRACE_TIME 7

— 1. 创建测试用户
SQL> CREATE USER test_user IDENTIFIED BY Test123
PROFILE enterprise_secure_profile;User created.

— 2. 尝试使用弱密码修改密码（应该失败）
SQL> ALTER USER test_user IDENTIFIED BY password;ERROR: ORA-28003: password verification for the specified password failed
ORA-20001: Password must contain at least one uppercase letter, one lowercase letter, one number, and one special character.

— 3. 使用强密码修改密码（应该成功）
SQL> ALTER USER test_user IDENTIFIED BY Test123!;User altered.

— 4. 测试失败登录尝试
— 尝试使用错误密码登录3次，账户应该被锁定
SQL> CONN test_user/wrong_password;ERROR: ORA-01017: invalid username/password; logon denied

SQL> CONN test_user/wrong_password;ERROR: ORA-01017: invalid username/password; logon denied

SQL> CONN test_user/wrong_password;ERROR: ORA-28000: the account is locked

— 5. 解锁账户
SQL> ALTER USER test_user ACCOUNT UNLOCK;User altered.