当前位置:首页 > 开发 > 数据库 > 正文

用Trigger实现的Logon/Logoff Audit

发表于: 2008-07-01   作者:crownquartz   来源:转载   浏览:
sql
摘要: 1. 创建存储Audit信息的表: CREATE TABLE logonaudittable(  event       VARCHAR2(10),  sid         NUMBER,  serial# &nbs

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;

用Trigger实现的Logon/Logoff Audit

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号