使用DBMS_FGA实现细粒度审计

实验：使用DBMS_FGA实现细粒度审计

1，创建测试表
TEST_USR1@PROD1> create table audit_test (x number );

Table created.

2，创建审计策略
TEST_USR1@PROD1> conn / as sysdba
Connected.
SYS@PROD1> begin
DBMS_FGA.ADD_POLICY (
   object_schema      =>  ‘TEST_USR1‘,
   --要审计的对象所属的schema
   object_name        =>  ‘AUDIT_TEST‘,
   --要审计的对象名称
   policy_name        =>  ‘mypolicy1‘,
   --创建的审计策略的名称
   audit_condition    =>  ‘x < 100‘, 
   --审计条件（可以同时审计多列）
   audit_column       =>  ‘x‘,   
   --要审计的列（也可以为多列）   
   handler_schema     =>   NULL,
   handler_module     =>   NULL,
   --如果某些操作触发本审计策略时，则可以指定数据库
   --后继处理。
   enable             =>   TRUE,
   statement_types    =>  ‘INSERT, UPDATE‘,
   --要审计的操作类型（insert，update，delete，select）
   audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED,
   --指定了审计记录的存放位置
   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);
   --是否针对所有列都进行该审计操作。
end;
/
   
PL/SQL procedure successfully completed.

SYS@PROD1> col OBJECT_SCHEMA a10
SYS@PROD1> col OBJECT_SCHEMA for a10
SYS@PROD1> col OBJECT_NAME for a10
SYS@PROD1> co POLICY_NAME for a10
SYS@PROD1> col POLICY_NAME for a10
SYS@PROD1> select OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME,ENABLED from dba_audit_policies;

OBJECT_SCH OBJECT_NAM POLICY_NAM ENA
---------- ---------- ---------- ---
TEST_USR1  AUDIT_TEST MYPOLICY1  YES

3，执行触发审计的操作
SYS@PROD1> conn test_usr1/test;
Connected.
TEST_USR1@PROD1> insert into audit_test values (2);

1 row created.

TEST_USR1@PROD1> insert into audit_test values (101);

1 row created.

TEST_USR1@PROD1> commit;

Commit complete.

4，查看生成的审计记录
TEST_USR1@PROD1> select count(*) from sys.fga_log$;

  COUNT(*)
----------
         1

TEST_USR1@PROD1> desc sys.fga_log$;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SESSIONID                                 NOT NULL NUMBER
 TIMESTAMP#                                         DATE
 DBUID                                              VARCHAR2(30)
 OSUID                                              VARCHAR2(255)
 OSHST                                              VARCHAR2(128)
 CLIENTID                                           VARCHAR2(64)
 EXTID                                              VARCHAR2(4000)
 OBJ$SCHEMA                                         VARCHAR2(30)
 OBJ$NAME                                           VARCHAR2(128)
 POLICYNAME                                         VARCHAR2(30)
 SCN                                                NUMBER
 SQLTEXT                                            VARCHAR2(4000)
 LSQLTEXT                                           CLOB
 SQLBIND                                            VARCHAR2(4000)
 COMMENT$TEXT                                       VARCHAR2(4000)
 PLHOL                                              LONG
 STMT_TYPE                                          NUMBER
 NTIMESTAMP#                                        TIMESTAMP(6)
 PROXY$SID                                          NUMBER
 USER$GUID                                          VARCHAR2(32)
 INSTANCE#                                          NUMBER
 PROCESS#                                           VARCHAR2(16)
 XID                                                RAW(8)
 AUDITID                                            VARCHAR2(64)
 STATEMENT                                          NUMBER
 ENTRYID                                            NUMBER
 DBID                                               NUMBER
 LSQLBIND                                           CLOB
 OBJ$EDITION                                        VARCHAR2(30)

TEST_USR1@PROD1> select POLICYNAME,OBJ$SCHEMA,OBJ$NAME,LSQLTEXT from sys.fga_log$;

POLICYNAME     OBJ$SCHEMA  OBJ$NAME    LSQLTEXT
----------------------------------------------------------
MYPOLICY1       TEST_USR1  AUDIT_TEST   insert into audit_test values (2)

TEST_USR1@PROD1>  select OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME,SQL_TEXT from V$XML_AUDIT_TRAIL;

no rows selected
--如果在前面创建审计策略的时候，指定了audit_trail为
--XML的话，则执行该sql来查询生成的审计结果。