实战篇：Oracle 巧记登录用户 IP，无所遁形

前言

日常工作生产，我们一般都通过监听连接 Oracle 数据库。如果想要记录访问过数据库的用户 IP 地址，常规方式是无法做到的，但是可以通过一些非常规方式来实现。

一、介绍

这里提供几种方式：

• 通过触发器实现
• 查看监听日志
• 通过 PLSQL 包 DBMS_SESSION

二、实战演示

1、触发器实现

创建单独表空间存放记录：

create tablespace test datafile;

AI写代码sql

* 1

通过 ctas 复制 v$session 表结构，用来存放 session 历史记录：

create table session_history tablespace test as (select sid,username,program,machine,'000.000.000.000'ipadd,sysdate moditime from v$session where 0=1);

AI写代码sql

* 1

创建触发器 on_logon_trigger，当有用户登录时，将记录插入 session 历史记录表：

CREATE or replace trigger on_logon_trigger after logon
ON database begin
INSERT INTO session_history
SELECT  sid
       ,username
       ,program
       ,machine
       ,sys_context('userenv','ip_address')
       ,sysdate
FROM v$session
WHERE audsid = userenv('sessionid'); end;
/

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12

本机通过 lucifer 用户登录：

sqlplus lucifer/lucifer@10.211.55.110/orcl

AI写代码bash

* 1

查询 非 SYS 用户的登录记录：

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select * from session_history q where q.username not in ('SYS');

AI写代码sql

* 1
* 2

至此，已经可以记录到登录数据库的用户 IP 地址，第一种方式已经介绍完毕！

2、查看监听日志

查看监听日志位置：

su - oracle
lsnrctl status

AI写代码bash

* 1
* 2

查看监听日志：

tail -100 log.xml

AI写代码bash

* 1

这种方式也是可以实现查看登录IP，但是查询起来可能有些麻烦。

3、PLSQL包 DBMS_SESSION

为方便后面测试，先删除第一种方式创建的触发器和表空间：

drop trigger on_logon_trigger;
drop tablespace test;

AI写代码sql

* 1
* 2

测试是否还能看到 IP 地址：

sqlplus lucifer/lucifer@10.211.55.110/orcl
sqlplus / as sysdba
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select username,machine,terminal,program,client_info,logon_time from v$session;

AI写代码bash

* 1
* 2
* 3
* 4

从上图的 client_info 字段为空，可以看出 v$session 视图并没有记录到 IP。
​
使用 DBMS_SESSION 程序包设置可以查询
IP
地址：

exec DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));

AI写代码sql

* 1

主机测试用户登录是否能查看 IP 地址：

sqlplus lucifer/lucifer@10.211.55.110/orcl
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select sys_context('userenv','ip_address') from dual; 

AI写代码bash

* 1
* 2
* 3

换一个主机客户端登录，查看是否可以查询 IP 地址：

从上述实验可以看出，客户端已经可以查询 IP 地址，说明 plsql 包已生效，但是不会记录到 v$session 中，需要创建一个触发器来实现。

创建触发器，记录客户端登录 IP：

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

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6

查询 v$session 查看是否有记录 IP 地址：

select username,machine,terminal,program,client_info,logon_time from v$session where username is not null;

AI写代码sql

* 1

可以看到，IP 地址已经被记录了。

通过以上几种方式，我们可以跟踪记录到登录用户的IP地址。