Praćenje sesija koristeći trigger kod prijave
Friday, 26.06.2009 – msuticOvdje u firmi imamo Web aplikaciju koja se često spaja na bazu, izvrši nekoliko naredbi i odspaja nakon nekoliko sekundi. Vrlo je teško uhvatiti te sessije kako bi doznali što Web aplikacija točno radi na bazi. Kako bi si pomogao u tome kreirao sam trigger koji se okida kod prijave sessije i pokreće praćenje. Informacije o “trace” datoteci zapisuje u info tablicu.
Dakle, prvo ću kreirati tablicu:
SQL> CREATE TABLE logon_audit_info ( logon_time DATE , username VARCHAR2(100), tracefile VARCHAR2(100) );
Potom ću kreirati trigger koji će hvatati sesije pokrenute od strane korisnika “MSUTIC”. Naravno vi ćete umjesto “MSUTIC” koristiti naziv korisnika kojeg vaša Web aplikacija koristi za spajanje na bazu. “MSUTIC” je ovdje korišten samo u testne svrhe.
CREATE OR REPLACE TRIGGER audit_login_trigger
AFTER LOGON ON DATABASE
DECLARE
l_user dba_users.username%TYPE := USER;
l_sql VARCHAR2 (500);
l_tracefile VARCHAR2 (100);
l_time DATE;
BEGIN
l_sql :=
'alter session set events '
|| CHR (39)
|| '10046 trace name context forever, level 12'
|| CHR (39);
l_time := SYSDATE;
IF (l_user = 'MSUTIC')
THEN
EXECUTE IMMEDIATE l_sql;
SELECT pa.VALUE
|| '/'
|| LOWER (SYS_CONTEXT ('userenv', 'instance_name'))
|| '_ora_'
|| p.spid
|| '.trc'
INTO l_tracefile
FROM v$session s, v$process p, v$parameter pa
WHERE pa.NAME = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT ('USERENV', 'SESSIONID');
INSERT INTO logon_audit_info
(logon_time, username, tracefile
)
VALUES (l_time, l_user, l_tracefile
);
COMMIT;
END IF;
END;
/
I sad mali test.
$ sqlplus /nolog SQL*Plus: Release 11.1.0.7.0 - Production on Fri Jun 26 14:22:47 2009 Copyright (c) 1982, 2008, Oracle. All rights reserved. SQL> connect msutic@test11 Enter password: ****** Connected. SQL> select count(*) from test; COUNT(*) ---------- 1 SQL> exit
Izvršio sam “select count(*) from test” i odmah se odjavio.
SQL> set lines 200 SQL> col logon_time for a20 SQL> col username for a15 SQL> col tracefile for a150 SQL> select * from logon_audit_info; SQL> select to_char(logon_time,'dd.mm.yyyy hh24:mi:ss'),username,tracefile from logon_audit_info; TO_CHAR(LOGON_TIME, USERNAME TRACEFILE ------------------- --------------- ------------------------------------------------------------ 26.06.2009 14:25:59 MSUTIC /oracle/diag/rdbms/test11/test11/trace/test11_ora_31218.trc
Kao što vidite lokacija “trace” datoteke mi se zapisala u tablicu.
Pa da provjerimo jeli to ispravna “trace” datoteka.
$vi /oracle/diag/rdbms/test11/test11/trace/test11_ora_31218.trc ... ... *** 2009-06-26 14:26:07.877 WAIT #0: nam='SQL*Net message from client' ela= 8173860 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1246019167877543 ===================== PARSING IN CURSOR #2 len=25 dep=0 uid=84 oct=3 lid=84 tim=1246019167868491 hv=297253644 ad='3ac91a38' sqlid='7b2twsn8vgfsc' select count(*) from test END OF STMT PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1950795681,tim=1246019167868491 EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1950795681,tim=1246019167868491 WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1246019167878005 FETCH #2:c=0,e=0,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=1950795681,tim=1246019167868491 STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us)' STAT #2 id=2 cnt=1 pid=1 pos=1 obj=76970 op='TABLE ACCESS FULL TEST (cr=7 pr=0 pw=0 time=0 us cost=3 size=0 card=1)' WAIT #2: nam='SQL*Net message from client' ela= 265 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1246019167878544 FETCH #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1950795681,tim=1246019167868491 WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1246019167878647 ... ...
Znači imam ispravnu “trace” datoteku i mogu napraviti detaljnu analizu.
Nakon što vam trigger više neće biti potreban ne morate ga brisati, već ga se može samo deaktivirati pa kad idući put bude potrebno neke sesije pratiti samo ćete ga ponovo aktivirati.
alter trigger audit_logon_trigger disable;