Overview to Auditing - Oracle

Overview: Auditing is recording and monitoring of database action of user /schema.

It is used for:

1.    Gather or monitor statistics about database activities. E.g. how many users are connected to the database, tables being updated or count of logical IO being performed?

2.    Investigating apprehensive activities. e.g. any unauthorized user s deleting or updating data from tables.

Types of Auditing

1.  Statement Auditing:

 It is based on type of the statement irrespective of schema objects.

a)  AUDIT TABLE monitors DDL statements (CREATE OR ALTER) regardless of the table name on which it is being performed.

b)  AUDIT SELECT TABLE audits all SELECT statements, irrespective of the table or view.

c)  Statement auditing can be broad or focused. It can audit activities of all database users or a set of database users.

2.    Privilege Auditing:  

a)    It is the selective auditing of the statements allowed using system privilege. E.g. privilege SELECT ANY TABLE allows users to select the data from the table. Privilege Auditing will audit those statements which are executed using SELECT ANY TABLE privilege like Select * from employees.

b)    In privilege auditing, owner privileges and schema object privileges checked before system privileges. If owner and object privileges are not enough to audit the action performed, action will not be performed.

c)    If similar statement and privilege audit options both are set, only one audit record will be generated. As an illustration, if the statement clause TABLE and system privilege CREATE TABLE, both are audit, one record will be inserted when table will be created.

d)    Privilege auditing is more focused than statement auditing as it audits only specific type of statements.

 For example, statement auditing option TABLE audits CREATE TABLE, DROP TABLE and ALTER TABLE, while privilege auditing option CREATE TABLE audits only CREATE TABLE statements (as CREATE TABLE requires CREATE TABLE privilege).

e)    Privilege auditing can also be broad or focused. It can audit activities of all database users or a set of database users.

3.     Schema Object Auditing:

a)    It is the selective auditing of specific DML statements /queries and DCL (GRANT and REVOKE) statements. This type of audits the operations performed by SELECT, DELETE, GRANT and REVOKE statements. Also, we can audit statements that refer to views, tables, functions, procedures and packages. Only standalone procedures can be audited, procedures which are inside a package cannot be audited.

b)    Statements that refer clusters, database link, indexes and synonyms cannot be audited directly. However, we can audit them indirectly by auditing the operations which affect the base table.

c)    Schema object auditing is always set for all users. This auditing cannot be set for specific users.

Schema Object Audit Options for Views and Procedures

Views, Procedures, Functions, and Packages refer underlying objects like TABLE in their definition. So, while auditing these objects, multiple records may be generated.

Illustration:

Consider the following statements.

AUDIT SELECT ON emp;

CREATE VIEW emp_dept AS

  SELECT emp_id, first_name, emp.dept_id

    FROM emp, dept

    WHERE emp.dept_id = dept.dept_id;

AUDIT SELECT ON emp_dept;

SELECT * FROM emp_dept;

Result:

Two audit records should be generated. One, when we query view and other one, when view queried underlying table emp (indirectly through emp_dept). All audit records belongs to the user who queried the emp_dept view.

Description:

The audit options for the objects (views, procedures, functions etc.) are determined when they first used and it is placed in the shared pool. These options remain set unless and until these objects flushed from shared pool. Any changes to these objects will not replicate to the objects which are in shared pool.

If auditing of SELECT statement is not set for emp table, this view will not generate any audit record for emp table.

4)   Fine Grained Auditing:

a)     This type of auditing allows the monitoring the data access based on content. Triggers also allows audit of INSERT, DELETE and UPDATE statement. Monitoring of SELECT statement will not work in all cases and it is expensive. This type of auditing provides an interface to audit SELECT statement as well.

b)    DBMS_FGA package administrate value based audit policies. Also, security administrator can create more policies on the target table. If any of the policy matches with the result of query, entry will be inserted into audit table.

Note: columns which are used while defining audit policy, must be used in the where clause of the query, only then the result will be audited.

Audit Records and the Audit Trail

1.    An audit record includes information about the user/schema manipulating the database objects. It also includes the date/time when the operation being done.

2.    Audit records can be stored in data dictionary tables which is called database Audit Trail or an operating system audit Trails.

3.    Database audit Trail: It is stored in SYS.AUD$ (SYS Schema) as data dictionary objects.

Audit trail contains different types of information, based on events audited and auditing options set.

Information which is included in each and every audited record:

                             I.        User Name

                           II.        Name of schema objects being accessed

                          III.        Operation performed or attempted

                          IV.        Session identifier

                            V.        Terminal Identifier.

                          VI.        Completion code of the operation

                        VII.        Date and Timestamp.

                       VIII.        System privileges used.

Action Code describes the operation being performed.  List of all action codes are stored in AUDIT_ACTIONS table which is data dictionary table.

Privileges used describe system privileges which are used to perform the operation. List of all these code with their description are stored in SYSTEM_PRIVILEGE_MAP table.

Completion Code describes the result of operations. Successful execution returns 0 while unsuccessful returns Oracle error Code.

4.    Operating System audit Trail: Operating system audit trail is encoded and not readable.

It can be decoded in data dictionary objects and error messages.

                                     1.            Irrespective of database auditing is enabled or disable, Oracle records some of the database action at the OS level.

                                     2.            At instance startup, audit record will be generated holding the details about the user who started the OS ,date and timestamp, user’s terminal identifier, status of database auditing(enabled/disabled). As, database audit trail is not available at instance startup, so this is recorded in the operating system audit trail.

                                     3.            At instance shut down, another audit record will be generated holding the details of the user shutting down the instance, date and time stamp and the user’s terminal identifier.

                                     4.            When connecting with the administrator privilege, audit record will be generated holding details about the user who has connected with the administrator privileges, date and time stamp.

                                     5.            If any operating system makes audit trail available to Oracle, then Oracle allows these records to be written in Operating system audit trail. When there is no audit trail available to Oracle, these records written to a file outside the database. The format of this file is similar to Oracle Trace File.

Mechanisms for Auditing

1.    When Are Audit Records Generated? 

·         The recording of audit information can be enabled or disabled.  With this functionality, any authorized user set audit options at any time but recording of audit information still lying with database administrator.

·         Audit record is generated during the execute phase of statement execution.

·         Generation and insertion of an audit trail is independent of user’s transaction. e.g.  If the transaction is rolled back, audit record will be there.

2.    When Audit Options Take Effect?

It depends on the type of auditing. Statement and privilege auditing remain in effect for particular user session. It will be the same for one session .if any changes requires, and then the database has to be restarted.

Auditing in different environment:

1.    Distributed database:  Instance audits only those statements which are executed by the users who are directly connected to the database. In the remote connection, user is connected through database link. So the auditing process will be same as it is for database link statements.

2.    Multitier environment:  In this environment, actions of auditing will be taken on behalf of the client. To use the auditing in this environment, use BY PROXY clause in the audit statement.

This clause provides three options:

a)    Audit all statements which are executed on behalf of specified users

b)    Audit all statements which are executed on behalf of any user

c)    Audit all statements which are executed on its own behalf.

Focus of Auditing

1.    Successful/unsuccessful executions of statements.

2.    BY SESSION and BY ACCESS auditing

3.    For all users or for specific users in the database.

1.    Successful and Unsuccessful Statement Executions Auditing

Oracle monitors the successful and unsuccessful execution of statements in selective, privilege and schema object auditing. Even though the statement do not complete successfully, we can monitor its actions. But for that statement, syntax of SQL should be proper. It fails due to lack of authorization or it refers to nonexistent schema object. Invalid statements cannot be audited.

For example, in statement privilege, auditing is set for all DDL statements. And if any CREATE TABLE statement fails due to insufficient space in Table space, then that statement cannot be audited.

1.    WHENEVER SUCCESFUL clause audit only successful executions of the audit statement.

2.    WHENEVER NOT SUCCESFUL clause audit only unsuccessful executions of the audit statement.

3.    When no clause is present, audit both types (successful/unsuccessful) of statements.

2.    BY SESSION and BY ACCESS Clauses of Audit Statement

BY SESSION

For any audit statement, this clause will insert one record in audit trail, for each user and schema objects during one session.

·         Example 1

SELECT TABLE statement auditing option is set BY SESSION.

User ‘A’ connects to the database and issues 10 Select statements against one table ‘ABC’ and disconnects.

User ‘B’ connects to the database and issues 5 Select statements against table ’XYZ’ and disconnects.

In this case, only two records will be there, one for each session.

·         Example 2

SELECT TABLE statement auditing option is set BY SESSION.

User ‘A’ connects to the database and issues 4 Select statements against one table ‘ABC’,  2 Select statements against one table ‘BCD’  and  5 Select statements against one table ’XYZ’ and disconnects.

In this case, three records will be there, one for each schema object.

BY ACCESS

For any audit statement, this clause will insert one audit record, when any auditable operation is executed within the cursor.

For each execution of auditable operations, BY ACCESS inserts one audit record into the audit trail. Cursor can be reused in the following events:

1.    Applications like Oracle Forms holding a cursor open.

2.    Frequent execution of cursors with changing value of bind variables.

3.    Statements with PL/SQL loops which uses the single cursor.

 Example

·         SELECT TABLE statement auditing option is set BY ACCESS.

·         User ‘A’ connects to the database and issues 10 Select statements against one table ‘ABC’ and disconnects.

·         User ‘B’ connects to the database and issues 5 Select statements against table ’XYZ’ and disconnects.

In this case, only 15 records will be there, one for each SELECT statement.

Important points:

Audit statement can be specified either BY SESSION or by access.

Statement and privilege audit options which audit DDL statement can set only BY ACCESS.

For all other options by SESSION is the default

3.    Audit By User

Statement and privilege audit options executed by any user or statement executed by list of users. If we select some specific users, we can minimize the audit record generated.

          Example

           To audit select statements by user ‘A’ and ‘B’ that query SELECT or DELETE from table, execute the following statement:

AUDIT SELECT TABLE, DELETE TABLE   BY A, B;

References:

www.oracle.com