用Trigger实现的Logon/Logoff Audit

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;