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

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

1、实现代码

[sql] view plain copy print ?

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

2. CREATETABLE stats$user_log 

3. ( 

4. user_id VARCHAR2 (30), 

5. session_id NUMBER (8), 

6. HOST VARCHAR2 (30), 

7. last_program VARCHAR2 (48), 

8. last_action VARCHAR2 (32), 

9. last_module VARCHAR2 (32), 

10. logon_day DATE, 

11. logon_time VARCHAR2 (10), 

12. logoff_day DATE, 

13. logoff_time VARCHAR2 (10), 

14. elapsed_minutes NUMBER (8) 

15. ); 

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

17. CREATEORREPLACETRIGGER logon_audit_trigger 

18. AFTER LOGON 

19. ONDATABASE

20. BEGIN

21. INSERTINTO stats$user_log 

22. VALUES (USER, 

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

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

25. NULL, 

26. NULL, 

27. NULL, 

28. SYSDATE, 

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

30. NULL, 

31. NULL, 

32. NULL); 

33. END; 

34. / 

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

36. CREATEORREPLACETRIGGER logoff_audit_trigger 

37. BEFORE LOGOFF 

38. ONDATABASE

39. BEGIN

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

41. -- Update the last action accessed

42. -- ***************************************************

43. UPDATE stats$user_log 

44. SET last_action = 

45. (SELECTaction

46. FROM v$session 

47. WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid) 

48. WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id; 

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

50. -- Update the last program accessed

51. -- ***************************************************

52. UPDATE stats$user_log 

53. SET last_program = 

54. (SELECT program 

55. FROM v$session 

56. WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid) 

57. WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id; 

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

59. -- Update the last module accessed

60. -- ***************************************************

61. UPDATE stats$user_log 

62. SET last_module = 

63. (SELECT module 

64. FROM v$session 

65. WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid) 

66. WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id; 

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

68. -- Update the logoff day

69. -- ***************************************************

70. UPDATE stats$user_log 

71. SET logoff_day = SYSDATE 

72. WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id; 

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

74. -- Update the logoff time

75. -- ***************************************************

76. UPDATE stats$user_log 

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

78. WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id; 

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

80. -- Compute the elapsed minutes

81. -- ***************************************************

82. UPDATE stats$user_log 

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

84. WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id; 

85. END; 

86. / 

--创建表用于存储登陆或登出的统计信息
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、结果样例

[sql] view plain copy print ?

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. 2 FROM sys.stats$user_log 

10. 3 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. 2 from sys.stats$user_log 

29. 3 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. ............. 

 

转载于:https://blog.51cto.com/ecloud/1336726