【OCP講課】Audit實驗與tips

--options:
dba_stmt_audit_opts 
dba_obj_audit_opts 
dba_priv_audit_opts

--audit info
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL

NOAUDIT ALL;               -- cancel statement audit
NOAUDIT ALL PRIVILEGES;    -- cancel pribilege audit
NOAUDIT ALL ON DEFAULT;    -- cancel object audit

--delete audit info
DELETE FROM SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE obj$name='EMP';

----FGA audit
sqlplus hao/hao
create table test (id number, name varchar2(100));
create table test2 (a date);
insert into test select rownum,object_name from dba_objects;

create or replace procedure test_handler(schema_name varchar2,table_name varchar2,policy varchar2)
is
begin
insert into test2 values(sysdate);
end;

begin
dbms_fga.add_policy (
OBJECT_SCHEMA=>'HAO',
OBJECT_NAME=>'TEST',
POLICY_NAME=>'audit_test',
AUDIT_CONDITION=>'id=3',
AUDIT_COLUMN=>'name',
HANDLER_SCHEMA=>'HAO',
HANDLER_MODULE=>'test_handler',
ENABLE=>TRUE,
STATEMENT_TYPES=>'SELECT'
 );
end;
/

select name from test where id=3;
select id from test where id=3;
select * from test;

 

begin
dbms_fga.drop_policy (
'HAO','TEST','audit_test'
 );
end;
/

set lines 180 pages 999
col OBJ$NAME for a30
select OBJ$SCHEMA,OBJ$NAME,POLICYNAME,STMT_TYPE from FGA_LOG$ ;

----------------------
audit_sys_operations=TRUE