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
- 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
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. connect sys/manager; create table stats$user_log ( user_id varchar2(30), session_id number(8), host varchar2(30), last_program varchar2(48), last_action varchar2(32), last_module varchar2(32), logon_day date, logon_time varchar2(10), logoff_day date, logoff_time varchar2(10), elapsed_minutes number(8) ) ;
Above shows the table definition that we used.
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. create or replace trigger logon_audit_trigger AFTER LOGON ON DATABASE BEGIN insert into stats$user_log values( user, sys_context('USERENV','SESSIONID'), sys_context('USERENV','HOST'), null, null, null, sysdate, to_char(sysdate, 'hh24:mi:ss'), null, null, null ); END; / 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.
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.
create or replace trigger logoff_audit_trigger BEFORE LOGOFF ON DATABASE BEGIN -- *************************************************** -- Update the last action accessed -- *************************************************** update stats$user_log set last_action = (select action from v$session where sys_context('USERENV','SESSIONID') = audsid) where sys_context('USERENV','SESSIONID') = session_id; --*************************************************** -- Update the last program accessed -- *************************************************** update stats$user_log set last_program = (select program from v$session where sys_context('USERENV','SESSIONID') = audsid) where sys_context('USERENV','SESSIONID') = session_id; -- *************************************************** -- Update the last module accessed -- *************************************************** update stats$user_log set last_module = (select module from v$session where sys_context('USERENV','SESSIONID') = audsid) where sys_context('USERENV','SESSIONID') = session_id; -- *************************************************** -- Update the logoff day -- *************************************************** update stats$user_log set logoff_day = sysdate where sys_context('USERENV','SESSIONID') = session_id; -- *************************************************** -- Update the logoff time -- *************************************************** update stats$user_log set logoff_time = to_char(sysdate, 'hh24:mi:ss') where sys_context('USERENV','SESSIONID') = session_id; -- *************************************************** -- Compute the elapsed minutes -- *************************************************** update stats$user_log set elapsed_minutes = round((logoff_day - logon_day)*1440) where sys_context('USERENV','SESSIONID') = session_id; END; / 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. Total Day User Minutes ---------- ---------- ------- 02-03-06 APPLSYSPUB 0 APPS 466 OPS$ORACLE 7 PERFSTAT 11 02-03-07 APPLSYSPUB 5 APPS 1,913 CUSJAN 1 JANEDI 5 OPS$ORACLE 6 PERFSTAT 134 SYS 58 02-03-08 APPLSYSPUB 1 APPS 5,866 OPS$ORACLE 15 PERFSTAT 44 SYS 6 02-03-09 APPS 0 OPS$ORACLE 0 PERFSTAT 29 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 below. Day HO NUMBER_OF_LOGINS ---------- -- ---------------- 02-03-06 01 2 02 3 03 1 04 3 05 6 06 9 07 14 08 19 09 21 10 22 11 26 12 28 13 45 14 38 15 26 16 26 17 25 18 26 19 26 20 26 21 49 22 26 23 24
At this point, the information can be taken into a Microsoft Excel spreadsheet and expanded into a line chart, as shown below.
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.
Tracking
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.
Here is a script to track the activity of a specific user:
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;
Additional information
For a complete guide to Oracle auditing and security, see Don Burleson's and Arup Nanda's new book, Oracle Privacy Security Auditing, by Rampant TechPress. Reader comments
I implemented your logon/logoff triggers and found that our DBMS_JOB and DBMS_SCHEDULER jobs began to fail. I found the cause in MOSC article Note:205477.1. (Select from v$session returns > 1 row causing the logon trigger to fail). I added the where rownum <= 1, and it fixed the problem.
Steven E. Whaley
Sr. Database Administrator
This is also an update of the comments of Mr. Steven E. Whaley. In some cases we loose information with the rownum <= 1 trick to prevent the TOO_MANY_ROWS exception. Here my trigger body (with all your information) plus our fix running in our pressure situations with all cases including DBMS_JOB or DBMS_SCHEDULE processes. < update stats$user_log set last_action = SYS_CONTEXT('USERENV','action'), last_module = SYS_CONTEXT('USERENV','module'), logoff_day = sysdate, logoff_time = to_char(sysdate, 'hh24:mi:ss'), elapsed_minutes = round((logoff_day - logon_day)*1440, 2), last_program = ( select program from v$session where sys_context('USERENV','SESSIONID') = audsid and status = 'ACTIVE' and type <> 'BACKGROUND' ) where sys_context('USERENV','SESSIONID') = session_id; > Best greetings, Thomas Pfaffenzeller System architect
Warning!
Auditing user activity is a resource-consuming task and auditing can place a great amount of stress on your Oracle server. Make sure to test all implementations of auditing before rolling them into production. For more details on Oracle auditing see my notes:
Oracle auditing books & services:
