【Oracle】设置FGA(Fine-Grained Audit)细粒度审计

【声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)

收集Oracle数据库内存相关的信息
【Oracle】ORA-32017和ORA-00384错误处理

【Oracle】设置FGA(Fine-Grained Audit)细粒度审计

FGA(Fine-Grained Audit)细粒度审计是Oracle提供的一种数据库审计方法，用于创建定制的审计设置。
可以通过调用Oracle的包DBMS_FGA.ADD_POLICY创建policy(审计策略)。

下面是一个简单的测试。

1. 设置FGA审计策略

 conn scott/tiger

 BEGIN DBMS_FGA.ADD_POLICY(object_schema => 'SCOTT',
 object_name => 'EMP',
 policy_name => 'EMP_ENAME',
 audit_column => 'ENAME',
 enable  => TRUE,
 statement_types => 'DELETE,UPDATE,INSERT,SELECT',
 audit_trail => DBMS_FGA.DB_EXTENDED,
 audit_column_opts =>DBMS_FGA.ANY_COLUMNS);
 END;
 /

２．查看设置的审计策略

 set pages 1000
 set line 200
 set trims on
 col OBJECT_SCHEMA for a12
 col OBJECT_NAME   for a15
 col POLICY_NAME   for a30
 col POLICY_COLUMN for a25
 col AUDIT_TRAIL   for a12
 
 select OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME,ENABLED,SEL,INS,UPD,DEL,AUDIT_TRAIL,POLICY_COLUMN 
 from ALL_AUDIT_POLICIES
 order by OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME;

3. 重启数据库

conn / as sysdba
shutdown immediate
startup

4.执行 expdp导出数据（成功导出12条数据）

 expdp scott/tiger directory=DUMP_DIR DUMPFILE=emp.dmp LOGFILE=DUMP_DIR:emp.log REUSE_DUMPFILES=Y TABLES=SCOTT.EMP

5. 删除数据trunccate

 conn scott/tiger
 truncate table emp;

6.执行impdp导入数据（成功导入12条数据）
impdp scott/tiger directory=DUMP_DIR DUMPFILE=emp.dmp LOGFILE=DUMP_DIR:emp.log TABLES=SCOTT.EMP CONTENT=DATA_ONLY

SQL> host  impdp scott/tiger directory=DUMP_DIR DUMPFILE=emp.dmp LOGFILE=DUMP_DIR:emp.log TABLES=SCOTT.EMP CONTENT=DATA_ONLY

Import: Release 19.0.0.0.0 - Production on 星期日 1月 21 09:41:07 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
已成功加载/卸载了主表 "SCOTT"."SYS_IMPORT_TABLE_01"
启动 "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=DUMP_DIR DUMPFILE=emp.dmp LOGFILE=DUMP_DIR:emp.log TABLES=SCOTT.EMP CONTENT=DATA_ONLY
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "SCOTT"."EMP"                               9.132 KB      12 行
作业 "SCOTT"."SYS_IMPORT_TABLE_01" 已于 星期日 1月 21 09:41:15 2024 elapsed 0 00:00:07 成功完成

7.确认FGA_LOG$的审计结果（FG审计log中输出import的操作记录）

 conn sys/PW
 set lin 2000 
 set pages 1000 
 col DB_USER format a10
 col OS_USER format a10
 col POLICY_NAME format a20
 col SQL_TEXT format a100
 select to_char(TIMESTAMP,'YYYYMMDDHH24MISS'),DB_USER,OS_USER,POLICY_NAME,SQL_TEXT 
 from dba_fga_audit_trail 
 order by timestamp desc; 

结果例：

SQL>  select to_char(TIMESTAMP,'YYYYMMDDHH24MISS'),DB_USER,OS_USER,POLICY_NAME,SQL_TEXT
  2   from dba_fga_audit_trail
  3   order by timestamp desc;

TO_CHAR(TIMESTAMP,'YYYYMMDDH DB_USER    OS_USER    POLICY_NAME          SQL_TEXT
---------------------------- ---------- ---------- -------------------- ----------------------------------------------------------------------------------------------------
20240121094115               SCOTT      OracleServ EMP_ENAME            INSERT /*+ APPEND PARALLEL("EMP",1)+*/ INTO RELATIONAL("SCOTT"."EMP" NOT XMLTYPE) ("EMPNO","ENAME","
                                        iceORCL                         JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
                                                                           SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
                                                                           FROM "ET$01EF9D620001" KU$

8.测试结束，删除策略。

BEGIN
 DBMS_FGA.DROP_POLICY(
  object_schema      => 'SCOTT',
  object_name        => 'EMP',
  policy_name        => 'EMP_ENAME');
END;
/

参考

32 Auditing Specific Activities with Fine-Grained Auditing
https://docs.oracle.com/en/database/oracle/oracle-database/23/dbseg/auditing-specific-activities-fine-grained-auditing1.html#GUID-B706FF6F-13A6-4944-AFCB-29971F5076FD