Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Auditing and reporting Oracle user activity

Oracle Tips by Burleson Consulting


Feb 9, 2004

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.
 
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.
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.

 
 
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:

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.