Oracle保存连接IP（添加定时删除过期数据）

数据库访问记录与安全审计

原创已于 2022-05-20 17:11:03 修改 · 2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle #tcp/ip

于 2021-12-02 17:07:53 首次发布

本文介绍了如何通过创建触发器和存储过程来记录数据库登录历史，包括用户IP、登录时间和程序信息。为确保安全，建议将不常访问的IP从白名单中移除。当数据量过大时，可以通过定时任务删除7天前的记录，以保持数据库性能。

把登录数据库的IP保存起码，主要是可以查看数据库全时段什么时候被谁访问，从而在安全组（白名单）上添加相关 IP，建议用完后，把触发器 login_log 禁用或删除即可，以免表的数据越来越多，导致空间不足。

--查询当前session连接用户

select sid,serial#,username,program,machine,client_info  
from v$session order by client_info

--在某个用户abc下创建登录历史表（也可在SYS下建表）

create table login_history  
(  
  username   varchar2(60),  --用户名   
  machine    varchar2(60),  --机器名   
  login_time date,          --登录时间   
  ip         varchar2(50),  --ip地址  
  program    varchar2(60)   --程序名
);

--需要登录SYSDBA账号运行（如果在SYS下建表的话，下面【abc.】可以去掉

create or replace trigger login_log  
  after logon on database  
begin  
  insert into abc.login_history  
    select username, machine, sysdate, sys_context('userenv', 'ip_address') ,program
      from v$session  
     where audsid = userenv('sessionid');  
  commit;  
end;

--创建查询session表时显示IP

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

--查询历史连接IP

select distinct IP from login_history order by IP;

数据库数据太多，查询速度慢，数据库之前的数据不需要保留，可以进行删除，
思路：设置定时删除，写个存储过程，定时任务定时执行
操作步骤：
1、存储过程（删除 7 天之前的数据）

create or replace PROCEDURE DELETE_login_history  AS
BEGIN
 delete from login_history where login_time <= sysdate-7;
Commit;
END DELETE_login_history ;

2、oracle 定时执行（设置凌晨2点执行）

declare
jobid number;
begin
dbms_job.submit(jobid,'DELETE_login_history;',TRUNC(sysdate+1)+2/24,'TRUNC(sysdate+1)+2/24');
commit;
dbms_output.put_line(jobid);
end;