Oracle 脚本实现简单的审计功能

原创布衣&凡尘 2022-08-03

3144

前景

为了数据安全，生产环境开数据库审计的话会有很大的消耗，一般不建议开启。于是根据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 <<EOF
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 <<EOF
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