Database Management: Auditing and reporting Oracle user activity

New federal legislation on security and privacy has made the auditing and logging of activity in an enterprise Oracle database imperative. This technique automates the tracking of user activity and generates reports necessary to meet regulatory mandates.

Changes in U.S. federal laws have mandated increased security for auditing Oracle user activity. HIPAA, the Sarbanes-Oxley Act, and the Gramm-Leach-Bliley Act have all produced serious constraints on Oracle professionals who are now required to produce detailed audit information for Oracle system users.

Starting with Oracle8i, Oracle introduced special triggers that are not associated with specific DML events (e.g., INSERT, UPDATE, and DELETE). These system-level triggers included database startup triggers, DDL triggers, and end-user login/logoff triggers.

While Oracle provided the functionality for these new triggers, it was not clear how they could be used in order to track system-wide usage. This article describes my work in creating end-user login/logoff procedures to facilitate tracking end-user activity. Please be advised that the initial implementation of system-level triggers for end-user tracking is quite new and, as such, is still a bit lacking in robust functionality.

While the user logon/logoff triggers will accurately tell you the time of the user logon and logoff, the code does not capture any information regarding the specific tasks that were performed during the user's session.

Also note that these user logon/logoff triggers are best used for applications that utilize time-stamped users, which means those users who are given a unique Oracle user ID when they access the application. Applications that do not utilize time-stamped Oracle user IDs (SAP, PeopleSoft) may not benefit greatly by using these logon/logoff triggers.

Now that we understand the basics, let's take a look at how we can design the user audit table to track user activity.

Designing a user audit table
The first step is to create an Oracle table that can store the information gathered by the end-user logon/logoff triggers. In order to properly design these triggers, let's begin by looking at the information that's available inside the system-level triggers. First, we'll gather the information provided at login:
  • User ID—the user ID that was used to perform the sign-on operation
  • Session ID—the Oracle control session ID for the user
  • Host—the host name of the computer
  • Logon date—an Oracle date data type corresponding to the user login time, accurate to 1/1000 of a second

Now we'll gather the information available just prior to user logoff. At user logoff time, the Oracle system-level trigger provides information about the current session and the activity of the user:
  • Last program—the name of the last program the user was executing at the time of system logoff
  • Last action—the last action performed by the user during the session
  • Last module—the name of the last module accessed by the user prior to logoff time
  • Logoff date—an Oracle date data type corresponding to the actual user logoff time, accurate to 1/1000 of a second

Now we know the information available at both logon and logoff, but how do we collect this information and make it accessible to management? Let’s take a look at the available options.

User table normalization
Since the user logon/logoff triggers are separate entities, we have several choices in the design of a table to support this information. We could design two separate tables: a user logon table and a user logoff table. If we did, however, we'd have the difficulty of joining these two tables together and correlating which logon corresponds to which logoff and so on. This can be a tricky process that presents a lot of opportunity for error. How did user 24 log off before logging on? And so on and so on.

Now let's consider the better option. In order to get around this table issue, we can create a database design whereby a single table is used to record both logon and logoff events (Listing A). This eliminates the need for table joins and data correlation. In addition, we'll add a field to compute the elapsed minutes for each user’s specific session. This precomputation done by the trigger saves time and makes for a much more informative report, as we'll see later.

Designing a logon trigger
Once the table is designed, the next step is to create a system-level logon trigger that fills in as much information as possible at the time of the logon event. Listing B illustrates the logon audit trigger that I created. As you can see, I populated this table with values that are available at logon time:
  • User—the Oracle user ID of the person establishing the Oracle session.
  • Session ID—uses Oracle's SYS context function to capture the Oracle session ID directly from the v$session table.
  • Host—uses Oracle’s SYS context function to capture the name of the host from which the Oracle session originated. Please note that capturing the host name is vital for systems using Oracle parallel server or real application clusters, because we can have many sessions connecting from many different instance hosts.
  • Logon date—captures the date of the actual work logon, accurate to 1/1000 of a second. Notice how we partitioned logon date into two separate fields. Having a separate field for logon day and logon time produces a reader-friendly report.

Now that the logon trigger is in place, we have the challenge of creating a logoff trigger to capture all of the information required to complete the elapsed time for the user session.

Designing the logoff trigger
To make a single table function for both logon and logoff events, it's first necessary to locate the logon row that is associated with the individual user session. As you might imagine, this is tricky because you may have many users who are signed on with identical user names. To get around this limitation, I used the Oracle session ID. As you know, Oracle assigns a unique session ID into the v$session table for each user logged on to Oracle. We can use this session ID as a primary key to update our user audit table with logoff information.

Now let’s look at the information that becomes available as a result of using our logoff trigger. We begin by updating the user log table to include the last action performed by the user. As you'll note in Listing C, updating the last action is accomplished by using the SYS context function to grab the action column from the v$session table.

Next, we update our audit table to show the last program that was accessed during the session. Again, we invoke the SYS context function to select the program column from the v$session table.

We update the last module that was accessed by the user session. This is accomplished by selecting the module column from the v$session table and then placing it into our user audit table.

The final and most important step of this procedure is to insert the logoff time and compute the elapsed time for the user session. As the code in Listing C shows, this is achieved by updating our user login table with logoff date data type and then computing the elapsed time. As I noted earlier, precomputing the elapsed time for each user session makes each record in the stats$user_log audit table very useful because it shows the entire duration of the session.

Let's examine a few sample reports that can be produced by the system. You can enhance these reports to fit specific needs. It's now obvious why the precomputing of elapsed minutes is such a valuable feature: It produces a more useful report.

User-activity reports
Using the user audit table to generate reports can provide a wealth of information that may prove to be critical to you as an Oracle administrator. Our first report is a summary of total session time, shown in Listing D.

We can start by creating a simple query against our user audit table that will show the day and date, the individual user ID, and the total number of minutes the user spent on the system. While primitive, this can give us an indication of the total amount of time each user spent on our system.

This information is especially useful when there are different user IDs going to different functional areas of the system. For example, if the Oracle user IDs can be correlated directly to screen functions, the Oracle administrator can get a very good idea of the amount of usage within each functional area of the Oracle applications. Now let’s examine yet another type of report.

User logon detail reports
We can also use the same table to show the number of users who are on our system at any given hour of the day. This information is especially useful for Oracle administrators who are in charge of tracking user activity.

By examining the user audit table for user logon times, we can get an accurate count of generated sessions at any given hour of the day. This information can be represented as shown in Listing E.

At this point, the information can be taken into a Microsoft Excel spreadsheet and expanded into a line chart, as shown in Figure A.

Figure A
Hourly sessions

As you can see, this produces a very clear graph showing user activity by the hour of the day. Once you get a large amount of user activity in your system, you can also summarize this information by the day of the week or the hour of the day. This provides a tremendous amount of information regarding the user signature for the system. By signature, we mean trend lines or spikes in user activity. For example, we might see high user activity every Wednesday afternoon at 1:00 P.M. With the user audit table, we can quickly identify these user signatures and adjust Oracle in order to accommodate these changes and end-user usage.

Related DDL, system errors, and user activity can easily be captured using the system-level triggers. However, it's clear that system-level triggers aren't as sophisticated as they might be, and Oracle indicates that efforts are underway to enhance system-level trigger functionality with the introduction of Oracle10g in 2004.

However, the judicious use of the system logon/logoff triggers can provide a very easy and reliable tracking mechanism for Oracle user activity. For the Oracle administrator who is committed to tracking user activity over long-term periods, the user audit table can offer a wealth of interesting user information, including user usage signatures, aggregated both by the hour of the day and the day of the week.

Additional information
For a complete guide to Oracle auditing and security, see Don Burleson's new book, Oracle Privacy Security Auditing, by Rampant TechPress.


Editor's Picks