Fine-Grained Auditing

As I discussed in “Oracle Database Auditing” post oracle auditing is a very powerful utility. Using this option you are able to identify users’ activities, object access and so on..

As we know there exist four types of auditing: statement, object, privilege and fine-grained auditing. I will discuss fine-grained auditing, because previous three options are clarified in “Oracle Database Auditing” post. FGA can be defined only for table and optionally on column.

Fine-grained auditing uses PL/SQL package DBMS_FGA, which has the following methods: creating , dropping, enabling and disabling policies. We will identify which policies are created in the database and also see the audit logs.

Creating FGA Policy

DBMS_FGA.ADD_POLICY method is used for creating FGA policy. Which has the following parameters:

[object_schema] -Username, which owns the object that should be audited.Default is NULL, means current schema.

object_name -Name of the object that should be audited.

policy_name -Unique name for the policy.

[audit_condition] -If the condition, indicated here, evaluates to true or null(both of them are default) audit entry will be created. Note that this condition cannot directly use the following functions: USERENV, USER, SYSDATE, UID. It cannot use sequences or subqueries. Also it can not reference LEVEL, PRIOR, or ROWNUM pseudocolumns.

[audit_column] -List of columns, delimited by comma, on which audit option will be identified. The default value is NULL, means that any column.

[handler_schema] -Username, which owns event handler procedure. The default value is NULL, means current schema.

[handler_module] -The procedure name, which handles the event. The default is NULL, means not to use event handler procedure.If the procedure is in package, then you should indicate the whole name, for example, DBMSOBJG.GET_TAB_SPACE.

[enable] -Values are true or false. Indicates if this policy should be enabled or disabled. The default is TRUE.

[statement_types]-Values are SELECT, INSERT, UPDATE, and DELETE(comma delimited list). Indicates which DML statement should be audited.The default is SELECT.

[audit_trail] -Value DBMS_FGA.DB_EXTENDED(default) indicates that  database should record sql text and bind variables also. Other available value is and DBMS_FGA.DB indicates that db should not save sql text and bind variables.

[audit_column_ops]– Value DBMS_FGA.ALL_COLUMNS indicates that all columns listed in AUDIT_COLUMN parameter must be referenced in order to create audit record. Other value is DBMS_FGA.ANY_COLUMNS(default) means that if any column will be referenced audit record will be created.

Note that parameters that are enclosed by ‘[‘ and ‘]’ are optional, others are mandatory.

For example:

–Assume we are connected as sys user.

BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD'
        ,audit_column=>'SALARY, COMMISSION_PCT'
        ,enable=>FALSE
        ,statement_types=>'SELECT,DELETE,INSERT');
END;

Enabling FGA Policy

If the policy is already enabled, enabling it once more will not give you an error. DBMS_FGA.ENABLE_POLICY is a method which enables it.

For example:

BEGIN
   DBMS_FGA.ENABLE_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD');
END;

Disabling FGA Policy

DBMS_FGA.DISABLE_POLICY is a method for disabling policy.

For example:

BEGIN
   DBMS_FGA.DISABLE_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD');
END;

Dropping FGA Policy

DBMS_FGA.DROP_POLICY is a method for dropping policy.

For example:

BEGIN
   DBMS_FGA.DROP_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD');
END;

Identifying FGA Policies in the Database

DBA_AUDIT_POLICIES it a view which shows all FGA policies enabled in your database.

–Query this

SELECT object_schema||'.'||object_name as Audited_Object
       ,policy_column
       ,policy_name
       ,enabled
       ,audit_trail
FROM dba_audit_policies;

–Result

AUDITED_OBJECT | POLICY_COLUMN | ENABLED | AUDIT_TRAIL
-------------------------------------------------------
HR.EMPLOYEES   | SALARY        | YES     |DB+EXTENDED

Viewing FGA logs

DBA_FGA_AUDIT_TRAIL is a view which shows audited logs, that have been written to the database.

–Query this

SELECT db_user
       ,timestamp
       ,userhost
       ,sql_text
FROM dba_fga_audit_trail
WHERE policy_name='EMPTABLE_AUD'

–Result

DB_USER|TIMESTAMP            |USERHOST  |SQL_TEXT
-------------------------------------------------------------
SCOTT  |2/16/2011 10:05:16 AM|ADA0\SCOTT|select * from employees

Oracle Database Auditing

Oracle auditing is a very powerful thing. It gives you the ability to track of users’ activities.
You can simply identify what they are doing to your DB:).

Audit records can be stored in Operating System files on in Database. Initialization parameter audit_trail identifies where audit logs should be saved.

–To save it in OS, open initSID.ora parameter file and add/change the following parameter:

*.audit_trail = OS

–To save it in Database:

*.audit_trail = DB

There exists additional option when you are saving logs in the database. If you set audit_trail parameter to db_extended, it will tell the database to record audit records in the database together with bind variables (SQLBIND) and also (SQLTEXT).

*.audit_trail = DB_EXTENDED

–To see if it is enabled, query the following:

select name,value
from v$parameter
where name='audit_trail' ;

–My result is:

NAME        VALUE
------------------
audit_trail DB

Note you should reboot the database for the change to take effect.

In my case logs will be recorded in SYS.AUD$ table. But if you set audit_trail=OS, then you should also indicate where to save log files. Initialization parameter audit_file_dest indicates it. Default is:

–In Windows

%ORACLE_BASE%\ADMIN\ORCL\ADUMP

–In Unix

$ORACLE_HOME/rdbms/audit

There are four levels of auditing: statement, privilege, object, and fine-grained access.

1. Statement Auditing

This auditing involves monitoring of execution of SQL statements.

For example:

audit create table;
audit drop table;
audit truncate table;

–Or simply

audit table;

You also have the ability to audit statements executed by some special user.

For example:

audit create table by mkupatadze;

Or you can audit statements executed by some special user whenever this SQL statement fails or successes.

For example:

audit create table by mkupatadze whenever not successful;

or

audit create table by mkupatadze whenever successful;

Default is both of them successful and not successful.

Other options which you can use in auditing are by access and by session.
By access – one audit record will be created for each executed statement.
By session -oracle will try to merge multiple audit entries into one record when the session and the action audited match. But it only works for SQL statements other than DDL.

If statement is DDL then oracle behaves as if you indicated by access option. So one audit record will be created for each executed DDL statement doesn’t matter which option you indicated.(It is very important to note)

For example:

audit create table by mkupatadze by access whenever successful;

So one audit record will be created for each executed create table statement by mkupatadze user whenever it is successful.

1.1 Identifying Enabled Statement Auditing Options

–Query the following view:

select user_name
       ,audit_option
       ,success
       ,failure
from dba_stmt_audit_opts

–My result

USER_NAME   | AUDIT_OPTION | SUCCESS    | FAILURE
--------------------------------------------------------
MKUPATADZE  | CREATE TABLE | BY ACCESS  | BY ACCESS

1.2 Disabling Statement Auditing

noaudit table;
noaudit create table by mkupatadze;

Note that whenever successful or whenever not successful options is not necessary.

2. Privilege Auditing

This audit option audits privileges, for example, select any table,create any table… and so on..

Audit Example:

audit create any table;
audit create any table by mkupatadze;
audit create any table by mkupatadze by access;
audit create any table by mkupatadze by access whenever successful;

In the first example, one audit record will be created for each executed create any table statement. The second one specifies the user…Other examples are self-explanatory.

2.1 Identifying Enabled Privilege Auditing Options

–Query this

select user_name
       ,privilege
       ,success
       ,failure
from dba_priv_audit_opts

–My result

USER_NAME |PRIVILEGE       |SUCCESS  |FAILURE
----------------------------------------------
NULL      |CREATE ANY TABLE|BY ACCESS|BY ACCESS
MKUPATADZE|CREATE ANY TABLE|BY ACCESS|BY ACCESS

2.2 Disabling Privilege Auditing

noaudit create any table;
noaudit create any table by mkupatadze;
noaudit create any table by mkupatadze whenever successful;

Note that if you have enabled auditing for some user,in my case mkupatadze, you must indicate by username clause to disable it. As you have seen in previous section, two different records were created in dba_priv_audit_opts for mkupatadze and for other users.

Also note that by access or by session clauses must not be specified, or it gives an error.

3. Object Auditing

audit select on hr.employees;
audit select on hr.employees by access;
audit select on hr.employees by session whenever successful;

3.1 Identifying Enabled Object Auditing Options

–Query this

select owner
        , object_name
        , object_type
        , del
        , sel
from dba_obj_audit_opts

–My result

OWNER     |OBJECT_NAME|OBJECT_TYPE|DEL|SEL
------------------------------------------
MKUPATADZE|EMPLOYEES  |TABLE      |-/-|S/A

Note that I have selected just DEL and SEL, there are many. DEL means delete object privilege, SEL means select object privilege.
The symbol “-” means that no audit option is enabled.
“A” means by access.
“S” means by session.
From “S/A” means that by session auditing is enables when it is successful and by access auditing is enabled when it is not successful.
So first place is for successful and second one for not successful auditing.

3.2 Disabling Object Auditing

noaudit select on hr.employees;
noaudit select on hr.employees whenever successful;

Note that by access or by session must not be specified.

See Audit Logs

The view dba_audit_trail is based on the SYS.AUD$ table. So if you query from this view, you will see the content of SYS.AUD$ table.

select username, timestamp, action_name
from dba_audit_trail

or

select * from sys.aud$

Purging Audit Logs

You can manually delete records from sys.aud$ table or create a job which periodically purges the table. SYS.AUD$ table is created in SYSTEM tablespace by default.

To manually purge audit records older than 60 days, execute the following as user SYS:

delete from sys.aud$ where timestamp# < sysdate-60;

Some aspects in this post is from Sybex.OCA.Oracle.10g.Administration.I.Study.Guide.1Z0-042 book.