简单审计oracle用户ip

 1 CREATE TABLE SYS.USER_LOGIN_CHECK
 2 (
 3 USERNAME VARCHAR2(40 BYTE),
 4 IPADDR VARCHAR2(30 BYTE),
 5 LAST_LOGIN_TIME TIMESTAMP(6)
 6 );
 7 
 8 
 9 
10 DROP TRIGGER SYS.TEST;
11 
12 CREATE OR REPLACE TRIGGER SYS.TEST
13 AFTER logon ON database
14 DECLARE
15 v_username sys.user_login_check.USERNAME%type;
16 v_ipaddr sys.user_login_check.IPADDR%type;
17 v_count number;
18 BEGIN
19 if sys_context('userenv', 'ip_address') is null then
20 select count(1) into v_count from sys.user_login_check where username=user and IPADDR is null;
21 else
22 select count(1) into v_count from sys.user_login_check where username=user and IPADDR=sys_context('userenv', 'ip_address');
23 end if;
24 
25 if v_count > 0 then
26 update sys.user_login_check set LAST_LOGIN_TIME=sysdate where username = user and IPADDR=sys_context('userenv', 'ip_address');
27 else
28 insert into sys.user_login_check values(user,sys_context('userenv', 'ip_address'),sysdate);
29 end if;
30 commit;
31 
32 END test;
33 /
posted on 2016-09-08 15:57 zhaobin022 阅读(491) 评论(0) 收藏举报