Auditing Oracle Data access at the Oracle Source

 

All Oracle managers know that simple Oracle triggers and code extensions can be used to enforce security and privacy at the Oracle application layer (OracleAS).  The real problem is securing the Oracle data source and all intermediate repositories and providing the ability to understand the root cause of the violation.

 

A ?detective? monitoring approach is used by many successful companies because Oracle allows you to know what actually happened when a breech occurs.  Simple auditing based only on preventative measures will not provide this level of insight.  Just like a human detective, the detective monitoring approach observes all aspects of Oracle data  access and keeps complete logs of all Oracle database activity.  To be fully safe and compliant, you must keep a complete audit trail for the Oracle data source and have a complete who, where, what, and when record of access and updates.

 

There are many challenges involved with auditing at the Oracle database level.  If you want to understand ?how? a violation happens, you must audit all events of interest,.  These events include privileged access by Oracle personnel, the auditing of all changes to the Oracle data, auditing all viewing of confidential Oracle data, and recording all changes to the Oracle database infrastructure, both by DDL and changes to executable Oracle database procedures.  Let's take a closer look at each auditing requirement. 

Auditing Privilege/Permission and Logon Events

 

You must have a complete record of the users who have Oracle data access including: ?who? is attempting to get it, ?what? they have rights to do with the Oracle data, ?why? they are changing the Oracle data, and ?when? the Oracle data was viewed or changed. 

 

While many Oracle databases such as Oracle provide primitive logon triggers for determining logon events, they don't work with many modern ERP products (SAP, Oracle Applications, PeopleSoft) because they use pre-spawned connections to the Oracle database.  User authentication and access management is done by the application server and the individual users are not exposed to the Oracle database.

 

The ?who? aspect of Oracle data auditing can be confounded if you use a tool such as SAP or Oracle applications that pre-spawns anonymous connections to Oracle.  The application controls user access and authenticated users are directed into the Oracle database under the control of the application (Figure 8).

 

  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  

Figure 8 - External application authentication

 

In these types of architectures an end-user has no direct privilege against the Oracle data source and the permission to view and access Oracle data is granted via the application.  Because the application controls all Oracle database access, you don't have to be concerned about back-door access with non-application interfaces such as Crystal Reports or ODBC.

 

However, Oracle is critical to audit the activity of privileged users, including DBAs, who have direct access to the Oracle database and can access or modify the application's underlying Oracle data. 

Auditing DDL Events
 

Managing changes to the schema definition of your Oracle database is critical.  You must have a complete record of all changes to your Oracle database system infrastructure and understand the potential security risks associated with each change.  This includes knowing that a table has been dropped or permissions have been changed inappropriately. Many open source solutions such as SCCS are inadequate and many Oracle managers use third-party products designed specifically to track schema changes, such as Merant PVCS (Serena), Kintana and Oracle Software Configuration Manager. 

 

If the organization has a policy of placing everything in the version control system, the changes made are automatically recorded. But what happens when someone makes an emergency change without using the proper procedure? The setup fails. This is a classic case of a system where the integrity can be guaranteed only when everyone follows the rules and no one bypasses them.   

Auditing DML Events
 

All auditing solutions must track changes to any of the Oracle data items, right-down to the column level.  It's not enough to know that a particular financial record was changed; you must also know exactly what has changed in the Oracle data content.  This includes the access method (?how? Oracle was changed), the before and after values, and the exact time and user ID. 

Auditing SELECT Events
 

Many Federal regulations mandate that you keep a complete record of access to private and confidential information.  For large active Oracle databases, Oracle is not uncommon to have daily viewing logs that are larger than the whole Oracle database and you must be able to easily run reports against this huge volume of Oracle data.  For example, the new HIPAA regulations allow any medical patient to request to know who has accessed their Oracle data in the past, and this simple query might involve accessing trillions of bytes of audit information.   When you have multiple, simultaneous requests for these reports, an improperly designed audit system might become crippled under the weight of the Oracle data volume. 

Auditing Execution and Modification of Stored Procedures
 

Many Oracle database shops encapsulate their Oracle database access inside code snippets called 'stored procedures?.  When using stored procedures an end-user takes-on the privileges required to execute the procedure, but only for the duration of the execution of the procedure.  In Oracle databases such as Oracle, stored procedures are written in an interpreted procedural language called PL/SQL.  As every Oracle manager should know, any language that is parsed and executed line-by-line is subject to injection attacks.  Hence, special audit procedures must be employed for any Oracle database that uses stored procedures.

 

Hardly a week passes without a report of a company suffering major losses due to an information security breach.  Let's take a look at the types of exposures faced by companies and see how an enterprise auditing solution can prevent the threats.