新增一个表空间用于存储审计日志
SQL> CREATE tablespace audit_data datafile '/data/oradata/orcl/audit01.dbf' SIZE 100M autoextend ON ;
设定审计数据存放表空间
SQL> BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_DATA'
);
END;
/
查看是否ok
SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name ='AUD$';
修改参数，重启后生效
db_extended:启用审计，把审计结果存放在数据库的sys.aud$表中，并且在clob列的sqlbind和sqltext字段记录额外的信息
SQL> alter system set audit_trail=DB,EXTENDED scope=spfile;
审计 lianguser.t_test 表 DELETE,UPDATE 都要记录审计
SQL> AUDIT DELETE,UPDATE on lianguser.t_test by ACCESS WHENEVER SUCCESSFUL;
创建测试表
create table t_test (id number,name varchar2(30));
insert into t_test values(1,'liang');
commit;
测试：
update t_test set name='liang2' where id =1;
查询审计记录
col OBJ_NAME for a20
col ACTION_NAME for a20
col timestamp for a40
SELECT OBJ_NAME,ACTION_NAME ,to_char(timestamp,'yyyy/mm/dd , HH:MI:SS') from sys.dba_audit_object;
OBJ_NAME ACTION_NAME TO_CHAR(TIMESTAMP,'DD
----------------------- ---------------- ---------------------
T_TEST UPDATE 2018/07/09 , 03:07:07