The Importance of Database Audit Trails and Security Assessments

Database audit trails are critical tools for discovery, investigation, and prosecution of cyber crimes ranging from credit card theft to malicious activity by ex-employees to corporate espionage.  And database security assessments are critical tools for preventing such activity. But an audit trail or a security assessment is only as good as the information that it captures. Many audit trails and security assessments take you only so far because of the way web applications are architected.  Fortunately, there is a way to fill that gap using Oracle Real Application Security

The Weak Link in Audit Trails and Security Assessments

When we perform database security assessments for clients, one of the key reports that we provide is a database user rights review. This is a report that shows which users in the organization have access to sensitive data in the database.  Unfortunately these reports are useless for many web applications because of the way authentication is done.

Similarly, when we do forensic analysis after a security incident, the audit trail should help us identify who the bad actors are, but for the same reasons, we can’t know for sure who the end user is that performed a suspicious action when a business user is not mapped directly to a database user.

The Real End User

A key requirement of data governance is an audit trail that identifies what specific users have access to sensitive data. Every major database vendor has audit capabilities that can show them what database user has accessed or modified a given piece of data. This auditing is very robust and if configured properly will comply with important security tenets, in particular , non-repudiation,  including the ability to prove the authenticity of the audit trail itself.

Unfortunately, with many web based applications, even though database auditing may be in place, weaker user access auditing is actually in practice. This is because many web applications are architected to use one (or a small number) of database user accounts known as application accounts. This is done for a variety of reasons including the difficulty of maintaining many users in the database,  the difficulty of reliably passing authentication through to the database in many environments, and the ability to share sessions for performance reasons.

The result of this practice is that the database itself does not know who the real end user is, and relies on the application sending in a user identifier, which is then looked up in a table in the database that is used for authentication (sometimes), authorization, and application-driven auditing.

The use of application accounts and application driven auditing is weaker than the use of real database accounts and database auditing for a variety of reasons.

  1. It relies on the application to do everything right. Every screen that touches data in any way has to make sure it correctly records the action and the proper user name.
  2. It relies on the database administrators to properly secure the user audit tables, user password tables, etc.
  3. It takes away the ability from standardized database security tools to generate reports describing what access an individual user has to a table or view. Custom code has to be written for each database and application to provide a User Rights Review report.

The Solution

One approach to filling this gap is with the Oracle Real Application Security feature in Oracle 12c.

Oracle’s Real Application Security (RAS) allows the use of individual database users even in a web application.  This results in the native database audit trail containing the actual end user information.

When working with Oracle Real Application Security,  fine grained database access to specific database objects is granted through an Access Control List mechanism to individual database accounts resulting in a database audit trail that is complete, robust, and non-reputable.

How it Works

At a high level, there are a set of constructs that enable the use of individual database accounts, rather than one main web application user. These constructs are building blocks and are used by higher-level constructs:

  • Traditional Oracle Roles and Privileges
  • RAS Principals
    • Direct Login Application User — Used in place of the traditional application user
    • Simple Application User — An account for each end user
    • Application Role — Manages a logical set of privileges that are defined as a set of ACL (access control lists).
  • ACE (Access Control Entry) — Controls the use of object privileges by a RAS principal
  • ACL (Access Control List) — Comprised of a set of Access Control Entries (ACE)
  • Security Class — A way of grouping privileges by database object
  • Data Security Policies
    • Data Realms — A way of restricting access to data rows based on a query
    • Column Constraints — A way of restricting access to specific columns of a table

These constructs build on each-other to provide very fine grained access control to data , this is an additional benefit to using RAS beyond the more detailed and accurate auditing capability and reporting capability.

Code Simplification

An additional benefit of this approach is that it relieves the application of two key responsibilities thus simplifying the design and reducing development effort

  1. The application does not have to manage authentication or authorization, as this is all handled at the database level.
  2. The application does not have to manage the auditing process, it can all be done through database configuration


Auditing and reporting user rights is critical to database security.  Many applications lose a reliable source of this information when web applications are used due to the use of application accounts.  This gap can compromise efforts to secure your data, particularly from internal threats.  If Oracle RAS is an option for you it can fill the gap

To learn more about how to secure your data visit us at