Oracle 数据库安全审计

1.1、传统审计 vs 统一审计特性传统审计AUD$统一审计12c接口AUDIT / NOAUDIT 语句CREATE AUDIT POLICY存储SYS.AUD$ 表UNIFIED_AUDIT_TRAIL性能每条审计一条 INSERT批量写入性能更好灵活性有限支持条件、列、行级审计管理直接操作 AUD$ 表DBMS_AUDIT_MGMT 包默认启用需要设置 audit_trail 参数12c 默认启用混合模式1.2、统一审计策略管理-- 审计所有 DDL 操作CREATE AUDIT POLICY ddl_audit_policyACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE,CREATE INDEX, DROP INDEX,CREATE VIEW, DROP VIEW,CREATE USER, ALTER USER, DROP USER;-- 审计特定表的 DMLCREATE AUDIT POLICY dml_hr_policyACTIONS SELECT ON hr.employees,INSERT ON hr.employees,UPDATE ON hr.employees,DELETE ON hr.employees;-- 审计登录行为CREATE AUDIT POLICY login_policyACTIONS LOGON, LOGOFF;-- 审计权限使用CREATE AUDIT POLICY priv_policySYSTEM PRIVILEGES SELECT ANY TABLE, DROP ANY TABLE, ALTER SYSTEM, GRANT ANY ROLE;-- 审计角色使用CREATE AUDIT POLICY role_policy ROLES DBA, IMP_FULL_DATABASE;-- 带条件的审计CREATE AUDIT POLICY after_hours_policyACTIONS LOGONWHEN TO_NUMBER(TO_CHAR(SYSDATE,HH24)) NOT BETWEEN 8 AND 18EVALUATE PER SESSION;-- 审计 SYSDBA/SYSOPER 操作CREATE AUDIT POLICY sysdba_policy PRIVILEGES SYSDBA, SYSOPER;-- 启用审计策略AUDIT POLICY ddl_audit_policy;AUDIT POLICY login_policy;AUDIT POLICY dml_hr_policy BY hr_admin;AUDIT POLICY sysdba_policy;-- 查看已启用的审计策略SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;-- 查看所有审计策略SELECT POLICY_NAME, AUDIT_CONDITION, AUDIT_OPTION FROM AUDIT_UNIFIED_POLICIES;-- 禁用审计策略NOAUDIT POLICY ddl_audit_policy;-- 删除审计策略DROP AUDIT POLICY ddl_audit_policy;1.3、细粒度审计FGA-- 创建 FGA 策略审计查询 salary 列BEGINDBMS_FGA.ADD_POLICY(object_schema HR,object_name EMPLOYEES,policy_name AUDIT_SALARY,audit_column SALARY,audit_condition SALARY 10000,statement_types SELECT,UPDATE,audit_trail DBMS_FGA.DB DBMS_FGA.EXTENDED);END;-- 查看 FGA 审计记录SELECT TIMESTAMP, DB_USER, OBJECT_NAME, SQL_TEXT, SCNFROM DBA_FGA_AUDIT_TRAILWHERE POLICY_NAME AUDIT_SALARYORDER BY TIMESTAMP DESC;-- 禁用 FGA 策略BEGINDBMS_FGA.DISABLE_POLICY(HR, EMPLOYEES, AUDIT_SALARY);END;-- 删除 FGA 策略BEGINDBMS_FGA.DROP_POLICY(HR, EMPLOYEES, AUDIT_SALARY);END;1.4、SYSDBA/SYSOPER 审计-- 确保 SYSDBA 审计已启用--在 init.ora 或 spfile 中 AUDIT_SYS_OPERATIONS TRUE-- 查看 SYSDBA 操作的审计记录SELECT USERNAME, TIMESTAMP, ACTION_NAME, SQL_TEXTFROM UNIFIED_AUDIT_TRAILWHERE PRIVILEGED YESORDER BY TIMESTAMP DESC;-- 传统方式查看 SYS 审计OS 文件-- SYS 审计记录默认写入 OS 文件-- $ADR_HOME/audit/ 目录下1.5、审计记录管理与归档-- 初始化审计清理BEGINDBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,default_cleanup_interval 24);END;-- 设置归档时间戳BEGINDBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,last_archive_time SYSTIMESTAMP - 180);END;-- 执行清理BEGINDBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,use_last_arch_time TRUE);END;-- 查看审计记录统计SELECT COUNT(*) AS total,MIN(EVENT_TIMESTAMP) AS earliest,MAX(EVENT_TIMESTAMP) AS latestFROM UNIFIED_AUDIT_TRAIL;-- 按操作类型统计SELECT ACTION_NAME, COUNT(*) AS cntFROM UNIFIED_AUDIT_TRAILGROUP BY ACTION_NAME ORDER BY cnt DESC;实例等保审计——全量 DDL/DML 审计S — Situation场景某军工企业要求所有 DDL 操作必须审计、核心表 DML 审计、所有登录行为审计审计记录保留 180 天。T — Task任务设计并实施完整的统一审计方案。A — Action行动1、创建 3 个审计策略DDL/DML/LOGIN2、启用策略并验证审计记录3、配置自动归档保留 180 天4、创建审计报表视图。R — Result结果方案上线后覆盖所有关键操作。一次安全演练中10 分钟内还原完整操作链路获得测评机构高度评价。