ORACLE用户审计功能

纵横四海

1. 
   
2. 
   
3. create table login_log(
   
4.     session_id int not null,
   
5.     login_on_time date,
   
6.     login_off_time date,
   
7.     user_in_db varchar2(30),
   
8.     machine varchar2(20),
   
9.     ip_address varchar2(20),
   
10.     run_program varchar2(20)
    
11. );
    
12. 
    
13. create table allow_user(
    
14.     ip_address  varchar2(20),
    
15.     login_user_name  nvarchar2(20)
    
16. );
    
17. 
    
18. create or replace trigger login_on_info
    
19. after logon on database
    
20. Begin
    
21.     insert into login_log(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program)
    
22.     select AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program
    
23.     from v$session where AUDSID=USERENV('SESSIONID');
    
24. END;
    
25. 
    
26. create or replace trigger login_off_info
    
27. before logoff on database
    
28. Begin
    
29.     update login_log set login_off_time=sysdate where session_id=USERENV('SESSIONID');
    
30.     exception when others then
    
31.         null;
    
32. END;
    
33. /
    
34. 
    

复制代码

纵横四海

1. 
   
2.      Auditing LOGOFF and LOGON
   
3. 
   
4.       　　 This tip comes from Milind Deobhankar, Oracle Software Engineer in
   
5.       Ahmedabad, Gujarat, India.
   
6.       Auditing the database to find out who is logged on, who has logged off,
   
7.       from which machines users are logged in, and from which program users are
   
8.       connected can be a very cumbersome task. Here is a procedure that will
   
9.       help the DBA to trace who has logged on, logged off, and at what times.
   
10.       First log on as the sys user (as this task is of the database
    
11.       administrator).
    
12.       Create the table in which all information will be stored.
    
13.       create table
    
14.       SYSTEM.login_info
    
15.       ( logindatetime date,
    
16.       LogoffDateTime Date,
    
17.       LoggedUser Varchar2(30),
    
18.       SESSIONID Number,
    
19.       TERMINAL varchar2(20),
    
20.       PROGRAM varchar2(20)
    
21.       );
    
22.       /
    
23.       Now create the trigger on the logon event so that whenever anybody logs
    
24.       on, the trigger will fire.
    
25.       CREATE OR REPLACE TRIGGER logon_audit AFTER LOGON ON DATABASE
    
26.       BEGIN
    
27.       insert into system.login_info
    
28.       values(sysdate,null,user,sys_context('USERENV','SESSIONID'),sys_context(
    
29.       'USERENV','TERMINAL'),null);
    
30.       END;
    
31.       /
    
32.       Now create the trigger on the logoff event so that whenever anybody logs
    
33.       off, the trigger will fire.
    
34.       CREATE OR REPLACE TRIGGER logoff_audit Before LOGOFF ON DATABASE
    
35.       BEGIN
    
36.       insert into system.login_info
    
37.       select
    
38.       '',sysdate,user,sys_context('USERENV','SESSIONID'),sys_context('USERENV'
    
39.       ,'TERMINAL'), program from v$session where logon_time = (select
    
40.       logindatetime from SYSTEM.login_info where SESSIONID=
    
41.       sys_context('USERENV','SESSIONID'));
    
42.       END;
    
43.       /
    
44.       Shutdown the database, restart it, and log on as SYS. There will be values
    
45.       inserted in the table. Now retrieve the value from the table.
    
46.       select to_char(logindatetime,'DD-MON-YYYY
    
47.       hh:mi:ss')LOGIN,to_char(LogoffDateTime,'DD-MON-YYYY hh:mi:ss')LOGOFF,
    
48.       SESSIONID,
    
49.       TERMINAL,
    
50.       PROGRAM
    
51.       from SYSTEM.login_info
    
52.       group by SESSIONID,logindatetime,LogoffDateTime,TERMINAL,PROGRAM;
    
53.       /
    
54.       Alternatively, you can also develop a PL/SQL procedure to do the same
    
55.       thing.
    
56.       create or replace procedure traceusers
    
57.       as
    
58.       CURSOR BROWSER IS
    
59.       select to_char(logindatetime,'DD-MON-YYYY
    
60.       hh:mi:ss')LOGIN,to_char(LogoffDateTime,'DD-MON-YYYY hh:mi:ss')LOGOFF,
    
61.       SESSIONID,
    
62.       TERMINAL,
    
63.       PROGRAM
    
64.       from SYSTEM.login_info
    
65.       group by SESSIONID,logindatetime,LogoffDateTime,TERMINAL,PROGRAM;
    
66.       BROWSEIT BROWSER%ROWTYPE;
    
67.       begin
    
68.       OPEN BROWSER;
    
69.       LOOP
    
70.       FETCH BROWSER INTO BROWSEIT;
    
71.       EXIT WHEN BROWSER%NOTFOUND;
    
72.       dbms_output.put_line('Logged In: '||BROWSEIT.LOGIN ||' Logged Off: '||
    
73.       BROWSEIT.LOGOFF || ' SessionID: '||BROWSEIT.SESSIONID||'
    
74.       Terminal:'||BROWSEIT.TERMINAL||' Program: '||BROWSEIT.PROGRAM);
    
75.       END LOOP;
    
76.       CLOSE BROWSER;
    
77.       end;
    
78.       /
    
79. 
    
80. 
    

复制代码

smilefish

这个怎么用啊,老大

smilefish

不好意思,起先没仔细看,现在明白了!
谢谢

tomdoctor

这是数据库方面的审计 如何实现ERP方面的审计

wocn

34