Oracle获取session登录IP

来源：互联网发布：耶鲁大学生活知乎编辑：程序博客网时间：2024/04/29 22:05

本文转自：http://www.zhongweicheng.com/?p=1786

使用sys创建一个表，用于记录客户端登录数据库服务器的信息

[oracle@vzwc1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 28 17:53:35 2015

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create table login_history
2 (username varchar2(60),
3 machine varchar2(60),
4 module varchar2(60),
5 login_time date,
6 ip varchar2(50)
7 );

Table created.

使用sys创建获取session登录信息的触发器

SQL> create or replace trigger login_log
2 after logon on database
3 begin
4 insert into sys.login_history
5 select username,machine,module,sysdate,sys_context('userenv','ip_address')
6 from gv$session where audsid = userenv('sessionid');
7 end;
8 /

Trigger created.

用客户端登录oracle，然后查询sys.login_history

SQL> select * from sys.login_history where ip is not null;

USERNAME MACHINE MODULE LOGIN_TIME IP
——– —————— —————————- ———— ——————
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC sqlplus.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC sqlplus.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC sqlplus.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC sqlplus.exe 28-MAR-15 192.168.1.100
SYSTEM vzwc3 sqlplus@vzwc3 (TNS V1-V3) 28-MAR-15 192.168.1.53
SYSTEM vzwc3 sqlplus@vzwc3 (TNS V1-V3) 28-MAR-15 192.168.1.53
SYSTEM vzwc3 sqlplus@vzwc3 (TNS V1-V3) 28-MAR-15 192.168.1.53
SYSTEM vzwc3 sqlplus@vzwc3 (TNS V1-V3) 28-MAR-15 192.168.1.53

0 0