用觸發器記錄資料庫使用者登陸資訊

貼個觸發器，作用：記錄資料庫使用者登陸資訊
SQL> create table log$information(
  2  logon_time timestamp,
  3  host_name varchar2(100),
  4  username varchar2(40),
  5  schemaname varchar2(40),
  6  sessionuser varchar2(40),
  7  ip_address varchar2(100)
  8  );

Table created.

SQL> CREATE OR REPLACE TRIGGER tr_login_record
  2     AFTER LOGON
  3     ON DATABASE
  4  BEGIN
  5     INSERT INTO log$information
  6        SELECT SYSTIMESTAMP,
  7               SYS_CONTEXT ('USERENV', 'HOST'),
  8               SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
  9               SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA'),
 10               SYS_CONTEXT ('USERENV', 'SESSION_USER'),
 11               SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
 12          FROM DUAL;
 13     COMMIT;
 14  EXCEPTION
 15     WHEN OTHERS
 16     THEN
 17        NULL;
 18  END;
 19  /

Trigger created.

SQL> select * from log$information;
LOGON_TIME                          HOST_NAME                      USERNAME   SCHEMANAME           SESSIONUSE IP_ADDRESS
----------------------------------- ------------------------------ ---------- -------------------- ---------- --------------------
10-NOV-12 12.49.02.699244 AM        WORKGROUP\YALLONKING-PC        SYS        SYS                  SYSTEM     192.168.137.1
10-NOV-12 12.49.02.995042 AM        WORKGROUP\YALLONKING-PC        SYS        SYS                  SYSTEM     192.168.137.1
10-NOV-12 12.49.14.847775 AM        OELx64                         SYS        SYS                  SYS
10-NOV-12 12.49.39.985996 AM        WORKGROUP\YALLONKING-PC        SYS        SYS                  SYSTEM     192.168.137.1
10-NOV-12 12.50.15.092293 AM        OELx64                         SYS        SYS                  SYS
10-NOV-12 12.51.15.348530 AM        OELx64                         SYS        SYS                  SYS
10-NOV-12 12.52.15.582945 AM        OELx64                         SYS        SYS                  SYS
10-NOV-12 12.53.15.847327 AM        OELx64                         SYS        SYS                  SYS
10-NOV-12 12.53.19.688558 AM        WORKGROUP\YALLONKING-PC        SYS        SYS                  TEST       192.168.137.1
10-NOV-12 12.53.19.937570 AM        WORKGROUP\YALLONKING-PC        SYS        SYS                  TEST       192.168.137.1

10 rows selected.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26143577/viewspace-749112/，如需轉載，請註明出處，否則將追究法律責任。