| |
 |
|
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
pre-computation 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. |
|
|
|
and include the URL for the page.
