当前位置 博文首页 > LuciferLiu_DBA：实战篇：Oracle巧记登录用户IP，无所遁形

最大化 缩小

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

    作者：[db:作者] 时间：2021-08-17 18:42

    作者简介

    • 作者：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地址。至于有什么作用，自己脑补吧。

    本次分享到此结束啦~

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

    cs
    上一篇：LuciferLiu_DBA：【Oracle小技巧】手把手教你玩转SQL*Plus命令行
    下一篇：没有了