Using automatic logon triggers to trace Oracle user activity
J. Lavender

Using Oracle system level triggers

Starting with Oracle8i, Oracle introduced special triggers that are not associated with DML events (e.g. INSERT, UPDATE, and DELETE).   These system level triggers included database startup triggers, DDL triggers and end-user log in / log off 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 our work in creating end-user log in / log off procedures to facilitate tracing 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 log on / log off triggers will accurately tell you the time of the user log on and user log off, unfortunately the code does not capture any information regarding the specific tasks that were performed during the user's session.

Also note that these user log on and log off triggers are best used for those types of applications that utilize time stamped users.  By time stamped users, we mean those users who are given a unique Oracle user ID when they access the application.  Applications that do not utilize the time stamped Oracle user IDs (SAP, PeopleSoft) may not benefit greatly by using these log on and log off triggers.

Now that we understand the basics, let's move on in and take 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 log on / log off triggers.  In order to properly design these triggers; we begin by taking a look at the information that is available to us inside the system level triggers. First we gather the information available at log in. 

• User ID -- this is the user ID that was used to perform the sign on operation.
   
• Session ID -- this is the Oracle control session ID for the user.
   
• Host -- this is the host name of the computer.
   
• Logon date -- this is an Oracle date data type corresponding to the user log in time, accurate to 1/1000 of a second

Now we gather the information available just prior to user log off. At user log off time, the Oracle system level trigger provides us with some information about the current session that the user was performing: 

• Last program -- this provides the name of last program that the user was executing at the time of system log off.
   
• Last action -- this provides the last action performed by the user during the session.
   
• Last module – this provides the name of the last module accessed by the user prior to log off time
   
• Log off date -- this is an Oracle date data type corresponding to the actual user log off time, accurate to 1/1000 of a second

Now we know the information available to us at log on and log off, 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 log on and user log off 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 log on table and a user log off table. However then we would have the difficulty of joining these two tables together and correlating which log on corresponds to which log off and so on. This can be a tricky process that presents a lot of opportunity for error. How did user 24 log off before they logged on... and so on… and so on?

Now we take a look at the better option. In order to get around this table issue, a database design was created whereby a single table is used to record both log on and log off events. This eliminates the need for table joins and the correlation of the data.  In addition, we added a field to compute the elapsed minutes for each user's specific session. This pre computation done by the trigger saves time and makes for a much more informative report, as we shall see later.

Figure 1 -- shows the table definition that we used.

Designing a log on trigger

Once the table is designed, the next issue was to create a system level log on trigger that would fill in as much information as possible at the time of the log on event.  Figure 2 above illustrates the log on audit trigger that we created.  As you can see, we populate this table with three values that are available at log on time: 

• User -- this is the Oracle user ID of the person establishing the Oracle session.
   

• Session ID -- this uses Oracle's SYS context function in order to capture the Oracle session ID directly from the v$session table.
   

• Host -- this 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.
   

• Log on date -- this captures the date of the actual work log on, accurate to 1/1000 of a second. Notice how we partitioned log on date into two separate fields. Having a separate field for log on day and log on time produces a reader friendly report. 

Figure 2 -- shows the log on trigger definition that we used

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

Designing the log off trigger

In order to make a single table function for both log on and log off events, it is first necessary to locate the log on 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.  In order to get around this limitation, the Oracle session ID was used.  As we know, Oracle assigns a unique session ID into the v$session table for each individual user logged on to Oracle. We can use this session ID as a primary key to update our user audit table with log off information.

Now let's take a look at the information that becomes available to us as a result of using our log off trigger.

We begin by updating the user log table to include the last action performed by the user.  As you'll note in figure 3, 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.

Next, 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 log off time, and compute the elapsed time for the user session.  As we can see in the code in figure 3, this is achieved by updating our user login table with log off date data type, and then computing the elapsed time.  As we noted before, pre computing the elapsed time for each user session makes each individual record in the stats$user_log audit table very useful because it shows the entire duration of the session.

Figure 3 -- shows the log off trigger definition that we used

We will take a look at a few sample reports that can be produced by the system. These reports can be enhanced to fit specific needs. It is now obvious why the pre computing of elapsed minutes is such a valuable feature. It produces a more useful report. 

User Activity Reports

Using our 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 below in figure 4.

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 that the user spent on the system.  While primitive, this can give us an indication of the total amount of time that was spent on our system by each individual user. 

This information is especially useful where 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, then the Oracle administrator can get a very good idea of the amount of usage within each functional area of their Oracle applications.

Figure 4 -- A sample user activity report

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 that 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 log on 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 figure 5 below. 

 Figure 5 -- shows number of users per hour

At this point the information can then be taken into an MS Excel spreadsheet, and expanded into impressive bar charts.  Figure 6 shows a nice example.

 Figure 6 -- shows hourly sessions

As we 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:00pm. Using this user audit table, we can quickly identify these user signatures, and adjust Oracle in order to accommodate these changes and end-user usage.

CREATE OR REPLACE TRIGGER "LOGON_AUDIT_TRIGGER" AFTER
LOGON ON DATABASE
DECLARE
sess number(10);
prog varchar2(70);
BEGIN
IF sys_context('USERENV','BG_JOB_ID') is null and user = 'MYUSERNAME' THEN
   sess := sys_context('USERENV','SESSIONID');
   SELECT program INTO prog FROM v$session WHERE audsid = sess
   and rownum<=1;
   INSERT INTO stats$user_log VALUES (
   user,sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   prog,
   sysdate,
   sys_context('USERENV','OS_USER'));
END IF;
END;

Conclusion

 Related DDL, system errors, and user activity can easily be captured using the system level triggers.   However, it is clear that system level triggers are not as sophisticated as they might be, and Oracle indicates that efforts are underway to enhance system level trigger functionality with the introduction of Oracle10i in 2003.

 However, the judicious use of the system log on and system log off 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 provide a wealth of interesting user information, including user usage signatures, aggregated both by the hour of the day and the day of the week.