Oracle Fine-grained (FGA) audits

Oracle9i Database introduces a new feature called Fine-grained auditing (FGA). Now the following is the use of FGA to achieve audit of the table audit. Where EDMs is a test account for the database.

1. Create a test table (under User EDMs)

Create Table T_audit_demo
(
CID INT not NULL,
CNAME VARCHAR2() NULL,
ename VARCHAR2() NULL,
CONSTRAINT Pk_t_audit_demo PRIMARY KEY (CID)
);

2. Create an Audit policy ( under user sys)

Begin
Dbms_fga.add_policy
(
Object_schema=> ' EDMS ',
Object_name=> ' T_audit_demo ',
Policy_name=> ' T_audit_demo_audit '
);
End;

3. test the Audit effect ( under User EDMs)

INSERT  into T_audit_demo VALUES(1, ' Zengxun ', ' ZENG XUN ');
INSERT  into T_audit_demo VALUES(2, ' Weng Dawn ', ' WENG LI MING ');
INSERT  into T_audit_demo VALUES(3, ' Geot Yong ', ' LIU DI YONG ');

4. use Select to query the test table ( under User EDMs)

Sql> SELECT * from T_audit_demo;
CID CNAME ename
----- -------------------- -------
1 Zengxun ZENG XUN
2 Weng liming WENG li MING
3 Geot Yong LIU DI YONG

5. Review the audit effect again ( user sys)

Sql> select Statement_type,sql_text from Dba_fga_audit_trail;
Statement_type Sql_text
-------------- -----------
Select SELECT * FROM T_audit_demo

Note: The previous INSERT statement is not in the audit. The default is to audit only select. It captures only SELECT statements in Oracle 9i. It is extended in Oracle 10i to enable auditing of all types of DML.

6. Modify the granularity of the audit ( user sys)

--No duplicate name, delete audit:

Begin
Dbms_fga.drop_policy
(
Object_schema=> ' EDMS ',
Object_name=> ' T_audit_demo ',
Policy_name=> ' T_audit_demo_audit '
);
End;

--Re-add:

Begin
Dbms_fga.add_policy
(
Object_schema=> ' EDMS ',
Object_name=> ' T_audit_demo ',
Policy_name=> ' T_audit_demo_audit ',
Statement_types=> ' INSERT, UPDATE, DELETE, SELECT '
);
End;

Note: An audit of truncat table cannot be implemented.

7. Test Audit ( user EDMs, sys)

INSERT into T_audit_demo VALUES (4, ' Huang Zhihong ', ' HUANG ZHI HONG ');
DELETE from T_audit_demo WHERE CID < 4;
Sql> select Statement_type,sql_text from Dba_fga_audit_trail;
Statement_type Sql_text
-------------- --------------------
Select SELECT * FROM T_audit_demo
Insert INSERT INTO T_audit_demo VALUES (4, ' Huang Zhihong ', ' HUANG ZHI HONG ')
Delete delete from T_audit_demo WHERE CID < 4
Select SELECT * FROM T_audit_demo

At this point, we have implemented an audit of table T_audit_demo. Tables or views related to FGA:

SELECT * FROM fga$
SELECT * FROM fga_log$
SELECT * FROM fgacol$
SELECT * FROM Dba_fga_audit_trail
SELECT * FROM Dba_common_audit_trail

SELECT * FROM Dba_audit_policies
SELECT * FROM Dba_fga_audit_trail

Packages or processes related to FGA:

Dbms_fga.add_policy
Dbms_fga.drop_policy

Oracle Fine-grained (FGA) audits