1. 创建存储Audit信息的表:
CREATE TABLE logonaudittable
(
event VARCHAR2(10),
sid NUMBER,
serial# NUMBER,
timestamp DATE,
username VARCHAR2(30),
osuserid VARCHAR2(30),
machinename VARCHAR2(64)
)
/
2. 创建logon trigger:
CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
v_sid NUMBER(10);
v_serial NUMBER(10);
CURSOR c1 IS
SELECT sid, serial#, osuser, machine
FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename;
INSERT INTO logonaudittable VALUES ( 'LOGON', v_sid, v_serial, sysdate,
user, osuserid, machinename );
CLOSE c1;
END;
/
3. 创建LOGOFF Trigger:
CREATE OR REPLACE TRIGGER logoffauditing
BEFORE LOGOFF ON database
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
v_sid NUMBER(10);
v_serial NUMBER(10);
CURSOR c1 IS
SELECT sid, serial#, osuser, machine
FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename;
INSERT INTO logonaudittable VALUES ( 'LOGOFF', v_sid, v_serial, sysdate,
user, osuserid, machinename );
CLOSE c1;
END;
/
查询结果:
~~~~~~
SQL> select event, sid, serial#, username, osuserid, to_char(timestamp,'dd-mon-yyyy hh24:mi:ss') as TIMESTAMP, machinename
from logonaudittable;