How to Secure and Audit Your Oracle 12c Database
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 LINK
CREATE ANY JOB
CREATE ANY LIBRARY
CREATE ANY PROCEDURE
CREATE ANY SQL TRANSLATION PROFILE
CREATE ANY TABLE
CREATE DATABASE LINK
CREATE EXTERNAL JOB
CREATE PUBLIC SYNONYM
CREATE SQL TRANSLATION PROFILE
DROP ANY PROCEDURE
DROP ANY SQL TRANSLATION PROFILE
DROP ANY TABLE
DROP DATABASE LINK
DROP PUBLIC SYNONYM
EXEMPT ACCESS POLICY
EXEMPT REDACTION POLICY
GRANT ANY OBJECT PRIVILEGE
GRANT ANY PRIVILEGE
GRANT ANY 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).