ORACLE audit

类型：
1. mondatory audit：针对sysdba，sysoper权限用户；
2. standard audit：标准级别，object级，11g默认开启
3. FGA：精细监控，行级监控
4. DBA：针对DBA操作的监控
开启standard audit：需要修改系统参数并重启数据库
audit_file_dest：在文件系统存放audit记录文件；
audit_trail：
none: close audit
os：开启audit，将记录放置在OS的audit_file_dest中.
db：开启audit，将记录放置在database的sys.aud$中.
db,extended：开启audit，将记录放置在database的sys.aud$中.并且记录执行的具体语句.
xml：开启audit，将记录以xml文件的形式放置在OS的audit_file_dest中.
xml,extended：开启audit，将记录以xml文件的形式放置在OS的audit_file_dest中.并且记录执行的具体语句
设置监控内容：
1. 设置system privilege：针对整个系统；
其中"create all table"针对在全部的schema；"create table"针对自己的schema
2. 设置object privilege：针对object
3. 设置statement：针对DDL
选项：by session(基于session) / by access(基于访问)
查看方式：
视图：select * from dba_audit_trail --> 表：select * from sys.aud$
视图：select * from dba_fga_audit_trail --> 表：select * from sys.fga_log$
If you just want to check the record change of one table (Can be used to find out the truncate and drop action)
select * from dba_tab_modifications
Example:

select * from dba_tab_modifications  where timestamp between to_date('2014-04-10 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2014-04-11 00:00:00','yyyy-mm-dd hh24:mi:ss') 
and table_owner='SUMMA'
and TRUNCATED='YES'

Find out the machine and user who trigger the truncate 

通过ASH 视图 DBA_HIST_ACTIVE_SESS_HISTORY和 V$ACTIVE_SESSION_HISTORY来定位一些DDL语句， 由于ASH默认是1秒采样一次，所以如果遇到了一些例如RAC 中truncate/drop 常见的 DFS Lock Handle、Enqueue Lock等等待，那么一般ASH都能捕捉到这个DDL，当然这也看运气，毕竟ASH不是审计功能。

SQL_OPCODE    12 为DROP TABLE  10为 DROP INDEX、85为TRUNCATE TABLE、86为TRUNCATE CLUSTER

select USER_ID, SQL_OPCODE, XID, SAMPLE_TIME,MODULE,MACHINE
from v$active_session_history
where SQL_OPCODE in (12, 10, 85, 86)
and SAMPLE_TIME between xx and xx;

select USER_ID, SQL_OPCODE, XID, SAMPLE_TIME,MODULE,MACHINE
from dba_hist_active_sess_history
where SQL_OPCODE in (12, 10, 85, 86)
and SAMPLE_TIME between xx and xx;

Example:

select *from v$active_session_history

where SQL_OPCODE in (12, 10, 85, 86)

and SAMPLE_TIME between TO_DATE('2014-04-10 00:00:00','YYYY-MM-DD HH24:MI:SS') and TO_DATE('2014-04-10 17:00:00','YYYY-MM-DD HH24:MI:SS');

select * from dba_hist_active_sess_history

where SQL_OPCODE in (12, 10, 85, 86)

and SAMPLE_TIME between TO_DATE('2014-04-10 00:00:00','YYYY-MM-DD HH24:MI:SS') and TO_DATE('2014-04-10 17:00:00','YYYY-MM-DD HH24:MI:SS');

Pasted from: <http://www.askmaclean.com/archives/track-drop-truncate-ddl.html>

Table operation monitor (Oracle enterprise)

1. change the database parameter audit and restart the database

alter system set audit_trail=db scope=spfile;

2. Create policy

DBMS_FGA.ADD_POLICY(
   object_schema      VARCHAR2, 
   object_name        VARCHAR2, 
   policy_name        VARCHAR2, 
   audit_condition    VARCHAR2, 
   audit_column       VARCHAR2, 
   handler_schema     VARCHAR2, 
   handler_module     VARCHAR2, 
   enable             BOOLEAN, 
   statement_types    VARCHAR2,
   audit_trail        BINARY_INTEGER IN DEFAULT,
   audit_column_opts  BINARY_INTEGER IN DEFAULT);

Pasted from: <http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_fga.htm#ARPLS66350>

 

Example:

BEGIN
DBMS_FGA.ADD_POLICY (
   object_schema      =>  'scott', 
   object_name        =>  'emp', 
   policy_name        =>  'scott_emp', 
   audit_condition    =>   NULL, 
   audit_column       =>   NULL, 
   handler_schema     =>   NULL, 
   handler_module     =>   NULL, 
   enable             =>   TRUE, 
   statement_types    =>  'SELECT, DELETE, INSERT, UPDATE', 
   audit_trail        =>   DBMS_FGA.DB, 
   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS); 
end;

Pasted from: <http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_fga.htm#ARPLS226>

 

3. use scott user login to select the table. and check the result in

SELECT * FROM DBA_FGA_AUDIT_TRAIL;

4. Check the policy

select * from DBA_AUDIT_POLICIES;

5. Drop policy

DBMS_FGA.DISABLE_POLICY(
   object_schema  VARCHAR2, 
   object_name    VARCHAR2, 
   policy_name    VARCHAR2 ); 

Example

DBMS_FGA.DISABLE_POLICY (
object_schema   =>  'scott',
object_name     =>  'emp',
policy_name     =>  'mypolicy1');