# 1. 创建角色
– 创建部门角色
SQL> CREATE ROLE fgedu_dept1_role;
SQL> CREATE ROLE fgedu_dept2_role;
SQL> CREATE ROLE fgedu_dept3_role;
# 2. 授予角色权限
– 授予部门1角色权限
SQL> GRANT CREATE SESSION TO fgedu_dept1_role;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_dept1_table TO fgedu_dept1_role;
– 授予部门2角色权限
SQL> GRANT CREATE SESSION TO fgedu_dept2_role;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_dept2_table TO fgedu_dept2_role;
– 授予部门3角色权限
SQL> GRANT CREATE SESSION TO fgedu_dept3_role;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_dept3_table TO fgedu_dept3_role;
# 3. 创建用户
– 创建部门1用户
SQL> CREATE USER fgedu_dept1_fgedu_user1 IDENTIFIED BY ‘Fgedu123456’ DEFAULT TABLESPACE fgedu TEMPORARY TABLESPACE fgedu_temp;
SQL> CREATE USER fgedu_dept1_fgedu_user2 IDENTIFIED BY ‘Fgedu123456’ DEFAULT TABLESPACE fgedu TEMPORARY TABLESPACE fgedu_temp;
– 创建部门2用户
SQL> CREATE USER fgedu_dept2_fgedu_user1 IDENTIFIED BY ‘Fgedu123456’ DEFAULT TABLESPACE fgedu TEMPORARY TABLESPACE fgedu_temp;
SQL> CREATE USER fgedu_dept2_fgedu_user2 IDENTIFIED BY ‘Fgedu123456’ DEFAULT TABLESPACE fgedu TEMPORARY TABLESPACE fgedu_temp;
– 创建部门3用户
SQL> CREATE USER fgedu_dept3_fgedu_user1 IDENTIFIED BY ‘Fgedu123456’ DEFAULT TABLESPACE fgedu TEMPORARY TABLESPACE fgedu_temp;
SQL> CREATE USER fgedu_dept3_fgedu_user2 IDENTIFIED BY ‘Fgedu123456’ DEFAULT TABLESPACE fgedu TEMPORARY TABLESPACE fgedu_temp;
# 4. 授予用户角色
– 授予部门1用户角色
SQL> GRANT fgedu_dept1_role TO fgedu_dept1_fgedu_user1;
SQL> GRANT fgedu_dept1_role TO fgedu_dept1_fgedu_user2;
– 授予部门2用户角色
SQL> GRANT fgedu_dept2_role TO fgedu_dept2_fgedu_user1;
SQL> GRANT fgedu_dept2_role TO fgedu_dept2_fgedu_user2;
– 授予部门3用户角色
SQL> GRANT fgedu_dept3_role TO fgedu_dept3_fgedu_user1;
SQL> GRANT fgedu_dept3_role TO fgedu_dept3_fgedu_user2;
# 5. 验证用户权限
– 查看用户角色
SQL> SELECT * FROM dba_role_privs WHERE grantee IN (‘FGEDU_DEPT1_USER1’, ‘FGEDU_DEPT2_USER1’, ‘FGEDU_DEPT3_USER1’);
# 输出结果
# GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
# —————— ——————– ————– ————-
# FGEDU_DEPT1_USER1 FGEDU_DEPT1_ROLE NO YES
# FGEDU_DEPT2_USER1 FGEDU_DEPT2_ROLE NO YES
# FGEDU_DEPT3_USER1 FGEDU_DEPT3_ROLE NO YES
– 查看用户权限
SQL> SELECT * FROM dba_tab_privs WHERE grantee IN (‘FGEDU_DEPT1_ROLE’, ‘FGEDU_DEPT2_ROLE’, ‘FGEDU_DEPT3_ROLE’);
# 输出结果
# GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
# —————— —— ——————- ——– ———- ———- ———-
# FGEDU_DEPT1_ROLE FGEDU FGEDU_DEPT1_TABLE FGEDU SELECT NO NO
# FGEDU_DEPT1_ROLE FGEDU FGEDU_DEPT1_TABLE FGEDU INSERT NO NO
# FGEDU_DEPT1_ROLE FGEDU FGEDU_DEPT1_TABLE FGEDU UPDATE NO NO
# FGEDU_DEPT1_ROLE FGEDU FGEDU_DEPT1_TABLE FGEDU DELETE NO NO
# FGEDU_DEPT2_ROLE FGEDU FGEDU_DEPT2_TABLE FGEDU SELECT NO NO
# FGEDU_DEPT2_ROLE FGEDU FGEDU_DEPT2_TABLE FGEDU INSERT NO NO
# FGEDU_DEPT2_ROLE FGEDU FGEDU_DEPT2_TABLE FGEDU UPDATE NO NO
# FGEDU_DEPT2_ROLE FGEDU FGEDU_DEPT2_TABLE FGEDU DELETE NO NO
# FGEDU_DEPT3_ROLE FGEDU FGEDU_DEPT3_TABLE FGEDU SELECT NO NO
# FGEDU_DEPT3_ROLE FGEDU FGEDU_DEPT3_TABLE FGEDU INSERT NO NO
# FGEDU_DEPT3_ROLE FGEDU FGEDU_DEPT3_TABLE FGEDU UPDATE NO NO
# FGEDU_DEPT3_ROLE FGEDU FGEDU_DEPT3_TABLE FGEDU DELETE NO NO
# 6. 实施结果
– 角色创建成功
– 用户创建成功
– 权限分配成功
– 最小权限原则得到实现

# 1. 开启审计功能
– 开启审计功能
SQL> ALTER SYSTEM SET ‘AUDIT_ENABLE’ = 1 BOTH;
– 查看审计状态
SQL> SELECT * FROM v$parameter WHERE name = ‘AUDIT_ENABLE’;
# 输出结果
# NAME VALUE
# ———— ———-
# AUDIT_ENABLE 1
# 2. 配置审计策略
– 配置语句级审计
SQL> AUDIT SELECT, INSERT, UPDATE, DELETE TABLE BY ACCESS;
– 配置对象级审计
SQL> AUDIT ALL ON fgedu_user BY ACCESS;
SQL> AUDIT ALL ON fgedu_order BY ACCESS;
SQL> AUDIT ALL ON fgedu_product BY ACCESS;
– 配置权限级审计
SQL> AUDIT GRANT ANY PRIVILEGE, GRANT ANY OBJECT PRIVILEGE BY ACCESS;
– 配置会话级审计
SQL> AUDIT SESSION BY ACCESS;
# 3. 配置审计日志
– 配置审计日志路径
SQL> ALTER SYSTEM SET ‘AUDIT_FILE_PATH’ = ‘/dm/audit’ BOTH;
– 配置审计日志文件大小（单位：MB）
SQL> ALTER SYSTEM SET ‘AUDIT_FILE_SIZE’ = 100 BOTH;
– 配置审计日志保留时间（单位：天）
SQL> ALTER SYSTEM SET ‘AUDIT_RETENTION’ = 90 BOTH;
– 创建审计日志目录
$ mkdir -p /dm/audit
$ chown -R dmdba:dinstall /dm/audit
# 4. 验证审计配置
– 查看审计策略
SQL> SELECT * FROM dba_audit_policies;
# 输出结果
# POLICY_NAME OBJECT_OWNER OBJECT_NAME AUDIT_OPTION AUDIT_TYPE
# —————- ————– ————- ————- ————
# FGEDU_STMT_AUDIT NULL NULL SELECT STATEMENT
# FGEDU_STMT_AUDIT NULL NULL INSERT STATEMENT
# FGEDU_STMT_AUDIT NULL NULL UPDATE STATEMENT
# FGEDU_STMT_AUDIT NULL NULL DELETE STATEMENT
# FGEDU_OBJ_AUDIT FGEDU FGEDU_USER ALL OBJECT
# FGEDU_OBJ_AUDIT FGEDU FGEDU_ORDER ALL OBJECT
# FGEDU_OBJ_AUDIT FGEDU FGEDU_PRODUCT ALL OBJECT
# FGEDU_PRIV_AUDIT NULL NULL GRANT PRIVILEGE
# FGEDU_SESSION_AUDIT NULL NULL SESSION SESSION
– 查看审计日志文件
$ ls -lh /dm/audit/
# 输出结果
# total 100M
# -rw-r–r– 1 dmdba dinstall 100M Jan 1 10:00 audit.log
# 5. 测试审计功能
– 执行测试操作
SQL> INSERT INTO fgedu_user VALUES (1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’);
SQL> UPDATE fgedu_user SET user_name = ‘fgedu_user1’ WHERE user_id = 1;
SQL> SELECT * FROM fgedu_user WHERE user_id = 1;
SQL> DELETE FROM fgedu_user WHERE user_id = 1;
– 查看审计记录
SQL> SELECT username, action_name, obj_name, sql_text, timestamp
FROM dba_audit_trail
WHERE username = ‘FGEDU_USER’
ORDER BY timestamp DESC;
# 输出结果
# USERNAME ACTION_NAME OBJ_NAME SQL_TEXT TIMESTAMP
# ————- ———— ———– —————————————– ——————-
# FGEDU_USER DELETE FGEDU_USER DELETE FROM fgedu_user WHERE user_id = 1 2024-01-01 10:00:00
# FGEDU_USER SELECT FGEDU_USER SELECT * FROM fgedu_user WHERE user_id = 1 2024-01-01 09:59:00
# FGEDU_USER UPDATE FGEDU_USER UPDATE fgedu_user SET user_name = ‘fgedu_user1’ 2024-01-01 09:58:00
# FGEDU_USER INSERT FGEDU_USER INSERT INTO fgedu_user VALUES (1, ‘fgedu_user1’) 2024-01-01 09:57:00
# 6. 实施结果
– 审计功能开启成功
– 审计策略配置成功
– 审计日志配置成功
– 审计功能测试成功

# 1. 发现安全事件
– 查看审计日志
SQL> SELECT username, action_name, timestamp, returncode
FROM dba_audit_trail
WHERE action_name = ‘LOGON’
ORDER BY timestamp DESC
LIMIT 10;
# 输出结果
# USERNAME ACTION_NAME TIMESTAMP RETURNCODE
# ————- ———— ——————- ———-
# FGEDU_USER LOGON 2024-01-01 10:00:00 0
# FGEDU_USER LOGON 2024-01-01 09:59:00 0
# FGEDU_USER LOGON 2024-01-01 09:58:00 0
# UNKNOWN_USER LOGON 2024-01-01 09:57:00 28001
– 查看登录失败记录
SQL> SELECT username, action_name, timestamp, returncode
FROM dba_audit_trail
WHERE action_name = ‘LOGON’ AND returncode != 0
ORDER BY timestamp DESC;
# 输出结果
# USERNAME ACTION_NAME TIMESTAMP RETURNCODE
# ————- ———— ——————- ———-
# UNKNOWN_USER LOGON 2024-01-01 09:57:00 28001
# UNKNOWN_USER LOGON 2024-01-01 09:56:00 28001
# UNKNOWN_USER LOGON 2024-01-01 09:55:00 28001
# 2. 分析安全事件
– 查看异常登录的详细信息
SQL> SELECT * FROM dba_audit_trail
WHERE username = ‘UNKNOWN_USER’
ORDER BY timestamp DESC;
# 输出结果
# USERNAME ACTION_NAME OBJ_NAME SQL_TEXT TIMESTAMP RETURNCODE
# ————- ———— ——— ——— ——————- ———-
# UNKNOWN_USER LOGON NULL NULL 2024-01-01 09:57:00 28001
# UNKNOWN_USER LOGON NULL NULL 2024-01-01 09:56:00 28001
# UNKNOWN_USER LOGON NULL NULL 2024-01-01 09:55:00 28001
– 查看当前会话
SQL> SELECT * FROM v$session WHERE username = ‘UNKNOWN_USER’;
# 输出结果
# SID SERIAL
# USERNAME STATUS LOGON_TIME MACHINE
# —- ——– ———— ——– ——————- —————
# 100 12345 UNKNOWN_USER INACTIVE 2024-01-01 09:57:00 192.168.1.100
# 3. 处理安全事件
– 锁定异常用户
SQL> ALTER USER UNKNOWN_USER ACCOUNT LOCK;
– 断开异常会话
SQL> ALTER SYSTEM DISCONNECT SESSION ‘100,12345’;
– 查看用户状态
SQL> SELECT username, account_status FROM dba_users WHERE username = ‘UNKNOWN_USER’;
# 输出结果
# USERNAME ACCOUNT_STATUS
# ————- —————
# UNKNOWN_USER LOCKED
# 4. 加强安全措施
– 配置防火墙规则
$ iptables -A INPUT -s 192.168.1.100 -j DROP
– 配置用户锁定策略
SQL> ALTER SYSTEM SET ‘FAILED_LOGIN_ATTEMPTS’ = 3 BOTH;
SQL> ALTER SYSTEM SET ‘PASSWORD_LOCK_TIME’ = 60 BOTH;
– 开启审计告警
SQL> CREATE PROCEDURE sp_login_failed_alert()
AS
BEGIN
SELECT username, timestamp, returncode
FROM dba_audit_trail
WHERE action_name = ‘LOGON’ AND returncode != 0 AND timestamp > SYSDATE – 1/24;
END;
# 5. 验证安全措施
– 查看用户锁定策略
SQL> SELECT * FROM v$parameter WHERE name IN (‘FAILED_LOGIN_ATTEMPTS’, ‘PASSWORD_LOCK_TIME’);
# 输出结果
# NAME VALUE
# ———————– ———-
# FAILED_LOGIN_ATTEMPTS 3
# PASSWORD_LOCK_TIME 60
– 查看防火墙规则
$ iptables -L -n | grep 192.168.1.100
# 输出结果
# DROP all — 192.168.1.100 0.0.0.0/0
# 6. 实施结果
– 异常用户锁定成功
– 异常会话断开成功
– 防火墙规则配置成功
– 用户锁定策略配置成功
– 审计告警配置成功