Auditing in the Oracle database - Oracle DBA

Auditing in the Oracle database stores information about database activities. The activities to be audited are specified by the DBA. Once enabled, auditing records the activity in the AUD$ table, owned by SYS.

Auditing Storing information about activities in the database in the SYS.AUD$ table. Auditing is controlled by the DBA.

Auditing can be fine-tuned in a number of ways. It can be restricted to particular objects or to specific users or based on whether the action is successful or unsuccessful. In other words, you might not care if users who are granted rights to a table access the table, but you might want to know when users without rights to a table try to access that table.

The types of auditing can be divided into two broad categories: statement auditing and object auditing. The general syntax for AUDIT is as follows:

The statement_clause allows you to specify not only the SQL statement to audit but also, optionally, the username that will be running the SQL statement. The object_clause allows you to specify a particular object to audit.

The BY SESSION clause means that an audit record is written to SYS.AUD$ only once in the session that triggered the audit, regardless of how many times the action was performed. BY ACCESS will record all occurrences of the specified action.

The NOAUDIT command turns off auditing and has the same syntax as AUDIT, except that BY SESSION or BY ACCESS is not specified when using NOAUDIT.

Statement Auditing

Statement auditing allows the DBA to trigger audit records in SYS.AUD$ when a given SQL statement is executed, either for all users or a particular group of users.

Recently, Janice, the DBA, created a new user SCRAWFORD and granted the CREATE TABLE privilege to SCRAWFORD. Janice is concerned that the new user is having trouble creating tables, so she decides to turn on auditing to see how often the new user's CREATE TABLE statements are failing:

In the next few days, the user SCRAWFORD runs a variety of CREATE TABLE statements, such as the following:

The user's second attempt failed because the table already exists.

Janice could review the SYS.AUD$ table, but she knows that the data dictionary view called DBA_AUDIT_TRAIL formats the records from SYS.AUD$ into a more readable format. She checks that view:

Statement Auditing

1 row selected.

The OBJ_NAME column contains the name of the object affected by the statement, and the ACTION_NAME column contains the type of statement executed. Because Janice is auditing only unsuccessful uses of the CREATE TABLE statement, there is only one row inserted into SYS.AUD$, even though two CREATE TABLE statements were executed.

The following week, Janice turns off the CREATE TABLE auditing with the following command:

Rows in the SYS.AUD$ table (and as a result, the DBA_AUDIT_TRAIL view) remain there until they are removed by the DBA.

Object Auditing

Object auditing allows the DBA to monitor access to specific objects in the database, along with the operations performed on those objects. For example, the DBA may want to see how often SELECT statements occur on a particular table in a certain period of time versus how many UPDATE statements occur against that same table. As with statement auditing, object auditing can also be further refined to audit only successful or only unsuccessful statements against the object.

Janice, the DBA, wants to find out how often the EMPLOYEES table in the HR schema is being accessed by SELECT, INSERT, UPDATE, and DELETE statements and by whom. She decides that auditing the table for a few hours one day would give her the information that she needs. The AUDIT statement she runs looks like this:

Audit succeeded.

After a few hours, she reviews the data dictionary view DBA_AUDIT_TRAIL to see what kind of activity has been performed against the EMPLOYEES table:

Object Auditing

5 rows selected.

From this query, she sees that the activity so far has been very light, with four accesses by HR and one by OE, all in the morning. Janice turns off the EMPLOYEE table auditing using the NOAUDIT command:

Noaudit succeeded.

As with statement auditing, the records in SYS.AUD$ remain there until they are removed by the DBA.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle DBA Topics