Implementing Oracle Database Security

<back to oca>

Apply the principle of least privilege


Audit database activity

Oracle 10g supports four levels of auditing and affords you two locations for recording these activities. You tell the database where to record audit trail records by setting the initialization parameter AUDIT_TRIAL that by default is set to NONE. You can set it to “DB”, “OS”, “DB, EXTENDED”, “OS, EXTENDED”.

Base table where db audit trail is stored is SYS.AUD$ . Over this table there are DBA_AUDIT_TRAIL, USER_AUDIT_TRAIL dictionary views. The parameter AUDIT_FILE_DEST tells the database where register OS files for audit trail. This parameter defaults to $ORACLE_HOME/rdbms/audit, and on Windoz machines the audit trails are written to the Event Viewer log file.

The four level of auditing are

  • statement (DBA_STMT_AUDIT_OPTS) : enables you to audit SQL statements by the type of statement, not by the specific schema objects on which they operate. For DDL the audit is only “by access”
  • privilege (DBA_PRIV_AUDIT_OPTS): enables you to audit the use of powerful system privileges that enable corresponding actions, such as AUDIT CREATE TABLE. Privilege auditing is more focused than statement auditing, which audits only a particular type of action. For DDL the audit is only “by access”
  • object (DBA_OBJ_AUDIT_OPTS): enables you to audit specific statements on a particular schema object, such as “AUDIT SELECT ON employees”. Schema object auditing is very focused, auditing only a single specified type of statement (such as SELECT) on a specified schema object. Schema object auditing always applies to all users of the database.
  • fine-grained access (Enterprise Edition only): enables you to audit at the most granularr level, data access and actions based on content, using any Boolean measure, such as “value>100000”. Enables auditing based on access to or changes in a column.

Implement Fine-Grained Auditing

This is done via DBMS_FGA pl/sql package. Fine-grained audit records are written on the SYS.FGA_LOG$ table on wich there is the dictionary view DBA_FGA_AUDIT_TRAIL.

The DBA_COMMON_AUDIT_TRAIL view combines standard and fine-grained audit log records.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: