檢視連線端IP

綜合網上各類資料整理而成；

檢視單籤連線到資料庫上的客戶端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;