本文主要是介绍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 ;
这篇关于using audit in oracle database 11g的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
