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

大家好，这里是 Lucifer三思而后行，专注于提升数据库运维效率。 @TOC目录日常工作生产，我们一般都通过监听连接 Oracle 数据库。如果想要记录访问过数据库的用户 IP 地址，常规方式是无法做到的，但是可以通过一些非常规方式来实现。这里提供几种方式： - 通过触发器实现 - 查看监听日志 - 通过 PLSQL 包 DBMSSESSION 创建单独表空间存放记录： sql create

2021年7月14日阅读—点赞—收藏—

oraclerac

大家好，这里是 Lucifer三思而后行，专注于提升数据库运维效率。

前言

本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟

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

一、介绍

这里提供几种方式：

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

二、实战演示

1、触发器实现

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

SQL

1create tablespace test datafile;

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

SQL

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

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

SQL

1CREATE or replace trigger on_logon_trigger after logon
2ON database begin
3INSERT INTO session_history
4SELECT  sid
5       ,username
6       ,program
7       ,machine
8       ,sys_context('userenv','ip_address')
9       ,sysdate
10FROM v$session
11WHERE audsid = userenv('sessionid'); end;
12/

本机通过 lucifer 用户登录：

Bash

1sqlplus lucifer/lucifer@10.211.55.110/orcl

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

SQL

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

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

2、查看监听日志

查看监听日志位置：

Bash

1su - oracle
2lsnrctl status

查看监听日志：

Bash

1tail -100 log.xml

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

3、PLSQL包 DBMS_SESSION

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

SQL

1drop trigger on_logon_trigger;
2drop tablespace test;

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

Bash

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

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

SQL

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

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

Bash

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

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

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

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

SQL

1create or replace trigger on_logon_trigger
2  after logon on database
3begin
4  dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
5end;
6/

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

SQL

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

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

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

往期精彩文章

Oracle 一键巡检自动生成 Word 报告
 Oracle 一键安装合集
 Oracle一键安装脚本的 21 个疑问与解答
 Oracle一键巡检脚本的 21 个疑问与解答
 全网首发：Oracle 23ai 一键安装脚本（非 RPM）
Oracle 19C 最新 RU 补丁 19.24 ，一键安装！
Oracle Linux 7.9 一键安装 Oracle 19C
RedHat 9.4(aarch64) 一键安装 Oracle 19C
openEuler 22.03 LTS SP4 一键安装 Oracle 19C RAC
RHEL 7.9 一键安装 Oracle 19C 19.23 RAC
Oracle DataGuard GAP 修复手册
 优化 Oracle：最佳实践与开发规范
 DBA 必备：Linux 软件源配置全攻略
 Linux 一键配置时钟同步全攻略

感谢您的阅读，这里是 Lucifer三思而后行，欢迎点赞+关注，我会持续分享数据库知识、运维技巧。