Database Auditing

Welcome to this section of auditing. Now that users can access the database, there is need to keep an eye on their actions. Database auditing refers to keeping track and monitoring of actions performed by users as they are using the database. There are a lot of reasons why we should carry out auditing and among them include: it is used as a tool for strengthening of the database security, facilitates investigations, it is sometimes a legal requirement, it is one tool of ensuring data integrity, and so many other reasons.

In Oracle, auditing is enabled by the AUDIT keyword and to disabled by the NOAUDIT keyword. After enabling auditing, audit trails are stored either in the database or operating system (we shall use the database). We need to first configure the audt_trail parameter to determine where the trails will be stored. To make this change, we use the ALTER SYSTEM statement in the figure below. Then after, we restart the database to effect the change in the initialization parameter file (this is done by shutting down the database and after we start it up). After this, the database is set to store the audit trails. So, lets dive into this 🎬.

Types of Database Auditing

Object Auditing

This is the monitoring and recording use of SQL statements that require specific object privileges. You remember the object privileges 😊? If no, check out the user authorization section from here. Such privileges include SELECT, UPDATE, INSERT, DELETE, etc. For example, we need to audit the use of SELECT and UPDATE statement on the hr.departments table. To do so, we use this statement:

From the above figure, we first connect as an administrator and then we enable auditing on the hr.employees tables. So, whoever user will select or update any data in this table, the record will be captured in the audit trails.

We can also just audit only whenever the use of an object privilege is successful by use of WHENEVER SUCCESSFUL keyword or auditing can be done whenever the usage of a privilege is unsuccessful by use of WHENEVER NOT SUCCESSFUL. For example, we need to audit the INSERT statement on hr.regions table whenever successful and INSERT statement on hr.countries whenever unsuccessful.

Object auditing is enabled to all users or none at all i.e. once it is enabled, it applies to all users. For example, with the INSERT statement on hr.countries or hr.regions, whoever will use the privilege will be audited. When it is disabled, all users are disabled.

To find out the enabled privileges, we use the dba_obj_audit_opts view. From the code snippet below, it is seen that auditing has been enabled on countries, employees and regions table.

To disable the object auditing, we use the NOAUDIT statement. Let us disable the object auditing on hr.employees.

Privilege Auditing

This is the type of auditing that focuses on monitoring and recording of SQL statements that require the use of system privileges. Such privileges include CREATE USER, CREATE TABLE, CREATE TABLESPACE, ALTER USER, etc. Just like object auditing, privilege auditing is also enabled using the AUDIT keyword and disabled by the NOAUDIT keyword. It can also be enabled on only statements that are successfully executed or those that are unsuccessfully executed.

The special thing about privilege auditing is that it can be done on a specific individual. This is not like in the object auditing where auditing is enabled for all users or none.

To identify the enabled privilege auditing options, we use the dba_priv_audit_opts data dictionary view.

Statement Auditing

Statement auditing looks at monitoring and recording of specific SQL statements. Oracle provides an easy way of enforcing auditing through use of one word that contains a list of SQL statements and it is this one word that is used to audit. For example, to audit CREATE TABLE, DROP TABLE and TRUNCATE TABLE, we can just use TABLE. Therefore, to audit for these statements, we just say:

There are other statements that were coined by Oracle that contain a list of privileges for example, USER for CREATE USER, ALTER USER and DROP USER, VIEW for CREATE VIEW, ALTER VIEW and DROP VIEW, ROLE for CREATE ROLE, ALTER ROLE and DROP ROLE, etc. To checkout the enabled statement audit options, we use the dba_stmt_audit_opts view.

Just like privilege auditing, we can also audit just specific individuals rather than all users. We can as well audit only successful or unsuccessful execution on SQL statements. For example, We can enable VIEW statement for only Montaher user.

Viewing the Audit Trails

To view audit trails, we use the dba_audit_trail data dictionary view. Let us run some SQL statements which we enabled auditing on.

Let us use the dba_audit_trail view to see the audit trails. We are going to use the a few columns i.e. username, sql_text, and timestamp. This will display the name of the user account, sql_text that was used and the time which the user used the sql_text.

If you have reached this far, congratulations 🎉. Oracle must be proud of you! 😉. Now that users can access and use the database, their actions also monitored; we need to think of backing up user data and that is exactly what the next section is all about.

Summary

1. Auditing is the monitoring and recording of users’ actions as they are using the database.

2. Object auditing focuses on monitoring and recording of SQL statements that require object privileges.

3. Privilege auditing focuses on monitoring and recording of SQL statements that require system privileges.

4. Statement auditing focuses on monitoring and recording of specific SQL statements.

5. Auditing is enabled by AUDIT keyword and disabled by NOAUDIT keyword.

6. We the dba_audit_trail view to access the audit trail records.