using audit in oracle database 11g

由于要模拟系统操作，从数据库层面直接插入数据，于是跟踪应用系统的某个操作会在数据库层面产生那些dml语句。

 

1、一开始使用的是sql_trace ，发现sql trace 只跟踪select语句，由于要往db里面插入数据，因此就必须查询出所有的dml操作，一下是sql trace的具体操作

 

SQL> select sid,serial#,username ,osuser,machine,status from v$session where username='BBC_PUB' order by osuser;

 

    SID    SERIAL# USERNAME        OSUSER       MACHINE            STATUS
---------- ---------- ------------------------------ ------------------------------ ---------------------------------------------------------------- --------

    1752     2 BBC_PUB        weblogic       abc            INACTIVE
       615    16 BBC_PUB        weblogic      abc            INACTIVE

>在session层面启用sql trace

exec dbms_system.set_sql_trace_in_session(615,16,true)
exec dbms_system.set_sql_trace_in_session(1752,2,true)

>查找该session所对应的操作系统进程，该spid就是生成的trace文件的后缀

select s.sid,s.serial#,s.username,p.spid   
from v$session s,v$process p
where s.paddr=p.ADDR
and s.username='BBC_PUB' order by s.osuser;

>使用oracle自带工具tkprof进行分析

/oracle/app/product/11.2.0/db_1/bin/tkprof /oracle/app/diag/rdbms/atgbbc/atgbbc/trace/atgbbc_ora_11237.trc
output = 3.txt

这样就可以使用文本编辑器来进行分析

>在session层面禁用sql trace

exec dbms_system.set_sql_trace_in_session(615,16,false)
exec dbms_system.set_sql_trace_in_session(1752,2,false)

 

2、在朋友的建议下，果断的放弃sql trace，于是使用oracle 审计功能，发现审计功能果然强大，在数据库层面的每个操作都能够记录下来，以下是具体的操作过程：

SQL> ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;

>重启db

SQL> show parameter audit

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest        string  /oracle/app/admin/atgbbc/adump
audit_sys_operations       boolean  FALSE
audit_syslog_level       string
audit_trail        string  DB, EXTENDED
SQL>

>对用户启用审计

AUDIT ALL BY bbc_pub BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY bbc_pub BY ACCESS;
AUDIT EXECUTE PROCEDURE BY bbc_pub BY ACCESS;

>在应用层面执行某操作后执行如下sql进行查询

 

set linesize 500
COLUMN username FORMAT A10
COLUMN owner    FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
col sql_bind for a20
col sql_text for a40
col os_username for a10

SELECT OS_USERNAME,
    username,
       extended_timestamp,
       owner,
       obj_name,
       action_name,
    SQL_BIND,
    SQL_TEXT
FROM   dba_audit_trail
WHERE  owner = 'BBC_PUB'
and timestamp>to_date('20121121162200','yyyymmddhh24:mi:ss')
and action_name in ('INSERT','UPDATE','DELETE') and os_username='weblogic'
and obj_name not like 'DMS_%' and  obj_name not like '%D2%'
ORDER BY timestamp;

 

>禁用审计

 

noAUDIT ALL BY bbc_pub ;
noAUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY bbc_pub ;
noAUDIT EXECUTE PROCEDURE BY bbc_pub ;