Auditing Categories - Implementing Database Security and Auditing - page 351

Figure 12.1

Failed login
reports.

you saw how to implement this type of audit trail in DB2
using event monitors and how to implement this type of audit trail in SQL
Server using traces. While the context in that section was actually one of a
hacker trying to plant a Trojan that collects this information to be used in
launching an attack, the methods shown are precisely what you would use
to create a login/logout audit trail in DB2 or SQL Server. Oracle has more
than one way to produce this audit trail, but perhaps the easiest one is using
system-level triggers that have been around since Oracle 8i.
Just as an Oracle trigger fires when you insert or update a row, a system-
level trigger fires at specific system events such as logon, logoff, and DDL
execution. Let's see how to implement this type of audit trail.
First, create a table where you will keep the information:

In Section 9.6

create table user_login_audit
(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
login_day date,
login_time varchar2(10),
logout_day date,
logout_time varchar2(10)
);

Next, create the trigger to be fired upon a new login:

create or replace trigger
user_login_audit_trigger
AFTER LOGON ON DATABASE