Oracle Database Security: Database Auditing

In this article, I will continue with Oracle Database Security and I will present some important facts about standard database auditing, audit triggers, and audit policies in Oracle. Database auditing has two components: monitoring and persistent registration of established database activity sets and events. The purposes of database auditing are non-repudiation, investigation of suspicious activities, detection of problems generated by configurations regarding authorization (resources access), compliance with actual legislation and control.

Standard Auditing

What activities do we audit? Database starting and stopping as well as the connections made by the database administrator are implicitly audited by Oracle and data is automatically stored in the operating system. The table below shows other activities that can be monitored:

Where do we keep the audited activities?

  • in the database, using database audit trail, where we have two possibilities:
    • audit_trail =DB
      which can be done with the following code:
    • audit_trail =DB,EXTENDED
      using the following code:

    The difference between DB and DB,EXTENDED is that the second populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.

  • external, using operating system audit trail, with the following possibilities:
    • audit_trail = OS
      and the code used is:

    • audit_trail = XML and the AUDIT_FILE_DEST = file path (implicitly is $ORACLE_BASE/admin/$ORACLE_SID/adump)
      with the code:

In order to find the current configuration of the stored audited activities, one can run the following query, written with lowercase:

More useful commands:

Audit starting for activity XAUDIT X
Audit stopping for activity XNOAUDIT X
Audit stopping for all SQL commands that are not set to a specific objectNOAUDIT ALL
Audit stopping for privilegeNOAUDIT ALL PRIVILEDGES
Audit stopping for all SQL commands that are set to a specific objectNOAUDIT ALL ON DEFAULT

Now let’s have some examples of database auditing.

Here is an example of a standard audit with audited information stored in the database:

The audited information consists of the SELECT statements executed on the database tables.

In SQL Developer, execute the following script:

Then, the database must be restarted. In order to do that, in the SQLPlus terminal, connect with username sys as sysdba / password and run the following statements:

Note that the above sizes differ from one system to another.

To verify if the audit trail is set correctly, run the following query:

or:

When you want to stop the audit, execute:

To view what statements were recorded by the audit, you can use:

or

Another example is for standard audit with audited data stored in an XML file, in the standard path.

Again, restart the database: connect to the SQLPlus terminal with username sys as sysdba / password and run SHUTDOWN IMMEDIATE and STARTUP commands.

Then, each time a select, insert, update and delete query fails on employees table, it should be recorded in the XML file.

When we want to stop the audit, we run the following commands in the database development environment:

And restore the default audit trail:

Below is an example of a part of an XML audit file:

How do we delete the audited information?

The volume of the audited data can become very big due to the number of the audited activities and their frequency. This is why it is recommended to periodically archive the audited data and delete it from the production system.

If the audited data are stored in the database (database audit trail), then we can use delete statements (but only after we have archived the data!):

You can choose to delete the audited information for a specific database object, for example, a table called products:

Audit Triggers

A trigger is a PL/SQL block or the CALL statement of a PL/SQL procedure that is automatically executed each time an event occurs. There are two types of triggers: at the database level (database statements) and at the application level (for example pushing a button on an Oracle Form). The triggers used for auditing are the database level triggers. They classify into the following categories:

  • DML triggers – where a DML statement is triggered on a table. Those triggers can be executed once at the command level regardless of the number of records (triggers at the statement level) or they can be executed FOR EVERY ROW (triggers at the record level). Types of record level triggers: BEFORE STATEMENT, AFTER STATEMENT, BEFORE EACH ROW, AFTER EACH ROW;
  • INSTEAD OF triggers – where a DML statement is triggered on a view;
  • SYSTEM triggers – triggered by events such as starting/stopping the database, DDL statements, user login/logout. Types of system triggers: AFTER EVENT, BEFORE EVENT.

Querying the SYS.TRIGGER$ table or the ALL_TRIGGERS view offers information about all the database level triggers. For example, the distinct trigger type from the database can be found as follows:

The DBA_TRIGGERS view offers information about the triggers automatically created by the Oracle products at installation. If we want to find information about the SYSTEM triggers (‘BEFORE EVENT’ and ‘AFTER EVENT’), we can run the following statement:

Also, at installation, DML triggers are automatically created on the HR user schema:

For auditing, we can create customized triggers to record the desired information, but one should create a special table to store the audited information.

It is important to make sure that the developed triggers don’t influence the normal database activity. The purpose of auditing is to passively monitor the normal, day-to-day database activity and to store it for later analyze. Consequently, it is not recommended to create INSTEAD OF triggers to return the results from the target tables to the audit table.

DML triggers at the statement level can coexist with DML triggers at the record level. The order of call is:

  • trigger BEFORE statement
  • for each record affected
    • trigger BEFORE record
    • current DML action
    • trigger AFTER record
  • trigger AFTER statement

User-defined triggers will be executed only if, according to Oracle, the statement is correct and can occur. For a wrong DML statement or one that violates a constraint, an error will be returned and the trigger will not be executed. Therefore, for auditing, it is recommended to use especially the LMD triggers at the statement level.

Example of audit trigger:

Let’s assume we want to create a trigger to record in an audit table (called TAB_AUDIT_EMP) information about the DML statements that establish salaries over 20000 (currency is not important here) for the company’s employees. We want to store in TAB_AUDIT_EMP the sequence number of the query, the username, the session, the host and the date.

This can be done with the following:

Assume we make a salary modification for the employees in a specific department:

And then we verify the monitoring:

Audit policies

The third auditing method refers to audit policies. The definition of an audit policy contains the following:

  • specification of the object (schema, object name, columns) subjected to monitoring
  • specification of the actions audited for an object (SELECT, INSERT, UPDATE, DELETE): implicitly it’s SELECT
  • specification of the conditions that must be fulfilled in order to record the audited information, it is done in the WHEN clause of the trigger and is optional
  • an event handler that additionally handles the event, which it is optional.

An audit policy can fi active (ENABLED status) or inactive (DISABLED status). The list of the audit policies can be obtained by interrogating the ALL_AUDIT_POLICIES view:

For audit policy administration we have the DBMS_FGA package. In order to use this package, it is necessary to grant privilege to the users that will write PL/SQL code:

Example of audit policy:

We want to create an audit policy for recording the DML statements that modify the managers (MANAGER_ID) in the DEPARTMENTS table. We can choose to create a handler for the policy, called proc_audit_alert, that informs us of a modification regarding the manager:

And the policy can be:

Note that object_schema, object_name, policy_name, audit_column, statement_types and handler_module must be adapted to the policy you want to write.

Then, we execute the procedure:

We can verify if the policy is enabled:

Then, verify if the procedure and the policy work correctly, by executing an update:

In conclusion, auditing is necessary for each database and the methods provided above help you in finding a suspicious activity that might affect your database security. For more details about Oracle database auditing, see the bibliography below.

Bibliography:

Andreea-Elena Panait

Andreea-Elena Panait

Software Developer Enthusiast graduated with a Computer Science Bachelor's degree. Current interests in Database Administration, Business Intelligence, Big Data and Machine Learning Technologies.
Andreea-Elena Panait

Latest posts by Andreea-Elena Panait (see all)

835 views