使用DBMS_FGA實現(xiàn)細粒度審計

實驗：使用DBMS_FGA實現(xiàn)細粒度審計 1，創(chuàng)建測試表 TEST_USR1@PROD1 create table audit_test (x number ); Table created. 2，創(chuàng)建審計策略 TEST_USR1@PROD1 conn / as sysdba Connected. SYS@PROD1 begin DBMS_FGA.ADD_POLICY ( object_schema = 'TEST_USR1

實驗：使用DBMS_FGA實現(xiàn)細粒度審計

1，創(chuàng)建測試表
TEST_USR1@PROD1> create table audit_test (x number );

Table created.

2，創(chuàng)建審計策略
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',
--創(chuàng)建的審計策略的名稱
audit_condition => 'x < 100',
--審計條件（可以同時審計多列）
audit_column => 'x',
--要審計的列（也可以為多列）
handler_schema => NULL,
handler_module => NULL,
--如果某些操作觸發(fā)本審計策略時，則可以指定數(shù)據(jù)庫
--后繼處理。
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，執(zhí)行觸發(fā)審計的操作
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()
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()
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
--如果在前面創(chuàng)建審計策略的時候，指定了audit_trail為
--XML的話，則執(zhí)行該sql來查詢生成的審計結(jié)果。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com