|
Audit Trail
|
In this issue, I will talk about web system of today.
In current client/server system, each client establishes a session to database server.
In Web system, on the other hand, AP server establishes the session and
also uses connection pooling.
Is it possible to audit database activity under such circumstances?
I will analyze an auditing process of three hierarchies: DB server, AP server and
client.
I create a simple program on AP server as follows:
(1)I connect to a server first and issue a simple SQL statement without using the
connection pool. And then, disconnect from the server and repeat this process 100 times.
(2) I repeat the same process 100 times using the connection pool.
(3) Now, I examine what types of audit data can be obtained from both processes (1) and (2).
First, I execute the process (1) and then view DBA_AUDIT_SESSION.
*******************************************************************************
1 select os_username
2 ,username
3 ,terminal
4 ,to_char(timestamp,'hh24:mi:ss') timestamp
5 ,action_name
6 ,to_char(logoff_time,'hh24:mi:ss') logoff_time
7 ,returncode
8 from dba_audit_session ;
OS_USERNAM USERNAME TERMINAL TIMESTAMP ACTION_NAM LOGOFF_TIME RETURNCODE
---------- -------- -------- --------- ---------- ----------- ----------
SYSTEM ORABM unknown 15:44:43 LOGOFF 15:44:43 0
SYSTEM ORABM unknown 15:44:44 LOGOFF 15:44:44 0
SYSTEM ORABM unknown 15:44:44 LOGOFF 15:44:44 0
SYSTEM ORABM unknown 15:44:44 LOGOFF 15:44:45 0
SYSTEM ORABM unknown 15:44:45 LOGOFF 15:44:45 0
SYSTEM ORABM unknown 15:45:04 LOGOFF 15:45:04 0
...
SYSTEM ORABM unknown 15:45:04 LOGOFF 15:45:04 0
SYSTEM ORABM unknown 15:45:04 LOGOFF 15:45:04 0
SYSTEM ORABM unknown 15:45:05 LOGOFF 15:45:05 0
SYSTEM ORABM unknown 15:45:05 LOGOFF 15:45:05 0
^^^^^^ ^^^^^^^
100 rows selected
*******************************************************************************
|
You may see unknown in TERMINAL column, which means audit data
is unknown.
Actually, audit data contains information collected when AP server
establishes a connection. Audit data is not capable of recording
the information of client.
BTW, the time required for executing this program is 21.831(s).
Next, I execute the process (2) using the connection pool to check the audit data.
*******************************************************************************
1 select os_username
2 ,username
3 ,terminal
4 ,to_char(timestamp,'hh24:mi:ss') timestamp
5 ,action_name
6 ,to_char(logoff_time,'hh24:mi:ss') logoff_time
7 ,returncode
8 from dba_audit_session ;
OS_USERNAM USERNAME TERMINAL TIMESTAMP ACTION_NAM LOGOFF_TIME RETURNCODE
---------- -------- -------- --------- ---------- ----------- ----------
SYSTEM ORABM unknown 15:44:43 LOGOFF 15:44:43 0
SYSTEM ORABM unknown 15:44:44 LOGOFF 15:44:44 0
SYSTEM ORABM unknown 15:44:44 LOGOFF 15:44:44 0
SYSTEM ORABM unknown 15:44:44 LOGOFF 15:44:45 0
SYSTEM ORABM unknown 15:44:45 LOGOFF 15:44:45 0
...
SYSTEM ORABM unknown 15:45:04 LOGOFF 15:45:04 0
SYSTEM ORABM unknown 15:45:04 LOGOFF 15:45:04 0
SYSTEM ORABM unknown 15:45:05 LOGOFF 15:45:05 0
SYSTEM ORABM unknown 15:45:05 LOGOFF 15:45:05 0
SYSTEM ORABM unknown 15:52:06 LOGON 0<-(1)
^^^^^^ ^^^^^^^ ^^^^^
101 rows selected
*******************************************************************************
|
One session takes care of 100 programs at a time.
Also, ACTION_NAME column indicates LOGON, which means
the process logs on continuously.
TERMINAL column indicates unknown.
I wonder if this result indicates that Web system is only
capable of auditing up to AP server activity.
This time, the time required for executing the program is 0.791(s).
Connection pool does improve performance.
In next issue,, I will analyze the database auditing in WEB system.
Koji Shinkubo
|
|
