ITPub博客

首页 > Linux操作系统 > Linux操作系统 > audit users login and logoff

audit users login and logoff

原创 Linux操作系统 作者:cpayu2006 时间:2009-10-02 13:25:48 0 删除 编辑
方法:
1. Create the table to capture the LOGIN and LOGOUT timings.

CREATE TABLE loginaudit
(
  event       VARCHAR2(10),
  sid         NUMBER,
  serial#     NUMBER,
  timestamp   DATE,
  username    VARCHAR2(30),
  osuserid    VARCHAR2(30),
  machinename VARCHAR2(64)
)
/

2. Create LOGON trigger at DATABASE level.

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 loginaudit VALUES ( 'LOGON', v_sid, v_serial, sysdate,
      user, osuserid, machinename );

  CLOSE c1;
END;
/

3. Create 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 loginaudit VALUES ( 'LOGOFF', v_sid, v_serial, sysdate,
    user, osuserid, machinename );

  CLOSE c1;
END;
/


Check the result:
~~~~~~~~~~~~~~~~~
select event, sid, serial#, username, osuserid,
       to_char(timestamp,'dd-mon-yyyy hh24:mi:ss') as TIMESTAMP, machinename
from loginaudit;


需要注意的是:

查看当前用户的sid和serial#:

select sid, serial#, status from v$session where audsid=userenv('sessionid');


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/763776/viewspace-615944/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 新的一周
请登录后发表评论 登录
全部评论

注册时间:2009-09-25

  • 博文量
    14
  • 访问量
    11355