-- 记录登录数据库的用户IP等信息
conn sys/ create table session_history
(
sid number(10),
spid number(10),
audsid number(10),
osuser varchar2(20),
username varchar2(20),
ip varchar2(20),
logon_time date,
logoff_time date
)
/
CREATE OR REPLACE TRIGGER logon_history
AFTER LOGON ON database
declare
client_ip varchar2(20);
BEGIN
IF userenv('sessionid')!=0 THEN
select SYS_CONTEXT('USERENV','IP_ADDRESS') into client_ip from dual;
if client_ip = '' then
insert into session_history(sid,spid,audsid,osuser,username,logon_time,ip)
select s.sid,p.spid,s.audsid,s.osusr,s.username,SYSDATE,utl_inaddr.get_host_address from v$session
where audsid = userenv('sessionid') ;
else
insert into session_history(sid,spid,audsid,osuser,username,logon_time,ip)
select s.sid,p.spid,s.audsid,s.osusr,s.username,SYSDATE,SYS_CONTEXT('USERENV','IP_ADDRESS') from v$session
where audsid = userenv('sessionid') ;
end if;
EXCEPTION
WHEN OTHERS THEN
dbms_output.putline('error!');END IF;
commit;
END;
/
网络连接的用户可以获得ip,本地登录的却得不到服务端ip地址,获取服务器端ip的sql语句没有问题,好像那个if语句从来没有成功过,请教给位高手指点一下!
多谢多谢!
conn sys/ create table session_history
(
sid number(10),
spid number(10),
audsid number(10),
osuser varchar2(20),
username varchar2(20),
ip varchar2(20),
logon_time date,
logoff_time date
)
/
CREATE OR REPLACE TRIGGER logon_history
AFTER LOGON ON database
declare
client_ip varchar2(20);
BEGIN
IF userenv('sessionid')!=0 THEN
select SYS_CONTEXT('USERENV','IP_ADDRESS') into client_ip from dual;
if client_ip = '' then
insert into session_history(sid,spid,audsid,osuser,username,logon_time,ip)
select s.sid,p.spid,s.audsid,s.osusr,s.username,SYSDATE,utl_inaddr.get_host_address from v$session
where audsid = userenv('sessionid') ;
else
insert into session_history(sid,spid,audsid,osuser,username,logon_time,ip)
select s.sid,p.spid,s.audsid,s.osusr,s.username,SYSDATE,SYS_CONTEXT('USERENV','IP_ADDRESS') from v$session
where audsid = userenv('sessionid') ;
end if;
EXCEPTION
WHEN OTHERS THEN
dbms_output.putline('error!');END IF;
commit;
END;
/
网络连接的用户可以获得ip,本地登录的却得不到服务端ip地址,获取服务器端ip的sql语句没有问题,好像那个if语句从来没有成功过,请教给位高手指点一下!
多谢多谢!
少个别名s