The Trigger-Happy DBA - System Triggers

January 22, 2004

So far, the trigger-happy DBA series has dealt with triggers related to Data Manipulation Language (DML) operations. As mentioned in the beginning of the series, another type of useful trigger is that of the system trigger. System triggers can be delineated into two categories: those based on Data Definition Language (DDL) statements, and those based upon database events. Use of system triggers can greatly expand a DBA's ability to monitor database activity and events. Moreover, after having read this article, you'll be able to sharp shoot someone who asks, "How many triggers does Oracle have?" Most people will seize upon the before/during/after insert/update/delete on row/table easy-to-answer OCP test type of question (and answer), which is largely correct where plain vanilla DML triggers are concerned. How would you count INSTEAD-OF triggers when it comes to DML? So, how many other triggers does Oracle have or allow?

The syntax for creating a system trigger is very similar to the syntax used for creating DML triggers.

The number of system triggers available to the DBA is 11 under the short and simple plan. If you want the deluxe version or variety, you can refer to the more than 20 system-defined event attributes shown in Chapter 16 of the Oracle9i Application Developer's Guide. In this month's article, we will look at the 11 "simple" triggers related to DDL and database events. Let's identify these 11 triggers before going further.

Some common sense is in order here. If you wait long enough and visit enough DBA-related question-and-answer web sites, inevitably, and amusingly, you will see the question about, "I'm trying to create a trigger that does X and Y before a user logs on - how do I do that?" That may be possible in some future version of Oracle - the version that senses when you are about to log on? - but don't hold your breath waiting for that release date!

So, the BEFORE and AFTER timing part really matters for the system events shown above, and you have a bit more flexibility with respect to the DDL statements. You will also notice there are no DURING's for the "when" part, and perhaps not so obvious, there are no INSTEAD-OF triggers for system events. What about TRUNCATE statements, you ask. TRUNCATE is a DDL statement, but unfortunately, Oracle does not capture this event (as far as triggers are concerned).

Let's construct a simple auditing type of trigger that captures a user's logon information. With auditing in mind, as with when to fire a DML type of trigger, timing matters. Maybe you have a sensitive database, one where you need to capture a user's session information. It would be more appropriate to capture a user's access/logon to the sensitive database immediately after that user logs on as opposed to capturing the session information before logging off. Who says you have to log off gracefully? If a session is abnormally terminated, does a BEFORE LOGOFF trigger fire?

As a side note, how can you obtain session information? One way is to use the SYS_CONTEXT function. Oracle's SQL Reference manual lists 37 parameters you can use with SYS_CONTEXT to obtain session information. See http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions122a.htm#1038178 for more information regarding this feature.

Here is our audit table:

SQL> CREATE TABLE session_info
  2  (username VARCHAR2(30),
  3  logon_date DATE,
  4  session_id VARCHAR2(30),
  5  ip_addr VARCHAR2(30),
  6  hostname VARCHAR2(30),
  7  auth_type VARCHAR2(30));

Table created.

Here is the code for the BEFORE LOGOFF trigger:

SQL> CREATE OR REPLACE TRIGGER trg_session_info
  2  BEFORE LOGOFF
  3  ON DATABASE
  4  DECLARE
  5   session_id VARCHAR2(30);
  6   ip_addr    VARCHAR2(30);
  7   hostname   VARCHAR2(30);
  8   auth_type  VARCHAR2(30);
  9  BEGIN
 10   SELECT sys_context ('USERENV', 'SESSIONID')
 11   INTO session_id
 12   FROM dual;
 13  
 14   SELECT sys_context ('USERENV', 'IP_ADDRESS')
 15   INTO ip_addr
 16   FROM dual;
 17   
 18   SELECT sys_context ('USERENV', 'HOST')
 19   INTO hostname
 20   FROM dual;
 21   
 22   SELECT sys_context ('USERENV', 'AUTHENTICATION_TYPE')
 23   INTO auth_type
 24   FROM dual;
 25   
 26   INSERT INTO session_info VALUES
 27   (user, sysdate, session_id, ip_addr, hostname, auth_type);
 28  END;
 29  /

Trigger created.

Let's connect as Scott then return as someone else:

SQL> select * from session_info;

USERNAME LOGON_DAT SESSION_ID IP_ADDR HOSTNAME            AUTH_TYPE
-------- --------- ---------- ------- ------------------- ---------
STECAL   12-JAN-04 577                WORKGROUP\D2JW5027  DATABASE
SCOTT    12-JAN-04 578                WORKGROUP\D2JW5027  DATABASE

Looks like the trigger worked, twice in fact, because when I left to connect as Scott, the newly created trg_session_info trigger captured my information as well.

Let's go back as Scott, and have Scott's session abnormally terminated. Does the BEFORE LOGOFF trigger capture Scott's session information?

SQL> select * from session_info;

USERNAME LOGON_DAT SESSION_ID IP_ADDR  HOSTNAME            AUTH_TYPE
-------- --------- ---------- -------- ------------------- ----------
STECAL   12-JAN-04 577                 WORKGROUP\D2JW5027  DATABASE
SCOTT    12-JAN-04 578                 WORKGROUP\D2JW5027  DATABASE
STECAL   12-JAN-04 579                 WORKGROUP\D2JW5027  DATABASE

The answer is no. In this particular case, Scott's session was terminated by clicking on the Windows close button. Assuming Scott is a malicious user, he could have viewed salaries and other personal information with some degree of obscurity. Use of an AFTER LOGON trigger would have immediately captured his session information. Of course, this trigger, in of and by itself, is not sufficient to fully protect access to sensitive information, but it is a means of letting potentially malicious (or snooping) users know they are being watched or monitored. Locks on doors help keep honest people honest, so the saying goes. Same idea here.

Changing the trigger to AFTER LOGON yields the following results with Scott logging on, and his session being terminated:

SQL> select * from session_info;

USERNAME LOGON_DAT SESSION_ID IP_ADDR  HOSTNAME            AUTH_TYPE
-------- --------- ---------- -------- ------------------- ----------
STECAL   12-JAN-04 577                 WORKGROUP\D2JW5027  DATABASE
SCOTT    12-JAN-04 578                 WORKGROUP\D2JW5027  DATABASE
STECAL   12-JAN-04 579                 WORKGROUP\D2JW5027  DATABASE
STECAL   12-JAN-04 581                 WORKGROUP\D2JW5027  DATABASE
STECAL   12-JAN-04 581                 WORKGROUP\D2JW5027  DATABASE
SCOTT    12-JAN-04 582                 WORKGROUP\D2JW5027  DATABASE
SCOTT    12-JAN-04 582                 WORKGROUP\D2JW5027  DATABASE

7 rows selected.

Look at the last four rows - aside from the date (the times would be different per user), it looks like two rows per user. This is an example of two things. First, do not forget to clean up after yourself (remove unnecessary triggers), and second, maybe you will want to capture the AFTER LOGON and BEFORE LOGOFF times (of course, it would be hard to change machines in the middle of a session!).

Another use of a system trigger may help you (as the DBA) identify users in need of some help when it comes to forming SQL queries. The SERVERERROR system event, when combined with the CURRENT_SQL parameter in the SYS_CONTEXT function, can flag or identify users who frequently make mistakes. The CURRENT_SQL parameter "returns the current SQL that triggered the fine-grained auditing event. You can specify this attribute only inside the event handler for the Fine-Grained Auditing feature." (From the SQL Reference manual) Even without FGAC, you can set up a simple trigger-audit table relationship as follows:

SQL> CREATE TABLE error_info
  2  (username VARCHAR2(30),
  3  logon_date DATE,
  4  session_id VARCHAR2(30),
  5  sql_statement VARCHAR2(64));

Table created.


SQL> CREATE OR REPLACE TRIGGER trg_server_error
  2  AFTER SERVERERROR
  3  ON DATABASE
  4  DECLARE
  5   session_id VARCHAR2(30);
  6   sql_statement   VARCHAR2(64);
  7  BEGIN
  8   SELECT sys_context ('USERENV', 'SESSIONID')
  9   INTO session_id
 10   FROM dual;
 11  
 12   SELECT sys_context ('USERENV', 'CURRENT_SQL')
 13   INTO sql_statement
 14   FROM dual;
 15   
 16   INSERT INTO error_info VALUES
 17   (user, sysdate, session_id, sql_statement);
 18  END;
 19  /

Trigger created.

SQL> select * from some_table;
select * from some_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from error_info;

USERNAME LOGON_DAT SESSION_ID SQL_STATEMENT
-------- --------- ---------- ---------------
STECAL   12-JAN-04 583

There are a great many things you can do with triggers, whether they are based on DML statements or system events. As a developer or DBA (or both), there is no such thing as having too many tricks up your sleeve. In terms of job or role separation, you can think of the DML triggers as being in the purview of the developer, and the system event triggers being in the DBA's, but a good DBA should possess some decent programming skills of his or her own, and that's where knowing how to avoid problems with DML triggers comes into play. Being and staying well-informed on the use (and limitations) of triggers will make you a trigger-happy DBA.

» See All Articles by Columnist Steve Callan