Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 20, 2010

Oracle 11g Security - Guidelines for Auditing

By James Koopmann

Before starting to audit your Oracle database, use some of these guidelines to help you plan your auditing initiative.

Over the last two months, we’ve been taking a look at the Oracle 2-Day Security Guide. Up to this point, we’ve taken a look at the following topics:

Enabling default security settings

Securing the Data Dictionary

Those Pesky Predefined Accounts

The Power of PROFILES

Object privileges and synonym usage

Explicit GRANTs and ROLES in Oracle Database 11g

Securing Oracle Database Client Connections

Securing Client Connections: OS Authentication

Secure Sockets Layer and Oracle 11g Security

Getting started with standard auditing in Oracle Database 11g

Setting default auditing as recommended by Oracle

Creating a standard audit trail in Oracle Database 11g

If you’ve noticed, throughout the Oracle 2-Day Security Guide, Oracle has focused on first setting or taking the defaults for just about all of the security setup setting; not venturing too far from normal operations and configurations of databases. It is thus proper, as we begin to close the 2-Day Security Guide book to end with a section that takes a look at a few guidelines for auditing. The 2-Day Security Guide categorizes its guidelines for auditing in the following way:

  • Using default auditing of SQL statements and privileges
  • Managing audited Information
  • Auditing typical database activity
  • Auditing suspicious database activity

The guidelines set forth for using default auditing of SQL statements and privileges basically just points us to the fact that when we create a new database you can enable auditing of a select set of SQL statements and privileges. Of course Oracle recommends you enable default auditing for this so that you can enforce strong internal controls and if needed you can meet any regulatory requirements as defined in the Sarbanes-Oxley Act. We went through using the database creation assistant (DBCA) in the Setting default auditing as recommended by Oracle topic, where from the Security Settings window we selected Keep the enhanced 11g default security settings (recommended) and clicked Next. Within the 2-Day Security Guide, you could also take a further look by checking out the "Using Default Auditing for Security-Relevant SQL Statements and Privileges" section for more information about default auditing.

Using Default Auditing for Security-Relevant SQL Statements and Privileges

As for the guidelines for managing audited information, the 2-Day Security Guide, mentions that “auditing does not severely affect database performance” you should “limit the number of audited events as much as possible” because this will “minimize the performance impact on the execution of audited statements and the size of the audit trail”. Now while the guide does say this is for ease of analysis, which I will agree, it brings up two very important questions that beg for answers. The first being: how can they say auditing does not severely affect database performance but then dogmatically state that limiting audited events will minimize the performance impact? Clearly if you audit too much or too many events, a database will suffer some impact. Probably the more logical explanation of the statements would be that you, as a security/database administrator, should know what you’re auditing or else it will become out of control. This sort of leads into the second beckoning question of: how much auditing is too much and how much is too little? Additionally, is there a fine line that would limit you from auditing everything you need and the performance impact you will experience? For these types of questions, the 2-Day Security Guide asks that you follow these two guidelines when developing your auditing strategy:

1.  Know why you’re auditing – Clearly auditing for the sake of auditing, especially in a production system, is not a good reason to audit. If you want to learn about auditing then you should play on a separate sandbox. This “knowing” also translates into “knowing” what to audit. You may say you want to detect ill-use of database information but make sure you know what that looks like. You may say that a SELECT statement that returns “a lot” of data is suspicious but depending on your application and the person performing the SELECT, it just might be valid.

2.  When you do audit, make sure you understand it – Putting a blanket set of auditing rules for a database is probably not going to make anyone happy. Imagine capturing every single SELECT statement that your database performs. Here you have to understand your database, understand the objects, understand the data, and understand your users, to put in place a minimum number of audit rules to capture just what you need for vulnerability detection. This will limit the amount of auditing data you as a security / database administrator will have to comb through—making your life much easier.

When auditing typical database activity, and we should probably emphasis typical here, the 2-Day Security Guide directs us to three guidelines for auditing when the purpose is for gathering historical information about database activities. These include:

1.  Only audit those actions that are pertinent to database activity – You should audit only specific database activities, to limit the amount of audit information gathered and cluttering up meaningful information with useless information.

2.  Archive old audit records and purge the audit trail – Over time, and sometimes not very long, the audit trail records collected will fill up and bog down the active audit area. It is a good idea to save off pertinent collected data (to an archive area) and then purge the audit trail. To archive audit records you can just use the INSERT..INTO..SELECT..FROM SYS.AUD$ statement of from the SYS.FGA_LOG$ if using fine-grained auditing. To purge you can just use a standard DELETE statement on these same objects. Just remember, before archiving, you can always SELECT only those audit rows that are of interest to reduce the archive area as well.

3.  Auditing should be considered when meeting certain regulations – Auditing, just as regular database activity, is access to sensitive data. Make sure you're auditing practices adhere to the same regulatory policies.

Once you figure out what to audit, the Security Guide offers two guidelines that state:

1.  Begin auditing general information and then switch to specific information – Often times when auditing is performed, knowing exactly what you’re looking for, not much information is available—causing one to question if auditing is set up properly or giving the false impression that nothing is happening on the database. For these reasons, it is often best to broaden the scope and set up options that audit more generally at first but using the standard audit trail. After you analyze this initial auditing information then you can always turn to auditing specific actions with options like fine-grained auditing.

2.  Keep your audit trail safe – Remember that the audit trail contains sensitive information and it also contains a record of what has happened to prove or disprove your case against suspicious activity. Because of this, you should keep your audited information safe and away from those that might want to add to or delete from it.

Setting up auditing is not as easy as just flipping on a switch. There are planning, running, and analyzing processes that must be considered. Improper planning can easily make you feel overwhelmed and unable to properly detect miss-use of a database. The 2-Day Security Guide helps overcome some of these downfalls by giving us some basic guidelines to get started.

» See All Articles by Columnist James Koopmann

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM