使用触发器记录oracle用户登陆信息

简介:

 Oracle 提供了强大的审计功能,可以针对用户级,系统级范围,以及标准审计,细粒度审计等多种方式来审计各种数据库层面上的操作。然很多中小型数据库需要记录用户的登陆登出信息,而又不希望牺牲太多的性能。基于这种情形,使用基于数据库级别的触发器可以简单的实现这个需求。

1、实现代码

  1. --创建表用于存储登陆或登出的统计信息

  2. CREATETABLE stats$user_log 

  3. user_id VARCHAR2 (30), 

  4. session_id NUMBER (8), 

  5. HOST VARCHAR2 (30), 

  6. last_program VARCHAR2 (48), 

  7. last_action VARCHAR2 (32), 

  8. last_module VARCHAR2 (32), 

  9. logon_day DATE

  10. logon_time VARCHAR2 (10), 

  11. logoff_day DATE

  12. logoff_time VARCHAR2 (10), 

  13. elapsed_minutes NUMBER (8) 

  14. ); 

  15. --创建登陆之后的触发器

  16. CREATEORREPLACETRIGGER logon_audit_trigger 

  17. AFTER LOGON 

  18. ONDATABASE

  19. BEGIN

  20. INSERTINTO stats$user_log 

  21. VALUES (USER

  22. SYS_CONTEXT ('USERENV''SESSIONID'), 

  23. SYS_CONTEXT ('USERENV''HOST'), 

  24. NULL

  25. NULL

  26. NULL

  27. SYSDATE, 

  28. TO_CHAR (SYSDATE, 'hh24:mi:ss'), 

  29. NULL

  30. NULL

  31. NULL); 

  32. END

  33. --创建登出之后的触发器

  34. CREATEORREPLACETRIGGER logoff_audit_trigger 

  35. BEFORE LOGOFF 

  36. ONDATABASE

  37. BEGIN

  38. -- ***************************************************

  39. -- Update the last action accessed

  40. -- ***************************************************

  41. UPDATE stats$user_log 

  42. SET last_action = 

  43. (SELECTaction

  44. FROM v$session 

  45. WHERE SYS_CONTEXT ('USERENV''SESSIONID') = audsid) 

  46. WHERE SYS_CONTEXT ('USERENV''SESSIONID') = session_id; 

  47. --***************************************************

  48. -- Update the last program accessed

  49. -- ***************************************************

  50. UPDATE stats$user_log 

  51. SET last_program = 

  52. (SELECT program 

  53. FROM v$session 

  54. WHERE SYS_CONTEXT ('USERENV''SESSIONID') = audsid) 

  55. WHERE SYS_CONTEXT ('USERENV''SESSIONID') = session_id; 

  56. -- ***************************************************

  57. -- Update the last module accessed

  58. -- ***************************************************

  59. UPDATE stats$user_log 

  60. SET last_module = 

  61. (SELECT module 

  62. FROM v$session 

  63. WHERE SYS_CONTEXT ('USERENV''SESSIONID') = audsid) 

  64. WHERE SYS_CONTEXT ('USERENV''SESSIONID') = session_id; 

  65. -- ***************************************************

  66. -- Update the logoff day

  67. -- ***************************************************

  68. UPDATE stats$user_log 

  69. SET logoff_day = SYSDATE 

  70. WHERE SYS_CONTEXT ('USERENV''SESSIONID') = session_id; 

  71. -- ***************************************************

  72. -- Update the logoff time

  73. -- ***************************************************

  74. UPDATE stats$user_log 

  75. SET logoff_time = TO_CHAR (SYSDATE, 'hh24:mi:ss'

  76. WHERE SYS_CONTEXT ('USERENV''SESSIONID') = session_id; 

  77. -- ***************************************************

  78. -- Compute the elapsed minutes

  79. -- ***************************************************

  80. UPDATE stats$user_log 

  81. SET elapsed_minutes = ROUND ( (logoff_day - logon_day) * 1440) 

  82. WHERE SYS_CONTEXT ('USERENV''SESSIONID') = session_id; 

  83. END

--创建表用于存储登陆或登出的统计信息
CREATE TABLE stats$user_log
(
   user_id           VARCHAR2 (30),
   session_id        NUMBER (8),
   HOST              VARCHAR2 (30),
   last_program      VARCHAR2 (48),
   last_action       VARCHAR2 (32),
   last_module       VARCHAR2 (32),
   logon_day         DATE,
   logon_time        VARCHAR2 (10),
   logoff_day        DATE,
   logoff_time       VARCHAR2 (10),
   elapsed_minutes   NUMBER (8)
);

--创建登陆之后的触发器
CREATE OR REPLACE TRIGGER logon_audit_trigger
   AFTER LOGON
   ON DATABASE
BEGIN
   INSERT INTO stats$user_log
        VALUES (USER,
                SYS_CONTEXT ('USERENV', 'SESSIONID'),
                SYS_CONTEXT ('USERENV', 'HOST'),
                NULL,
                NULL,
                NULL,
                SYSDATE,
                TO_CHAR (SYSDATE, 'hh24:mi:ss'),
                NULL,
                NULL,
                NULL);
END;
/

--创建登出之后的触发器
CREATE OR REPLACE TRIGGER logoff_audit_trigger
   BEFORE LOGOFF
   ON DATABASE
BEGIN
   -- ***************************************************
   -- Update the last action accessed
   -- ***************************************************
   UPDATE stats$user_log
      SET last_action =
             (SELECT action
                FROM v$session
               WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   --***************************************************
   -- Update the last program accessed
   -- ***************************************************
   UPDATE stats$user_log
      SET last_program =
             (SELECT program
                FROM v$session
               WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   -- ***************************************************
   -- Update the last module accessed
   -- ***************************************************
   UPDATE stats$user_log
      SET last_module =
             (SELECT module
                FROM v$session
               WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   -- ***************************************************
   -- Update the logoff day
   -- ***************************************************
   UPDATE stats$user_log
      SET logoff_day = SYSDATE
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   -- ***************************************************
   -- Update the logoff time
   -- ***************************************************
   UPDATE stats$user_log
      SET logoff_time = TO_CHAR (SYSDATE, 'hh24:mi:ss')
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   -- ***************************************************
   -- Compute the elapsed minutes
   -- ***************************************************
   UPDATE stats$user_log
      SET elapsed_minutes = ROUND ( (logoff_day - logon_day) * 1440)
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
END;
/

2、结果样例

  1. --查看用户的登入登出信息

  2. SQL> select * from sys.stats$user_log where rownum<3; 

  3. USER_ID SESSION_ID HOST LAST_PROGRAM LAST_MODULE LOGON_DAY LOGON_TIME LOGOFF_DA LOGOFF_TIM ELP_MINS 

  4. ---------- ---------- --------------- ---------------- ---------------- --------- ---------- --------- ---------- --------

  5. GX_ADMIN 5409517 v2012DB01u JDBC Thin Client JDBC Thin Client 24-OCT-13 12:20:30 24-OCT-13 16:20:30 240 

  6. GX_ADMIN 5409518 v2013DB01u JDBC Thin Client JDBC Thin Client 24-OCT-13 12:22:23 24-OCT-13 16:22:30 240 

  7. --汇总用户登陆时间 

  8. SQL> SELECT user_id, TRUNC (logon_day) logon_day, SUM (elapsed_minutes) total_time 

  9. FROM sys.stats$user_log 

  10. GROUPBY user_id, TRUNC (logon_day) ORDERBY 2; 

  11. USER_ID LOGON_DAY TOTAL_TIME 

  12. ------------------------------ --------- ----------

  13. GX_ADMIN 24-OCT-13 960 

  14. SYS 24-OCT-13 

  15. GX_ADMIN 25-OCT-13 2891 

  16. GX_WEBUSER 25-OCT-13 

  17. SYS 25-OCT-13 

  18. GX_WEBUSER 26-OCT-13 

  19. GX_ADMIN 26-OCT-13 2880 

  20. SYS 26-OCT-13 

  21. GX_WEBUSER 27-OCT-13 

  22. GX_ADMIN 27-OCT-13 2640 

  23. GX_WEBUSER 28-OCT-13 

  24. --Author : Leshami

  25. --Blog : http://blog.csdn.net/leshami

  26. --基于日期时间段的用户登陆数

  27. SQL> select trunc (logon_day) logon_day,substr(logon_time,1,2) hour,count(user_id) as number_of_logins 

  28. from sys.stats$user_log 

  29. groupby trunc (logon_day) ,substr(logon_time,1,2) orderby 1,2; 

  30. LOGON_DAY HOUR NUMBER_OF_LOGINS 

  31. --------- ------ ----------------

  32. 24-OCT-13 12 2 

  33. 24-OCT-13 16 3 

  34. 24-OCT-13 20 2 

  35. 24-OCT-13 22 2 

  36. 24-OCT-13 23 1 

  37. 25-OCT-13 00 2 

  38. 25-OCT-13 03 104 

  39. 25-OCT-13 04 2 

  40. 25-OCT-13 06 2 

  41. 25-OCT-13 10 2 

  42. 25-OCT-13 14 2 

  43. ............. 













本文转自东方之子736651CTO博客,原文链接:http://blog.51cto.com/ecloud/1336726 ,如需转载请自行联系原作者



相关文章
|
2月前
|
SQL 存储 Oracle
Oracle系列十四:触发器
Oracle系列十四:触发器
|
2月前
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Dept实体类和对应的配置信息
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Dept实体类和对应的配置信息
|
19天前
|
Oracle 关系型数据库
oracle收集统计信息,游标失效时间
Dbms_stats Invalidates Cursors in Auto_invalidate mode
20 0
|
19天前
|
Oracle 关系型数据库
oracle收集统计信息,游标失效时间
Dbms_stats Invalidates Cursors in Auto_invalidate mode
9 0
|
2月前
|
存储 数据采集 运维
DataWorks产品使用合集之DataWorks创建HTTP触发器节点背景信息的步骤如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
50 0
|
2月前
|
缓存 Oracle 关系型数据库
Oracle中的触发器与序列:自增列的魔法组合
【4月更文挑战第19天】Oracle数据库中,通过触发器和序列的组合可实现自增列功能。序列充当自动计数器,提供递增数值,而触发器则在插入新记录时自动分配序列值。创建序列如`CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE`,然后创建触发器`TRIGGER trg_employees_before_insert`在`BEFORE INSERT`时将序列的下一个值赋予`employee_id`。这种方式使得在插入记录时无需手动设置ID,提高了效率。但使用时需注意序列状态、并发性和性能优化。
|
2月前
|
JavaScript Java 数据库连接
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Emp实体类和对应的配置信息
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Emp实体类和对应的配置信息
|
9月前
|
SQL 存储 Oracle
Oracle触发器----你会教对象触发嘛
Oracle触发器----你会教对象触发嘛
|
10月前
|
SQL 存储 Oracle
Oracle 游标&子程序&触发器
游标的作用:处理多行数据,类似与java中的集合
60 0
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】

推荐镜像

更多