oracle 脚本实现简单的审计功能 -m6米乐安卓版下载

前景

为了数据安全，生产环境开数据库审计的话会有很大的消耗，一般不建议开启。于是根据dba_hist_active_sess_history写了个shell 脚本，每天产生一个简单的审计报告。

审计脚本：audit_db.sh

#!/bin/sh  
export oracle_sid=two
export oracle_unqname=two
export oracle_base=/u01/app/oracle
export oracle_home=/u01/app/oracle/11.2.0.3/product
export path=$oracle_home/bin:$path
export ld_library_path=$oracle_home/lib:$ld_library_path
export nls_lang=american_america.al32utf8
export con_user='sqlplus -s / as dba'
export au_dir='/u01/report/audit_db'
audit_date=`date  %y-%m-%d -d "1 day ago"`
if [ ! -d ${au_dir}/${audit_date} ];then
        mkdir -p ${au_dir}/${audit_date}
fi
result=`$con_user <set colsep','; 
set echo off;      
set feedback off;  
set heading off;   
set pagesize 0;    
set linesize 1000;   
set numwidth 12;   
set termout off;   
set timing off;    
set trimout on;    
set trimspool on;  
set trims on; 
col username format a15
col machine format a30
col program format a50
col sql_opname format a20
spool ${au_dir}/${audit_date}/${audit_date}.txt 
select to_char(h.sample_time, 'yyyy-mm-dd hh24:mi:ss') exec_time,
           u.username,
           h.machine,
           h.program,
           h.sql_opname
      from sys.dba_hist_active_sess_history h
      left join sys.all_users u
        on u.user_id = h.user_id
     where h.module is not null
       and h.sql_opname is not null
       and to_char(h.sample_time, 'yyyy-mm-dd') = '$audit_date';
spool off
eof`
result_report=`$con_user <set colsep',';          
set echo off;      
set feedback off;  
set heading off;   
set pagesize 0;    
set linesize 800;   
set numwidth 12;   
set termout off;   
set timing off;    
set trimout on;    
set trimspool on;  
set trims on; 
col username format a15
col machine format a30
col program format a50
col sql_opname format a20
spool ${au_dir}/${audit_date}/${audit_date}.report 
select u.username, h.machine, h.program, h.sql_opname, count(*)
  from sys.dba_hist_active_sess_history h
  left join sys.all_users u
    on u.user_id = h.user_id
 where h.module is not null
   and h.sql_opname is not null
   and to_char(h.sample_time, 'yyyy-mm-dd') = '$audit_date'
 group by username, machine, program, h.sql_opname
 order by 5 desc;
spool off
eof`

输出日志：

[oracle@localhost 2022-08-02]$ cat 2022-08-02.report
业务用户 主机名                 连接方式                操作类型     操作次数
two    ,app.tomcat.com        ,jdbc thin client        ,select,        8610
two    ,app1.tomcat.com        ,jdbc thin client       ,insert,        8610

                         文章推荐