Using Oracle Database native Auditing features

Preventing attacks to the database is the only approach to securing the database. Detecting potential attacks is also as important after all, the best security in the world is not going to stop every attacker. This is where the advantage of auditing comes to play.

Auditing allows us to monitor the environment and identify potential attacks.

This post will be based on Oracle 11g.

Basically an Oracle 11g database supports four levels of auditing which we will discuss in a moment.

Audit records can be stored in the following locations:

•          Database
•        Operating System

The database initialization parameter AUDIT_TRAIL is used to enable or disable auditing. The default setting for this parameter is DB. Let’s look at the various settings available.

Types of AUDIT_TRAIL settings

·         AUDIT_TRAIL=DB: This setting tells the database to record audit records directly into the database. IT is stored in the base table called SYS.AUD$. It however does not guarantee the integrity of the audit logs as the database administrator would have access to manipulate with the data at his will.

·         AUDIT_TRAIL=DB,EXTENDED: This setting configures the database to record audit records in the database together with bind variables (SQLBIND) and the SQL statement triggering the audit entry. This entry will be put in the SQLTEXT column. Data is still stored in the SYS.AUD$ base table.

·         AUDIT_TRAIL=OS: This setting configures the database to record audit records in operating system files. For windows, the logs are stored in the Windows Event Viewer Log file. On Unix operating system, audit records are written into files in the directory specified by the initialization parameter audit_file_dest.

·         AUDIT_TRAIL=XML,EXTENDED: This setting configures the database to record audit records in the database to OS files in XML format.

·         AUDIT_TRAIL=NONE: This setting disables auditing.

To see the current setting in the AUDIT_TRAIL parameter the following query can be used.

Show parameter AUDIT_TRAIL

As can be seen in the screen shot, the AUDIT_TRAIL is set to DB

Note that the settings are not dynamic. Thus changing the settings would require the entire database to be restarted. The ALTER SYSTEM command is used to make changes to the AUDIT_TRAIL parameter

As an example, the command to change the settings to OS can be seen below:

ALTER SYSTEM SET audit_trail=OS SCOPE=SPFILE;

This command can only be run with a user account with DBA privilege.

Types of Auditing

Oracle 11g database supports 4 levels of auditing:

·         Statement Auditing

·         Privilege Auditing

·         Object Auditing

·         Fine-grained access Auditing

Let us look at them in details

Statement Auditing

Statement auditing involves monitoring and recording the execution of specific types of SQL statements.

This type of auditing can be enabled using the AUDIT statement. Examples can be seen below,

·         To audit activities on all tables the command will be written as:

AUDIT table;

·         To audit activities done by a specific user (Say OLUTAYO) on all tables the command will be written as:

AUDIT table BY olutayo;

To monitor users that are probing the database, we can limit auditing to only these unsuccessful executions by using a WHENEVER clause.

AUDIT table BY olutayo WHENEVER NOT SUCCESSFUL;

There are many auditing options to choose from. Click here to see all the statement-auditing options available.

To view the statement audit that has been enabled on your database the following data dictionary view can be queried: DBA_STMT_AUDIT_OPTS as seen below:

Select * from DBA_STMT_AUDIT_OPTS;

In addition all activities performed by user connected using SYS or SYSDBA/SYSOPER privileges can be audited by setting the AUDIT_SYS_OPERATIONS initialization parameter to TRUE.

To disable the Statement Audit, the following command is used.

NOAUDIT table

NOAUDIT table BY Olutayo;

NOAUDIT table BY olutayo WHENEVER NOT SUCCESSFUL

Privilege Auditing

This type of auditing involves tracking and recording the execution of SQL statements that require a specific system privilege, such as DROP ANY TABLE or GRANT ANY PRIVILEGE.

It can be enabled by specifying the system privilege to be tracked or monitored. Let have a look at an example.

·         To audit SQL statements that require the system privilege DROP ANY TABLE, the following query should be used:

AUDIT drop any table;

·         To audit SQL statements made by user OLUTAYO that require the DROP ANY TABLE privilege, the following query should be used:

AUDIT drop any table BY olutayo;

·         To audit SQL statements made by user OLUTAYO that require the DROP ANY TABLE privilege but require one audit entry for the triggering session, the following query should be used:

AUDIT DELETE ANY TABLE BY olutayo BY SESSION;

This generates less audit records

·         To audit SQL statements made by user OLUTAYO that require the DROP ANY TABLE privilege but require an audit entry for each auditable action during the session, the following query should be used:

AUDIT DELETE ANY TABLE BY olutayo BY ACCESS;

This generates more audit records

How to view users that are already monitored with Privilege Auditing

·         To view this use the following query below:

SELECT privilege, user_name

FROM dba_priv_audit_opts;

                      

Disabling Privilege Auditing

·         System privilege auditing can be disabled using the following command:

NOAUDIT<system privilege>;

·         Let’s look at an example. Suppose I want to DISABLE the audit on anyone that issues the GRANT ANY PRIVILEGE system privilege command, I would write the query as this:

NOAUDIT GRANT ANY PRIVILEGE;

Object Auditing

This type of auditing is used to monitor/audit activities performed at the object level. These audit logs are stored in the DBA_OBJ_AUDIT_OPTS data dictionary view. Unlike the statement and privilege audits, DBA_OBJ_AUDIT_OPTS data dictionary view has columns for each object privilege that auditing can be enabled on, and in each of these columns, a code is reported that shows the auditing options.

Let us have a look at the following example:

This table can be explained as thus:

·         Auditing has been enabled on the PAROLL table

·         No auditing has been enabled for SELECT activity on the PAROLL table

·         Auditing has been enabled for INSERT activity on the PAYROLL table with one audit entry for each access.

·         Auditing has been enabled for DELETE activity on the PAYROLL table with one audit entry for each access. There is also one audit entry generated for each session when the access is not successful.

Let us have a quick understanding of what the narrations means:

Disabling Object Auditing

·         Object Auditing can be disabled using the following command:

NOAUDIT INSERT ON PAYROLL WHENEVER SUCCESSFUL

Fine-grained auditing 

Fine-grained auditing (FGA) extends the capability of capturing actual SQL statements that query or manipulate data. FGA also allows auditing to be more narrowly focused than standard or object auditing.

FGA audit option can be:

·         Focused by individual columns within a table or a view

·         Can even be conditional so that audits are captured only if certain administrative specifications are met. This administrator defined specification is called FGA POLICY.

The DBMS_FGA is a plsql package that is used to create an audit policy for the target table or view if any of rows returned from a query block matches the audited column and the specified audit condition then an audit event cause an audit record to be created and stored in the audit trail.

TERMS USED IN AUDIT POLICY

·         Policy_Name: Every FGA policy created must have a name.

·         Audit_Condition: The audit condition is a SQL predicate that defines when the audit event must fire.

·         Audit_Column: The audit column defines the data that is being audited.

·         Object_schema: The defines the owner of the object to be audited

·         Object_name: This defines the object to be audited

·         Status: The status indicates whether the FGA policy is enabled. It is either true or false

Now let’s have a working example of an FGA policy:

Scenario:

The management of a bank has asked you to track people who are viewing the salaries of Executives.

You are to design an FGA policy to monitor anyone who issues a SELECT statement on the SALARY column on any of the staffs in DEPARTMENT 60.

• Log on as the sysdba and type the following query:

begin dbms_fga.add_policy

(object_schema=>'hr',

object_name=>'employees',

policy_name=>'audit_emp_salary',

audit_condition=>'department_id=60',

audit_column=>'salary',

enable=>true,

statement_types=>'select');

end;

/

• Open another command line and log in as hr account
• Type the following query.

 select last_name,salary

from employees

where department_id=60;

FGA audit logs are mostly stored in the FGA_LOG$ view. This where we would go and fetch our audited records.

Now let’s test our FGA Policy.

Log as the sysdba and type the following:

SET LIN 800

COL oshst FORMAT A20

COL osuid FORMAT A20

 COL obj$schema FORMAT A15

COL OBJ$NAME FORMAT A15

COL ntimestamp# FORMAT A20

COL lsqltext FORMAT A30

COL policyname FORMAT A20

select oshst,osuid,obj$schema,obj$name,

ntimestamp#,lsqltext,policyname

from fga_log$;

And that’s it for understanding the native audit features Oracle database.

Hope you enjoyed the post and stay tuned for more.

Reference:

To read more on this topic click on this link Auditing database activity.