使用触发器记录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 ,如需转载请自行联系原作者



上一篇:防火墙内JVisualVM连接jstatd解决方案


下一篇:duilib中的V和H布局中滚动条问题