The Trigger-Happy DBA – System Triggers

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                WORKGROUPD2JW5027  DATABASE
SCOTT    12-JAN-04 578                WORKGROUPD2JW5027  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                 WORKGROUPD2JW5027  DATABASE
SCOTT    12-JAN-04 578                 WORKGROUPD2JW5027  DATABASE
STECAL   12-JAN-04 579                 WORKGROUPD2JW5027  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                 WORKGROUPD2JW5027  DATABASE
SCOTT    12-JAN-04 578                 WORKGROUPD2JW5027  DATABASE
STECAL   12-JAN-04 579                 WORKGROUPD2JW5027  DATABASE
STECAL   12-JAN-04 581                 WORKGROUPD2JW5027  DATABASE
STECAL   12-JAN-04 581                 WORKGROUPD2JW5027  DATABASE
SCOTT    12-JAN-04 582                 WORKGROUPD2JW5027  DATABASE
SCOTT    12-JAN-04 582                 WORKGROUPD2JW5027  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