How to Secure and Audit Your Oracle 12c Database

By Chris Ruel, Michael Wessler

Security is an especially important concern when dealing with anything relating to computers and the Internet. As an administrator of software applications like Oracle 12c, you are concerned with security because you want to protect your data.

Audits: Oracle 12c’s big brother

Just when users think it’s safe to do whatever they want in the database, along comes auditing. No, really . . . being able to audit what happens in the database is like having police on the streets. Auditing can

  • Protect you from people with prying eyes or malicious intentions.

  • Help you track down who’s responsible for certain actions in the database.

  • Help analyze access data.

You can choose from many auditing options:

  • Users: Auditing can be turned on for everything a user does, from logging in to what SQL statements he’s running.

  • Objects: Every action against an object can be audited.

  • System privileges: Specific SQL statements such as ALTER, DROP, CONNECT, and CREATE can be audited.

  • Combination: Most likely, you will choose a combination of users, objects, and system privileges to accomplish your auditing needs.

You will rarely audit everything in the database. Some overhead is involved. Auditing can cost you in terms of the following:

  • CPU: Audit operations execute inside the database with each SQL statement or connection you run. The more you audit, the more work there is in the background for Oracle to do.

  • Storage: Oracle audits generate an audit trail for you to look at later. Again, the more data being audited, the more information is generated.

  • Personnel: Viewing and analyzing the auditing information could be a job in and of itself if you have a very large database with lots of users and lots of auditing. Someone has to interpret the audit trail and determine how the data will be used. The audit trail itself has to be managed.

    How long are you going to keep the info? Where will it go for long-term storage? Who will clean it up when it is no longer needed?

Find out what your auditing requirements are. Sometimes companies are bound by corporate guidelines. Or, you may be under the gun for industry certifications such as Sarbanes-Oxley, which require a fair amount of auditing.

Although Oracle auditing can cover a lot of the bases, it may not meet all your requirements. Make sure you can equate each one of your auditing requirements with Oracle auditing capability. In most cases, the database will have you covered.

How to get ready to audit with Oracle 12c

Oracle 11g changed the amount of auditing turned on by default. In 12c, all the following database actions are automatically audited by default:

ADMINISTER KEY MANAGEMENT

ALTER ANY PROCEDURE

ALTER ANY SQL TRANSLATION PROFILE

ALTER ANY TABLE

ALTER DATABASE

ALTER DATABASE LINK

ALTER PROFILE

ALTER ROLE

ALTER SYSTEM

ALTER USER

AUDIT SYSTEM

CREATE ANY JOB

CREATE ANY LIBRARY

CREATE ANY PROCEDURE

CREATE ANY SQL TRANSLATION PROFILE

CREATE ANY TABLE

CREATE DATABASE LINK

CREATE DIRECTORY

CREATE EXTERNAL JOB

CREATE PROFILE

CREATE PUBLIC SYNONYM

CREATE ROLE

CREATE SQL TRANSLATION PROFILE

CREATE USER

DROP ANY PROCEDURE

DROP ANY SQL TRANSLATION PROFILE

DROP ANY TABLE

DROP DATABASE LINK

DROP DIRECTORY

DROP PROFILE

DROP PUBLIC SYNONYM

DROP ROLE

DROP USER

EXEMPT ACCESS POLICY

EXEMPT REDACTION POLICY

GRANT ANY OBJECT PRIVILEGE

GRANT ANY PRIVILEGE

GRANT ANY ROLE

LOGMINING

LOGOFF

LOGON

PURGE DBA_RECYCLEBIN

SET ROLE

TRANSLATE ANY SQL

Furthermore, in 11g the database parameter AUDIT_TRAIL was set to DB. This was a significant change over previous versions. Before, it was set to NONE, meaning auditing was not turned on. Turning it on required restarting the database; a tall order in a production system.

Having the default parameter set to DB was convenient in case you forgot when you created the database. However, in 12c, this parameter no longer has any effect for newly created databases. It applies only to databases that are migrated from 11g to 12c.

Oracle 12c also changes the way default auditing is done in the database. Prior to 12c, each user had to have his or her auditing enabled in a very granular fashion. In 12c, a new feature called Unified Auditing greatly simplifies setting up and managing auditing in the database.

It allows you to group not only users and roles but also features, parameters, and applications in such a way that configuring proper auditing can be done quickly and efficiently.

By default, in 12c the database is delivered with a basic policy that covers some of the main audits that most database administrators would be concerned about. These audits, however, are constrained to SYSTEM privileges primarily. If you think about it, Oracle doesn’t have the intrinsic knowledge of your application. Therefore, it is up to you to configure object level auditing if required in your environment.

The AUDIT_TRAIL parameter no longer applies in newly created 12c databases. If you migrated from 11g, all your audit entries go into the table SYS.AUD$. Keep an eye on this internal table. Depending on your level of auditing, it can grow very quickly. Consider creating a maintenance plan that has directives for either purging the table or moving the audit rows to more permanent long-term storage (depending on your organization).