实验：使用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来查询生成的审计结果。