audit users login and logoff

方法：
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/763776/viewspace-615944/