Posts Tagged ‘Audit’

In my previous topic I have given an idea about basics of oracle database auditing. Before entering in this topic I assume that my previous topic “Basics of Database Auditing” has already been read.

Actions Audited by Default

Regardless of whether database auditing is enabled, Oracle Database always audits certain database-related operations and writes them to the operating system audit file. This fact is called mandatory auditing, and it includes the following operations:

  • Connections to the instance with administrator privileges (connecting to Oracle Database as SYSOPER or SYSDBA.)
  • Database startup
  • Database shutdown

Enabling and Disabling Standard Auditing

(A)Setting the AUDIT_TRAIL Initialization Parameter

ALTER SYSTEM SET AUDIT_TRAIL =value scope=spfile;

The AUDIT_TRAIL initialization parameter must be set to enable standard auditing. It may take any of the six values.

  1. DB: Enables database auditing and directs all audit records to the database audit trail (SYS.AUD$), except for records that are always written to the operating system audit trail.
  2. XML: All elements of the AuditRecord node except Sql_Text and Sql_Bind will be printed to the operating system XML audit file.
  3. DB,EXTENDED: Does all actions of AUDIT_TRAIL=DB and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. (These two columns are populated only when this parameter is specified.)
  4. XML,EXTENDED: Does all actions of AUDIT_TRAIL=XML and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. (These columns are populated only when this parameter is specified.)
  5. OS: Enables database auditing and directs all audit records to an operating system file
  6. NONE: Disables standard auditing (This value is the default.)

(B)Specifying a Directory for the Operating System Auditing Trail

The AUDIT_FILE_DEST initialization parameter specifies an operating system directory into which the audit trail is written when either AUDIT_TRAIL=OS or AUDIT_TRAIL=XML is specified.

Mandatory auditing information also goes into that directory, as do audit records for user SYS if the AUDIT_SYS_OPERATIONS initialization parameter is specified.

AUDIT_FILE_DEST can be changed with ALTER SYSTEM SET AUDIT_FILE_DEST = DEFERRED, meaning the new destination will be effective for all subsequent sessions.

If the AUDIT_FILE_DEST parameter is not specified, then the default location on Solaris is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump.

(C)Enabling Standard Auditing Options

To use the AUDIT statement to set statement and privilege options, you must have the AUDIT SYSTEM privilege.

To use it to set object audit options, you must own the object to be audited or have the AUDIT ANY privilege.

Audit statements that set statement and privilege audit options can include a BY clause to specify a list of users or application proxies to limit the scope of the statement and privilege audit options.

When setting auditing options, you can also specify the following conditions for auditing:


BY SESSION causes Oracle Database to write a single record for all SQL statements of the same type issued in the same session. BY ACCESS causes Oracle to write one record for each access.


WHENEVER SUCCESSFUL chooses auditing only for statements that succeed. WHENEVER NOT SUCCESSFUL chooses auditing only for statements that fail or result in errors.

Auditing Connections and Disconnections


Enabling Privilege Auditing


Enabling Object Auditing


(D)Turning Off Statement and Privilege Auditing

The following statement turns off all statement audit options:

The following statement turns off all privilege audit options:


Turning Off Object Auditing


Turning Off Network Auditing


Purging Audit Records from the Audit Trail

For example, to delete all audit records from the audit trail, enter the following statement:

Alternatively, to delete all audit records from the audit trail generated as a result of auditing the table emp, enter the following statement:


(E)Viewing Database Audit Trail Information DBA_STMT_AUDIT_OPTS


Describes current system auditing options across the system and by user.


Describes current system privileges being audited across the system and by user.


Describes auditing options on all objects. The USER view describes auditing options on all objects owned by the current user.


Lists all audit trail entries. The USER view shows audit trail entries relating to current user.


Contains audit trail records for all objects in the system. The USER view lists audit trail records for statements concerning objects that are accessible to the current user.


Lists all audit trail records concerning CONNECT and DISCONNECT. The USER view lists all audit trail records concerning connections and disconnections for the current user.


Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user.


Lists audit trail entries produced BY AUDIT NOT EXISTS.

Read Full Post »

What is Auditing?

Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include user name, application, time, and so on. Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered, including the contents within a specified object.

Why we need it?

  • Enable future accountability for current actions taken in a particular schema, table, or row, or affecting specific content.
  • Deter users (or others) from inappropriate actions based on that accountability.
  • Investigate suspicious activity.
  • Notify an auditor that an unauthorized user is manipulating or deleting data and that the user has more privileges than expected which can lead to reassessing user authorizations.
  • Monitor and gather data about specific database activities.
  • Detect problems with an authorization or access control implementation.

For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies do generate audit records, then you will know the other security controls are not properly implemented.

Oracle allows audit options to be focused or broad, enabling you to audit the following:

  • Successful statement executions, unsuccessful statement executions, or both.
  • Statement executions once in each user session or once every time the statement is executed.
  • Activities of all users or of a specific user

Type of Auditing

1.Statement Auditing.(DDL statement and DML statements)
2.Privilege Auditing(system privilege)
3.Schema Object Auditing
4.Fine-Grained Auditing(Monitor Data Acess based on Content)

Audit Records and Audit Trails

Audit records include information about the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail.

The two general types of auditing are standard auditing, which is based on privileges, schemas, objects, and statements, and fine-grained auditing. Standard audit records can be written either to DBA_AUDIT_TRAIL (the sys.aud$ table) or to the operating system. Fine-grained audit records are written to DBA_FGA_AUDIT_TRAIL (the sys.fga_log$ table) and the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.

When Are Audit Records Created?

SQL statements inside PL/SQL program units are individually audited, as necessary, when the program unit is executed.

The generation and insertion of an audit trail record is independent of a user transaction being committed. That is, even if a user transaction is rolled back, the audit trail record remains committed.

Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. Setting or changing statement or privilege audit options in a session does not take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.

In contrast, changes to schema object audit options become effective for current sessions immediately.

Operations by the SYS user and by users connected through SYSDBA or SYSOPER can be fully audited with the AUDIT_SYS_OPERATIONS initialization parameter. Every successful SQL statement from SYS is audited. This specialized form of auditing audits all actions performed by every user with the SYSDBA privilege and writes only to an operating system location. It is not dependent on the standard auditing parameter, AUDIT_TRAIL.

Read Full Post »

The parameter AUDIT_TRAIL enables or disables auditing as well as it specifies where the auditing information will be kept and in which format the audit information will be stored.

In order to set the parameter- if you start your database with spfile then use,
ALTER SYSTEM SET AUDIT_TRAIL={ none | os | db | db,extended | xml | xml,extended } scope=spfile;

And restart your database with modified spfile.

Now we see there may be 6 values that this parameter can take. In the next it is told what these 6 parameter indicates.

• none: The value none disables database auditing. So if use use AUDIT SELECT TABLE while this parameter is null then no auditing actually will do.

• os: Enables database auditing and directs all audit records to the operating system’s audit trail. The audit information will be populated in the OS file.

• db: Enables database auditing and directs all audit records to the database audit trail. The audit information will be populated in SYS.AUD$ table.

• db,extended: According with db auditing which populate information in SYS.AUD$ table. In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.

• xml: Enables database auditing and writes all audit records to XML format OS files.

• xml,extended: Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values and writes all audit records to XML format OS files.

Read Full Post »