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

作者简介

• 作者&＃xff1a;LuciferLiu&＃xff0c;中国DBA联盟(ACDU)成员。
• 目前主要从事Oracle DBA工作&＃xff0c;曾从事 Oracle 数据库开发工作&＃xff0c;主要服务于生产制造&＃xff0c;汽车金融等行业。
• 现拥有Oracle OCP&＃xff0c;OceanBase OBCA认证&＃xff0c;擅长Oracle数据库运维开发&＃xff0c;备份恢复&＃xff0c;安装迁移&＃xff0c;Linux自动化运维脚本编写等。

前言

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

实现

这里提供几种方式&＃xff1a;

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

1 触发器实现

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

sqlplus / as sysdba
create tablespace test datafile;


• 通过ctas从v$session创建session历史记录表指定表空间test

sqlplus / as sysdba
create table session_history tablespace test as (select sid,username,program,machine,&＃39;000.000.000.000&＃39;ipadd,sysdate moditime from v$session where 0&＃61;1);


• 创建触发器&＃xff0c;当有用户登录时&＃xff0c;将记录插入session历史记录表

sqlplus / as sysdba
CREATE or replace trigger on_logon_trigger after logon
ON database begin
INSERT INTO session_history
SELECT sid,username,program,machine,sys_context(&＃39;userenv&＃39;,&＃39;ip_address&＃39;),sysdate
FROM v$session
WHERE audsid &＃61; userenv(&＃39;sessionid&＃39;); end;
/


• 本机通过lucifer用户登录

sqlplus lucifer/lucifer&＃64;10.211.55.110/orcl


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

sqlplus / as sysdba
alter session set nls_date_format &＃61; &＃39;yyyy-mm-dd hh24:mi:ss&＃39;;
select * from session_history q where q.username not in (&＃39;SYS&＃39;);


至此&＃xff0c;第一种方式已经介绍完毕。可以看到&＃xff0c;已经可以记录到登录数据库的用户IP地址。

2 查看监听日志

• 查看监听日志位置

su - oracle
lsnrctl status


• 查看监听日志

tail -100 log.xml


这种方式也是可以实现查看登录IP&＃xff0c;但是查询起来过于麻烦&＃xff0c;不推荐使用。

3 PLSQL包 DBMS_SESSION

• 为方便后面测试&＃xff0c;先删除第一种方式创建的 触发器和表空间

sqlplus / as sysdba
drop trigger on_logon_trigger;
drop tablespace test;


• 测试是否还能看到IP

sqlplus lucifer/lucifer&＃64;10.211.55.110/orcl
sqlplus / as sysdba
alter session set nls_date_format &＃61; &＃39;yyyy-mm-dd hh24:mi:ss&＃39;;
select username,machine,terminal,program,client_info,logon_time from v$session;


从上图的 client_info字段为空&＃xff0c;可以看出v$session视图并没有记录到IP。
​

• 使用DBMS_SESSION程序包设置可以查询IP地址

sqlplus / as sysdba
exec DBMS_SESSION.set_identifier(SYS_CONTEXT(&＃39;USERENV&＃39;, &＃39;IP_ADDRESS&＃39;));
/


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

sqlplus lucifer/lucifer&＃64;10.211.55.110/orcl
alter session set nls_date_format &＃61; &＃39;yyyy-mm-dd hh24:mi:ss&＃39;;
select sys_context(&＃39;userenv&＃39;,&＃39;ip_address&＃39;) from dual;


• 换一个主机客户端登录&＃xff0c;查看是否可以查询IP地址
  

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

• 创建触发器&＃xff0c;记录客户端登录IP

sqlplus / as sysdba
create or replace trigger on_logon_triggerafter logon on database
begindbms_application_info.set_client_info(sys_context(&＃39;userenv&＃39;,&＃39;ip_address&＃39;));
end;
/


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

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


可以看到&＃xff0c;IP地址已经被记录了。

通过以上几种方式&＃xff0c;我们可以跟踪记录到登录用户的IP地址。至于有什么作用&＃xff0c;自己脑补吧。

本次分享到此结束啦~

如果觉得文章对你有帮助&＃xff0c;点赞、收藏、关注、评论&＃xff0c;一键四连支持&＃xff0c;你的支持就是我创作最大的动力。