ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用触发器记录数据库用户登陆信息

用触发器记录数据库用户登陆信息

原创 Linux操作系统 作者:YallonKing 时间:2012-11-13 15:02:35 0 删除 编辑
贴个触发器,作用:记录数据库用户登陆信息
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-07

  • 博文量
    72
  • 访问量
    259981