ITPub博客

首页 > 数据库 > Oracle > 查看连接端IP

查看连接端IP

Oracle 作者:studywell 时间:2016-03-16 16:24:08 0 删除 编辑

综合网上各类资料整理而成;

查看单签连接到数据库上的客户端IP;
1、数据库系统上执行
linux下查看
netstat |grep 1521

windows下查看
netstat -na | find "1521"


2. 利用 DBMS_SESSION 过程包.
在当前会话下执行语句,给在v$session中显示当前会话的ip信息;
exec DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
select s.CLIENT_IDENTIFIER,s.* from v$session s;

显示单签会话的连接情况:
select AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program from v$session where AUDSID=USERENV('SESSIONID');


3.通过触发器。

create or replace trigger on_logon_trigger
after logon on database
begin
    dbms_application_info.set_client_info(sys_context( 'userenv', 'ip_address' ) );
end;

这样当客户端登陆后,在v$session的client_info列会记录其相应的IP信息。
select client_info from v$session;


4.通过触发器,将登陆记录到表里面;

Step 00 Check PUBLIC SYSTEM VIEW PRIVILEGE

SELECT * FROM DBA_TAB_PRIVS T
WHERE T.grantee='PUBLIC'
AND T.table_name LIKE 'V_$%'

Step 01 grant select privilege as sys user login:
        grant select on v_$session to public;
        grant select on v_$mystat to public;

Step 02 create login$information table:

create table LOGIN$INFORMATION
(
  LOGIN_USER VARCHAR2(30),
  LOGIN_TIME DATE,
  IP_ADRESS  VARCHAR2(256),
  AUSID      NUMBER,
  TERMINAL   VARCHAR2(16),
  OSUSER     VARCHAR2(30),
  MACHINE    VARCHAR2(64),
  PROGRAM    VARCHAR2(64),
  SID        NUMBER,
  SERIAL#    NUMBER
)

Step 03 create trigger on database level:

CREATE OR REPLACE TRIGGER tr_login_record
  AFTER logon ON DATABASE
DECLARE
  miUserSid NUMBER;
  mtSession v$session%ROWTYPE;
  CURSOR cSession(iiUserSid IN NUMBER) IS
    SELECT * FROM v$session WHERE sid = iiUserSid;
BEGIN
  SELECT sid INTO miUserSid FROM v$mystat WHERE rownum <= 1;
  OPEN cSession(miUserSid);
  FETCH cSession
    INTO mtSession;
  --if user exists then insert data
  IF cSession%FOUND THEN
    insert into login$information
      (login_user,
       login_time,
       ip_adress,
       ausid,
       terminal,
       osuser,
       machine,
       program,
       sid,
       serial#)
    VALUES
      (ora_login_user,
       SYSDATE,
       SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
       userenv('SESSIONID'),
       mtSession.Terminal,
       mtSession.Osuser,
       mtSession.Machine,
       mtSession.Program,
       mtSession.Sid,
       mtSession.Serial#);
  ELSE
    null;
    CLOSE cSession;
    raise_application_error(-20099, 'Login Exception', FALSE);
  END IF;
  CLOSE cSession;
EXCEPTION
  WHEN OTHERS THEN
    null;
END tr_login_record;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29519108/viewspace-2058279/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-03-04

  • 博文量
    305
  • 访问量
    428502