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

Lucifer三思而后行

发布于 2021-08-17 16:11:31

1.2K0

文章被收录于专栏：公众号：Lucifer三思而后行公众号：Lucifer三思而后行

作者简介

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

前言

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

实现

这里提供几种方式：

通过触发器实现
查看监听日志
通过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,'000.000.000.000'ipadd,sysdate moditime from v$session where 0=1);

创建触发器，当有用户登录时，将记录插入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('userenv','ip_address')
       ,sysdate
FROM v$session
WHERE audsid = userenv('sessionid'); end;
/

本机通过lucifer用户登录

sqlplus lucifer/lucifer@10.211.55.110/orcl

查询非SYS用户的登录记录

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

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

2 查看监听日志

查看监听日志位置

su - oracle
lsnrctl status

查看监听日志

tail -100 log.xml

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

3 PLSQL包 DBMS_SESSION

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

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

测试是否还能看到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;

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

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

sqlplus / as sysdba
exec DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
/

主机测试用户登录是否能查看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;

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

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

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

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

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

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

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

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

本次分享到此结束啦~

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

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021/07/15 ，如有侵权请联系 cloudcommunity@tencent.com 删除

tcp/ip

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

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

作者简介

前言

实现

1 触发器实现

2 查看监听日志

3 PLSQL包 DBMS_SESSION

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐