Oracle audit - Fine-Grained Audit

As you know in Oracle database has several audit options: SQL statement, privileges, schemas, objects, and network. Additionally we can use fine-grained auditing to monitor specific database activities, such as actions on a database table or times that activities occur. 

Today I will stay on fine-grained audit option. Firstly it was introduced in Oracle9i Database to record an audit trail when a user selects from a table, not just changes it.

Please note, FGA is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table.

  

If you prefer to write the fine-grained audit records to the SYS.FGA_LOG$ table, then set the audit_trail parameter for the DBMS_FGA.ADD_POLICY parameter to DB or DB+EXTENDED.

You can use fine-grained auditing to audit the following types of actions:

• Accessing a table between 9 p.m. and 6 a.m. or on Saturday and Sunday
• Using an IP address from outside the corporate network
• Selecting or updating a table column
• Modifying a value in a table column

Let`s use. First set audit_trail parameter and restart database to use spfile

SQL>  alter system set audit_trail=DB scope=spfile;

System altered.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1336960 bytes

Variable Size             310380928 bytes

Database Buffers          104857600 bytes

Redo Buffers                6094848 bytes

Database mounted.

Database opened.

SQL>

SQL> show parameters audit_trail

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_trail                             string      DB

SQL>

--create new table and insert some records.

SQL> conn ulfet/ulfet
Connected.

SQL> create table telebeler (
id number,
f_name varchar2(10),
l_name varchar2(15),
lesson_name char(20),
score number);

Table created.

SQL> insert into telebeler values(1, 'Azer', 'Ibrahimov', 'History', 5);
1 row created.

SQL> insert into telebeler values(2, 'Kerim', 'Veliyev', 'History', 2);
1 row created.

SQL> insert into telebeler values(3, 'Ulfet', 'Tanriverdiyev', 'History', 4);
1 row created.

SQL> insert into telebeler values(4, 'Mamed', 'Aliyev', 'History', 3);
1 row created.

SQL> insert into telebeler values(5, 'Omar', 'Tanriverdiyev', 'History', 5);
1 row created.

SQL> commit;

Commit complete.

Table is ready, now create audit policy.

The following policy audits any queries of score greater and equal than 4

--Connect as sys

SQL> conn sys/?????? as sysdba

Connected.

SQL> BEGIN

  DBMS_FGA.add_policy(
    object_schema   => 'ULFET',
    object_name     => 'TELEBELER',
    policy_name     => 'CHECK_SCORE_AUDIT',
    audit_condition => 'score >= 4',
    audit_column    => 'SCORE');

END;

/

  2    3    4    5    6    7    8    9 

PL/SQL procedure successfully completed.

SQL>

Now connect as ulfet and select some records. 

Querying telebelers which score less than 4 and greater than 4.

SQL> select * from telebeler where score <=3;

        ID            F_NAME        L_NAME                    LESSON_NAME     SCORE
         ---------- ------------           ---------------                 --------------------          ----------
         2             Kerim              Veliyev                       History                       2
         4             Mamed           Aliyev                         History                        3

SQL> select * from telebeler where score=5;

        ID            F_NAME        L_NAME                    LESSON_NAME    SCORE
         ---------- ------------           ---------------                 --------------------         ----------
         1             Azer                Ibrahimov                  History                       5
         5             Omar              Tanriverdiyev            History                       5

  

 SQL> select * from telebeler where score>=4;

        ID            F_NAME        L_NAME                    LESSON_NAME    SCORE
         ---------- ------------           ---------------                 --------------------         ----------
         1             Azer                Ibrahimov                  History                       5
         3             Ulfet                Tanriverdiyev            History                       4
         5             Omar              Tanriverdiyev            History                       5

SQL>.

Now connect as sysdba and check audit records.

--connect as sys
SQL> conn sys/pass as sysdba

SQL> select db_user,policy_name,sql_text,TIMESTAMP from dba_fga_audit_trail order by 4;

DB_USER   POLICY_NAME SQL_TEXT TIMESTAMP
---------------- ---------------------- --------------- -----------------------------------------------------------------
ULFET         CHECK_SCORE_AUDIT select * from telebeler where score=5 07-DEC-12
ULFET         CHECK_SCORE_AUDIT select * from telebeler where score>=4 07-DEC-12

To list of FGA policy_name use DBA_AUDIT_POLICIES

SQL> SELECT POLICY_NAME FROM DBA_AUDIT_POLICIES;

POLICY_NAME
-----------------------------
CHECK_SCORE_AUDIT

SQL>

In Oracle 9i fine-grained auditing was limited queries, but in Oracle 10g it has been extended to include DML statements. For using this option just use statement_types parameter within DBMS_FGA.ADD_POLICY

statement_types => 'SELECT,INSERT,UPDATE,DELETE');

How to use and what permission are needed to create a Fine-Grained Audit Policy?

To create a fine-grained audit policy, you must have EXECUTE privileges on the DBMS_FGA PL/SQL package. The package is owned by the SYS user.

Particular user how has FGA essential grant can enable/disable FGA using DBMS_FGA.DISABLE_POLICY as:

SQL> BEGIN

DBMS_FGA.DISABLE_POLICY(
  object_schema        => 'ULFET',
  object_name          => 'TELEBELER',
  policy_name          => 'CHECK_SCORE_AUDIT');

END;

/  2    3    4    5    6    7
    

PL/SQL procedure successfully completed.

SQL>

Enable

SQL> BEGIN

DBMS_FGA.ENABLE_POLICY(
  object_schema        => 'ULFET',
  object_name          => 'TELEBELER',
  policy_name          => 'CHECK_SCORE_AUDIT',
  enable               => TRUE);

END; 

/  2    3    4    5    6    7    8 

PL/SQL procedure successfully completed.

SQL>

To drop FGA policy use:

SQL> BEGIN

DBMS_FGA.DROP_POLICY(
  object_schema        => 'ULFET',
  object_name          => 'TELEBELER',
  policy_name          => 'CHECK_SCORE_AUDIT');

END;

/

PL/SQL procedure successfully completed.

In Oracle 11g has new audit feature, DBMS_AUDIT_MGMT package which provide you to move audit tables to different tablespace.

By default, the SYSTEM tablespace stores the database audit trail SYS.AUD$ and SYS.FGA_LOG$ tables.

To do that use the following package`s procedure:

BEGIN

 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

  AUDIT_TRAIL_LOCATION_VALUE  => 'AUD_AUX');

END;

In this example:

• AUDIT_TRAIL_TYPE: Refers to the database audit trail type. Enter one of the following values:
• DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.
• DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.
• DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables.
• AUDIT_TRAIL_LOCATION_VALUE: Specifies the destination tablespace. This example specifies a tablespace named AUDIT_TABLES.

Let`s check

SQL> conn /as sysdba

Connected.

SQL> SELECT table_name,  tablespace_name FROM dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$') ORDER BY 1;

TABLE_NAME                     TABLESPACE_NAME
------------------------------           ------------------------------
AUD$                                     SYSTEM
FGA_LOG$                           SYSTEM

SQL>

Create new tablespace for audit table store

SQL> create tablespace audit_tables datafile '/u01/app/oracle/oradata/testdb/audit_tables1.dbf' size 50m autoextend on;

Tablespace created.

SQL>

Next we move the fine-grained audit trail table.

SQL> BEGIN

  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_TABLES');

END;

/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL>

Be patient while processing, it can last long time depending table`s size.

--Check above select

SQL> SELECT table_name,  tablespace_name FROM dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$') ORDER BY 1;

TABLE_NAME                                 TABLESPACE_NAME
--------------------------------------             ------------------------------
AUD$                                                 SYSTEM
FGA_LOG$                                       AUDIT_TABLES

SQL> 

Source:

http://docs.oracle.com/cd/E11882_01/server.112/e25789/cmntopc.htm#CNCPT89153 

http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#DBSEG60681