Fine-grained auditing with DBMS_FGA

Experiment: Using DBMS_FGA to achieve fine-grained auditing

1. Create a test table
[email protected]> CREATE TABLE audit_test (x number);

Table created.

2. Create an Audit policy
[email protected]> Conn/as SYSDBA
Connected.
[email protected]> Begin
Dbms_fga. Add_policy (
Object_schema = ' TEST_USR1 ',
--The schema to which the object to audit belongs
object_name = ' Audit_test ',
--The name of the object to audit
Policy_name = ' Mypolicy1 ',
--The name of the audit policy created
Audit_condition = ' x < 100 ',
--Audit conditions (multiple columns can be audited at the same time)
Audit_column = ' x ',
--Columns to audit (can also be multiple columns)
Handler_schema = NULL,
Handler_module = NULL,
--If certain operations trigger this audit policy, you can specify the database
--subsequent treatment.
Enable = TRUE,
Statement_types = ' INSERT, UPDATE ',
--type of operation to audit (Insert,update,delete,select)
Audit_trail = Dbms_fga. DB + DBMS_FGA. EXTENDED,
--Specifies where audit records are stored
Audit_column_opts = Dbms_fga. Any_columns);
--whether this audit action is performed for all columns.
End
/

PL/SQL procedure successfully completed.

[email protected]> Col object_schema A10
[email protected]> Col object_schema for A10
[email protected]> col object_name for A10
[email protected]> Co policy_name for A10
[email protected]> Col policy_name for A10
[email protected]> 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, perform the action that triggered the audit
[email protected]> Conn test_usr1/test;
Connected.
[email protected]> INSERT INTO audit_test values (2);

1 row created.

[email protected]> INSERT INTO audit_test values (101);

1 row created.

[email protected]> commit;

Commit complete.

4. View the generated audit records
[email protected]> select COUNT (*) from sys.fga_log$;

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

[email protected]> 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)

[email protected]> 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)

[email protected]> select Object_schema,object_name,policy_name,sql_text from V$xml_audit_trail;

No rows selected
--If the audit policy was created earlier, the audit_trail was specified as
--xml, the SQL is executed to query the generated audit results.

Fine-grained auditing with DBMS_FGA