1.create table and insert data
SQL> create table TEST
2 (
3 X NUMBER,
4 Y NUMBER
5 )
6 /
Table created
SQL> insert into test values(1,1);
1 row inserted
SQL> commit;
Commit complete
2.Creating an FGA Policy(disabled)
SQL> exec DBMS_FGA.ADD_POLICY(object_schema=>'LPPMTEST' ,object_name=>'TEST' ,policy_name=>'TEST_AUD' ,audit_column=>'X, Y' ,enable=>FALSE ,statement_types=>'UPDATE');
PL/SQL procedure successfully completed
3.Enabling an FGA Policy
SQL> exec DBMS_FGA.ENABLE_POLICY(object_schema=>'LPPMTEST' ,object_name=>'TEST' ,policy_name=>'TEST_AUD');
PL/SQL procedure successfully completed
4.execute update
SQL> update test set x=2,y=3;
1 row updated
SQL> commit;
Commit complete
5.grant privilege
SQL> conn sys/** as sysdba
Connected.
SQL> grant select on Dba_Fga_Audit_Trail to lppmtest;
Grant succeeded.
6.Reporting on the FGA Audit Trail Entries
SQL> SELECT session_id,TIMESTAMP,db_user,os_user,userhost,sql_text FROM Dba_Fga_Audit_Trail;
SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST SQL_TEXT
---------- ----------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
16202785 2007-11-6 1 LPPMTEST H2 WORKGROUP/JACKY
update test set x=2,y=3
7.Disabling an FGA Policy
SQL> exec DBMS_FGA.DISABLE_POLICY(object_schema=>'LPPMTEST' ,object_name=>'TEST' ,policy_name=>'TEST_AUD');
PL/SQL procedure successfully completed
8.Dropping an FGA Policy
SQL> EXEC DBMS_FGA.DROP_POLICY(object_schema=>'LPPMTEST' ,object_name=>'TEST' ,policy_name=>'TEST_AUD');
PL/SQL procedure successfully completed
SQL>