Basics of Oracle 12c Audits

By Chris Ruel, Michael Wessler

Security is vital because you don’t want the wrong people looking at the Oracle 12c data and because you need to protect the data from being altered or corrupted. Auditing is one thing you can use for security. Being able to restrict and monitor the users in the system helps you provide a safe and secure operating environment for you and your customers or clients.

How to enable and disable audits with unified audit policies in Oracle 12c

You can do all the setup you want, but nothing is audited (except for defaults) until you choose to do so. This benefits you because you can set up, create, and configure your application and objects before you have to manage an audit trail. You turn auditing on and off with the AUDIT or NOAUDIT command.

Because Oracle 12c does a fair amount of default auditing, consider turning off some before setting up your application. Then you can enable all the auditing you want right before your application goes to production. Make that decision based on your own business needs.

A unified audit policy is a named group of audit settings that audit a particular aspect of user behavior. The CREATE AUDIT POLICY statement creates the policies. The policy can be as simple as auditing the activities of a single user or an organized set of complex audit policies that use conditions to affect specific audits.

You can also have more than one audit policy enabled in the database. Policies can include both system-wide and object-specific audit options. Most of the auditing that you will do for general activities (including standard auditing) should use audit policies.

How to audit Oracle 12c system privileges

With security being so important, Oracle 12c ships with some auditing turned on automatically via the ORA_SECURECONFIG policy. Furthermore, it’s not always users’ actions you want to audit but what they are trying to do. An audit can be generated even when someone tries to do something he isn’t allowed to do.

Default audits

Default, preconfigured audits in 12c include the system privilege or statement audits, including commands and actions such as CONNECT, ALTER, DROP, CREATE, and so on.

For example, you might want to track who is creating tables in the database or how often tables are created. This simple policy turns on auditing for any CREATE TABLE statement, which generates an audit entry every time someone creates a table. Type the following:

CREATE AUDIT POLICY table_cre_policy

You see this:

Operation 229 succeeded.

To enable this policy for the user SHARDIN, type

<AUDIT POLICY table_cre_policy BY shardin;>

You see this:

Audit succeeded.

To disable the policy for SHARDIN, type this:

<NOAUDIT POLICY table_cre_policy BY shardin;>

How to audit successful and unsuccessful attempts separately

The default is to audit both successful and unsuccessful attempts. You can audit the statement if the user successfully executes the command; the audit doesn’t happen if the command fails. This approach can be useful two ways:

  • If you audit only successful commands, you don’t have to sift through a bunch of audit entries that show a user trying to get the correct syntax.

  • If you audit specifically the unsuccessful commands, you can catch users trying to do things that they aren’t supposed to. For example, suppose users are forbidden to drop tables that they don’t own. First, you can prevent inappropriate drops by not giving them the DROP ANY TABLE system privilege. Second, if they try to do it anyway, it generates an error and audit the unsuccessful attempt.

This policy audits the DROP ANY TABLE command. Type the following:

<CREATE AUDIT POLICY drop_any_table_policy

You see this:

Operation 229 succeeded.

To audit only unsuccessful attempts for the user JKOTAN, execute the following:

<AUDIT POLICY drop_any_table_policy BY jkotan WHENEVER NOT SUCCESSFUL;>

You see this:

Audit succeeded.

How to audit Oracle 12c objects

Consider object auditing if you want to audit statements, such as SELECT, INSERT, UPDATE, and DELETE. Object auditing can track

  • Actions against specific objects

  • Privileges on all or specific tables

Sifting through an audit trail of a database with thousands of audited objects can be daunting. It’s also likely that some objects simply don’t need auditing. If so, restrict your auditing to specific objects.

Furthermore, you can audit objects with these parameters:

  • When the operation is successful or when it fails

  • Just once per session or every time it is executed

If you audit an object just once per session, it is audited the first time the user issues the statement. Every time after that, it is ignored. This cuts down your audit trail but also keeps you from being 100 percent sure if said user is responsible for later operations against a specific object in a session.

For example, if a user deletes a row from EMPLOYEES, the statement is audited. If the user goes back later and deletes another row within the same session, it will not be audited. You know what they say though: Where there’s smoke, there’s fire!

To create an audit policy on SELECT against the HR.EMPLOYEES table, type the following:

<CREATE AUDIT POLICY hr_emp_select
ACTIONS SELECT ON hr.employees;>

You see this:

Operation 229 succeeded.

To then audit each statement for the user DCOLLINS, type

<AUDIT POLICY hr_emp_select BY dcollins;>

You see this:

Audit succeeded.