在知识库中专注阅读，并随时返回相关工具与课程
大家好，这里是 Lucifer三思而后行，专注于提升数据库运维效率。 @TOC目录 日常工作生产，我们一般都通过监听连接 Oracle 数据库。如果想要记录访问过数据库的用户 IP 地址，常规方式是无法做到的，但是可以通过一些非常规方式来实现。 这里提供几种方式： - 通过触发器实现 - 查看监听日志 - 通过 PLSQL 包 DBMSSESSION 创建单独表空间存放记录： sql create
系统学习 Oracle DBA
开通会员 ¥128/月起
查看完整课程大家好，这里是 Lucifer三思而后行，专注于提升数据库运维效率。
本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟
日常工作生产，我们一般都通过监听连接 Oracle 数据库。如果想要记录访问过数据库的用户 IP 地址，常规方式是无法做到的，但是可以通过一些非常规方式来实现。
这里提供几种方式：
DBMS_SESSION创建单独表空间存放记录：
1create tablespace test datafile;
通过 ctas 复制 v$session 表结构，用来存放 session 历史记录：
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 历史记录表：
1CREATE or replace trigger on_logon_trigger after logon2ON database begin3INSERT INTO session_history4SELECT sid5 ,username6 ,program7 ,machine8 ,sys_context('userenv','ip_address')9 ,sysdate10FROM v$session11WHERE audsid = userenv('sessionid'); end;12/
本机通过 lucifer 用户登录：
1sqlplus lucifer/lucifer@10.211.55.110/orcl
查询 非 SYS 用户的登录记录：
1alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';2select * from session_history q where q.username not in ('SYS');
至此，已经可以记录到登录数据库的用户 IP 地址，第一种方式已经介绍完毕！
查看监听日志位置：
1su - oracle2lsnrctl status
查看监听日志：
1tail -100 log.xml
这种方式也是可以实现查看登录IP，但是查询起来可能有些麻烦。
为方便后面测试，先删除第一种方式创建的触发器和表空间：
1drop trigger on_logon_trigger;2drop tablespace test;
测试是否还能看到 IP 地址：
1sqlplus lucifer/lucifer@10.211.55.110/orcl2sqlplus / as sysdba3alter 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 地址：
1exec DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
主机测试用户登录是否能查看 IP 地址：
1sqlplus lucifer/lucifer@10.211.55.110/orcl2alter 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：
1create or replace trigger on_logon_trigger2 after logon on database3begin4 dbms_application_info.set_client_info(sys_context('userenv','ip_address'));5end;6/
查询 v$session 查看是否有记录 IP 地址：
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三思而后行，欢迎点赞+关注，我会持续分享数据库知识、运维技巧。