Posts Tagged ‘Audit trail’

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 »