Friday, December 7, 2012

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:


No comments:

Post a Comment

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...