Extending Oracle with system level event auditing triggers

Home
E-mail Us
Oracle Articles
New 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 support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Extending Oracle for system event auditing

Oracle Tips by Burleson Consulting

Some of the most exciting new features within Oracle are the new system-level triggers that were introduced in Oracle8i. What is truly exciting is that we can combine the system-level triggers with STATSPACK extension tables, thereby building a mechanism that will give complete auditing information on user logon and logoff, DDL, and server errors. For a complete description of the v$ views, get the "free 11g poster" sponsored by Quest Software.

Just as an Oracle trigger fires on a specific DM event, system-level triggers are fired at specific system events such as logon, logoff, database startup, DDL execution, and servererror triggers:

1. Database startup triggers - Database startup triggers can be used to invoke the dbms_shared_pool.keep procedure to pin all frequently-referenced PL/SQL packages into RAM.

2. logon triggers - The logon triggers can be used to store login information directly inside Oracle tables, thereby providing a complete audit of all those times when users entered your system.

3. logoff triggers -- Oracle logoff triggers can automatically capture elapsed session duration times for all users who have accessed your Oracle database.

4. Servererror triggers -- With the servererror trigger, the Oracle administrator can automatically log all serious PL/SQL errors into an Oracle STATSPACK extension table. This table, in turn, has an insert trigger that e-mails the server error directly to the Oracle professional for immediate notification.

5. DDL triggers -- Using the DDL trigger, the Oracle administrator can automatically track all changes to the database including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA.

Now let's take a close look at how these triggers work with Oracle tables.

Database Startup Event Triggers

Package pinning has become a very important part of Oracle tuning, and with the introduction of system-level triggers in Oracle8i, we now have an easy tool to ensure that frequently-executed PL/SQL remains cached inside the shared pool.

Just like using the KEEP pool with the data buffer caches, pinning packages ensures that the specified package always remains in the Most Recently Used (MRU) end of the data buffer. This prevents the PL/SQL from being paged-out, and then re-parsed on reload. The Oracle DBA controls the size of this RAM region by setting the shared_pool_size parameter to a value large enough to hold all of the PL/SQL.

Pinning of packages involves two areas:

1 - Pinning frequently executed packages -- Oracle performance can be greatly enhanced by pinning frequently executed packages inside the SGA.

2 - Pinning the standard Oracle packages -- These are shown in the code listing below, and should always be opined to prevent re-parsing by the Oracle SGA.

You can interrogate the v$db_object_cache view to see the most frequently used packages, and automatically pin them at database startup time (with an ON DATABASE STARTUP trigger) using dbms_shared_pool.keep.

create or replace trigger
   pin_packs
after startup on database
   begin
        -- Application-specific packages
        dbms_shared_pool.keep('MAIN_PACK');
        dbms_shared_pool.keep('OTHER_PACK');
        -- Oracle-supplied software packages
        dbms_shared_pool.keep('DBMS_ALERT');
        dbms_shared_pool.keep('DBMS_DDL');
        dbms_shared_pool.keep('DBMS_DESCRIBE');
        dbms_shared_pool.keep('DBMS_LOCK');
        dbms_shared_pool.keep('DBMS_OUTPUT');
        dbms_shared_pool.keep('DBMS_PIPE');
        dbms_shared_pool.keep('DBMS_SESSION');
        dbms_shared_pool.keep('DBMS_STANDARD');
        dbms_shared_pool.keep('DBMS_UTILITY');
        dbms_shared_pool.keep('STANDARD'); 
   end;

Extending STATSPACK Tables for System Events

Because the Oracle system-level triggers can interface with Oracle tables, the logical approach is to create Oracle tables to hold the data. And the logical place for this data is inside the STATSPACK tablespace, owned by the PERFSTAT user. Extending Oracle STATSPACK adds additional information to STATSPACK, and enhances existing metrics such as cumulative logins in the stats$sysstat table.

DDL table -- Using the code snippet shown below, we create an Oracle table to capture all of the salient metrics required to do effective change management within an Oracle environment. Note this table contains the date that the DDL was made, the ID of the user who originated the DDL change, the type of the object, and the object's name. This information can be quite useful for tracking purposes.
```
connect sys/manager;

drop table perfstat.stats$servererror_log;

create table 
   perfstat.stats$servererror_log 
(
   error     varchar2(30),
   timestamp date,
   username  varchar2(30),
   osuser    varchar2(30),
   machine   varchar2(64),
   process   varchar2(8),
   program   varchar2(48)
)
tablespace perfstat
;
```

servererror table -- The code snippet below creates an Oracle table that will capture all information relating to PL/SQL errors:

connect sys/manager;

drop table perfstat.stats$servererror_log;

create table 
   perfstat.stats$servererror_log 
(
   error     varchar2(30),
   timestamp date,
   username  varchar2(30),
   osuser    varchar2(30),
   machine   varchar2(64),
   process   varchar2(8),
   program   varchar2(48)
)
tablespace perfstat
;

login and logoff table
-- We have created a table called stats$user_log that can be used to trace both login and logoff events. Notice that this table contains the Oracle user ID, the name of the host server where the connection originated, the last program that was executed by the Oracle user, as well as their login and logoff times. Also, notice a special derived column called elapsed_minutes that is essentially the time difference (expressed in minutes) between the login time and logoff time.
```
connect sys/manager;

drop table perfstat.stats$user_log;

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)
)
;
```

Inside the Trigger Definition Scripts

Once we've created the Oracle tables to support the system-level triggers, the next step is to actually write to triggers to add the data to the Oracle STATSPACK extension tables. By storing system event data in Oracle tables, we have the ability to track user behavior over time and audit and monitor important usage trends. Let's examine some working triggers to see how they function:

DDL Trigger -- The DDL trigger executes every time a DDL statement is executed, and adds new entries to the stats$ddl_log table.

connect sys/manager

create or replace trigger
   DDLTrigger
AFTER DDL ON DATABASE
BEGIN
insert into
   perfstat.stats$ddl_log
(
   user_name,
   ddl_date,
   ddl_type,
   object_type,
   owner,
   object_name
)
VALUES
(
   ora_login_user,
   sysdate,
   ora_sysevent,
   ora_dict_obj_type,
   ora_dict_obj_owner,
   ora_dict_obj_name
);
END;
/

Servererror trigger -- The servererror trigger takes whatever server error was generated from Oracle PL/SQL and places it into an Oracle table. Note that by capturing the user ID and the time of the error, the Oracle administrator can build an insert trigger on the stats dollar or server error log table and immediately be notified via e-mail whenever a server error occurs. Click here for Important issues with the servererror trigger.

connect sys/manager

create or replace trigger
   log_errors_trig
after servererror on database
declare
   var_user      varchar2(30);
   var_osuser    varchar2(30);
   var_machine   varchar2(64);
   var_process   varchar2(9);
   var_program   varchar2(48);
begin
   select
      username,
      osuser,
      machine,
      process,
      program
   into
      var_user,
      var_osuser,
      var_machine,
      var_process,
      var_program
   from
      v$session
   where
      audsid=userenv('sessionid');

   insert into
      perfstat.stats$servererror_log
   values(
      dbms_standard.server_error(1),
      sysdate,
      var_user,
      var_osuser,
      var_machine,
      var_process,
      var_program);
end;
/

Logon trigger -- Next, we need to take look at the login trigger. In the code listing below we see that we are inserting into a table called stats_user_log. Inside the logon trigger you'll notice that we only populate selected columns of the table. At login time we only populate the user ID of the person who logs in and the time when they log into the system. We will use logoff trigger to fill in all the additional columns, including the all-important elapsed_minutes column.

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
);
COMMIT;
END;
/

Logoff trigger -- Using the logoff trigger functionality, we can gain information about the time that the end user logged off of the system, the last program they were executing, the host that they were on as well as the elapsed time for that individual user. The code listing below shows how we implement this using an Oracle trigger:

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;
COMMIT;
END;
/

Now that we have seen the scripts, let's take a look at how we can get great reports on the behavior of Oracle system events.

Putting It All Together

Once we have all of this information captured in the Oracle tables and the system level triggers, we can now add great reports that show the behavior of things within our Oracle database. As we know, the Oracle administrator is very interested in information that relates to end-user activity, and we also want to be able to track all the significant DDL, server errors, and user login information.

Servererror Reports

Servererror reports are especially useful for auditing the behavior of PL/SQL in your production environment. Any time an error occurs, the servererror trigger will log the error to a table, and you can write summary reports of all Oracle server errors.

Sat Mar 09                                                      page    1
                         Production Server Error Report
                            Rollup of errors my day

                                                                    Error
Date     Hr   MESSAGE                                               Count
------------- -------------------------------------------------- --------
03-08         ORA-02289: sequence does not exist                    2,421
              ORA-01001: invalid cursor                               480
              ORA-00054: resource busy and acquire with NOWAIT s      114
              ORA-00942: table or view does not exist                  39
              ORA-00942: table or view does not exist                  10
              ORA-01017: invalid username/password; logon denied        2
              ORA-00920: invalid relational operator                    1
              ORA-01445: cannot select ROWID from a join view wi        1


03-09         ORA-01001: invalid cursor                                25
              ORA-02289: sequence does not exist                       12
              ORA-00942: table or view does not exist                  11
              ORA-00054: resource busy and acquire with NOWAIT s       10
              ORA-01017: invalid username/password; logon denied        2

This report shows daily details, but we can also create a weekly rollup of server errors:

Mon Jul 22                                                   page    1
                Production Database Changes
                     Summary DDL Report

                           Changed         Number of         
DDL_D USER_NAME            Object          Production Changes 
----- -------------------- --------------- ----------         
07-21 MWCEDI               PACKAGE                  6         
      MWCEDI               PACKAGE BODY             6         
*****                                      ----------         
sum                                                12         
                                                        
                                                     
07-17 MWCEDI               PACKAGE                  3         
      MWCEDI               PACKAGE BODY             3         
*****                                      ----------         
sum                                                 6 
                                                    
                                                       
07-16 EUL_MWC              VIEW                     2         
*****                                      ----------         
sum                                                 2
                                                     
                                                     
07-15 MWCEDI               PACKAGE                  5 
      MWCEDI               PACKAGE BODY             5         
      APPS                 VIEW                     1         
      MWCEDI               PROCEDURE                1         
*****                                      ----------- 
sum                                                12

Oracle Logon Report

We can use the logon and logoff trigger to store information into stats$user_log and then write SQL to get detailed reports on average visit length, number of logons per hour, and many other useful auditing reports.

                   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


Day        HO NUMBER_OF_LOGINS
---------- -- ----------------
02-03-06   11               37
           12               28
           13               45
           14               38
           15               26
           16               26
           17               25
           18               26
           19               26
           20               26
           21               49
           22               26
           23               24

DDL Reports

This report is critical for the Oracle DBA who must track changes to their production database. This report uses the DDL trigger and produces a complete audit log of all Oracle databases changes.

            Date         Type     Object      Object
User       of DDL       of DDL   Type        Name
---------- ------------ -------- ----------- ----------------------------
SYS        03-07 10:11  DROP     TRIGGER     ERROR_AUDIT_TRIGGER
APPS       03-07 10:12  ALTER    TRIGGER     LOG_ERRORS_TRIG
APPS       03-07 10:14  ALTER    TRIGGER     LOG_ERRORS_TRIG
SYS        03-07 10:23  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
SYS        03-07 10:24  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
SYS        03-07 10:25  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
SYS        03-07 10:27  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
SYS        03-07 10:28  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
SYS        03-07 10:33  DROP     TRIGGER     LOG_ERRORS_TRIG
OPS$ORACLE 03-07 12:56  CREATE   TABLESPACE  JANEDI
OPS$ORACLE 03-07 12:57  CREATE   TABLESPACE  JANHF
APPS       03-07 13:10  ALTER    PACKAGE     PA_MC_CURRENCY_PKG
APPS       03-07 13:10  ALTER    TRIGGER     PA_MRC_DRAFT_INV_ITEMS_AIUD
JANEDI     03-07 14:15  CREATE   TABLE       JAN_EDI_HEADERS
JANEDI     03-07 14:15  CREATE   INDEX       JAN_EDI_HEAD_N1
JANEDI     03-07 14:15  CREATE   TABLE       JAN_EDI_LINES
JANEDI     03-07 14:15  CREATE   INDEX       JAN_EDI_LINE_N1
JANEDI     03-07 14:15  CREATE   TABLE       JAN_EDI_ERRORS
JANEDI     03-07 14:15  CREATE   TABLE       JAN_EDI_GP_ORDERS
JANEDI     03-07 14:15  CREATE   INDEX       JAN_EDI_GP_N1
JANEDI     03-07 14:15  CREATE   INDEX       JAN_EDI_GP_N2
JANEDI     03-07 14:15  CREATE   TABLE       JAN_EDI_GP_ERRORS
JANEDI     03-07 14:15  CREATE   TABLE       JAN_EDI_GP_CONTROLS
JANEDI     03-07 14:15  CREATE   INDEX       JAN_EDI_GP_CNTL_N1
JANEDI     03-07 14:18  ALTER    TABLE       JAN_EDI_HEADERS
JANEDI     03-07 14:18  ALTER    TABLE       JAN_EDI_LINES
JANEDI     03-07 14:18  ALTER    TABLE       JAN_EDI_ERRORS
JANEDI     03-07 14:18  ALTER    TABLE       JAN_EDI_GP_ORDERS
JANEDI     03-07 14:18  ALTER    TABLE       JAN_EDI_GP_ERRORS
JANEDI     03-07 14:18  ALTER    TABLE       JAN_EDI_GP_CONTROLS
OPS$ORACLE 03-07 14:20  ALTER    TABLE       JAN_DEPARTMENTS
OPS$ORACLE 03-07 14:20  ALTER    TABLE       JAN_FORECAST_INTERFACE
OPS$ORACLE 03-07 14:20  ALTER    TABLE       JAN_XREF_LOAD
OPS$ORACLE 03-07 14:20  ALTER    TABLE       JAN_JOBS_INTERFACE
OPS$ORACLE 03-07 14:20  ALTER    TABLE       JAN_ROUTING_COMMENTS
OPS$ORACLE 03-07 14:20  ALTER    TABLE       JAN_EDI_HEADERS
OPS$ORACLE 03-07 14:20  ALTER    TABLE       JAN_EDI_LINES
OPS$ORACLE 03-07 14:20  ALTER    TABLE       JAN_EDI_ERRORS
OPS$ORACLE 03-07 14:20  ALTER    TABLE       JAN_EDI_GP_ORDERS
OPS$ORACLE 03-07 14:20  ALTER    TABLE       JAN_EDI_GP_ERRORS
OPS$ORACLE 03-07 14:20  ALTER    TABLE       JAN_EDI_GP_CONTROLS
SYS        03-07 15:44  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
SYS        03-07 15:45  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
OPS$ORACLE 03-08 07:20  DROP     TABLE       ORACHECK_FS_TEMP
OPS$ORACLE 03-08 07:20  CREATE   TABLE       ORACHECK_FS_TEMP
APPS       03-08 11:21  ALTER    TRIGGER     ERROR_AUDIT_TRIGGER
SYS        03-08 11:21  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
APPS       03-08 11:23  ALTER    TRIGGER     ERROR_AUDIT_TRIGGER
SYS        03-08 11:25  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
SYS        03-08 12:54  ALTER    TRIGGER     ERROR_AUDIT_TRIGGER
SYS        03-08 12:54  DROP     TRIGGER     ERROR_AUDIT_TRIGGER
SYS        03-08 12:56  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
OPS$ORACLE 03-09 07:20  DROP     TABLE       ORACHECK_FS_TEMP
OPS$ORACLE 03-09 07:20  CREATE   TABLE       ORACHECK_FS_TEMP

Reader Comments

While all of the above tables\triggers record useful information, there is a problem with logging system errors in Oracle 10.2. MOSC 455008.1 indicates that this is caused by Bug 6072872 which causes unnecessary tracing of internal error messages that should be suppressed. The result of this bug is extensive trace files in the udump folder, it also generates misleading errors in the Job History when viewed thru the Web OEM. This bug causes all jobs to have a status of STOPPED (the reason being Job slave process was terminated) even though the jobs have completed successfully. I also discovered the dreaded ORA-25228 error, and you may want to refer people to MOSC 455008.1.

The alternative solutions appear to be to either

Disable the trigger
Wrap the current trigger in an IF statement that would ignore 25228 errors.
Apply Patch 6057422 or
Upgrade to 10.2.0.4.

Unfortunately, I have found that neither of the first two solve the incorrect status problem in OEM. We plan to start the upgrade process to 10.2.0.4 in the near future and Ill pass on the results at that time.

Larry Olson
Sandia National Laboratories
National Systems Modeling & Simulation

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.

��