/***** 1.CHECK *****/
-- Policy da duoc cau hinh
select *
from DBA_AUDIT_POLICIES;
-- Check log da audit (bang sys.fga_log$)
--truncate
table fga_log$;
select *
from dba_fga_audit_trail where db_user='USER1' and timestamp > sysdate-1 order by timestamp
desc;;
select *
from sys.fga_log$ where ntimestamp# >
sysdate-1 order by ntimestamp# desc;
SQL>
show parameter audit;
NAME TYPE
------------------------------------
--------------------------------
VALUE
------------------------------
audit_file_dest string
/oracle/base/admin/fpdb/adump
audit_sys_operations boolean
FALSE
audit_syslog_level string
audit_trail string
DB
/***** 2.DISABLE *****/
BEGIN
DBMS_FGA.disable_policy(
object_schema => 'TEST1_OWNER',
object_name => 'TABLE1_GRANTED',
policy_name => 'FGA_TABLE1');
END;
/
/***** 3.ADD *****/
BEGIN
DBMS_FGA.add_policy(
object_schema => 'TEST1_OWNER',
object_name => 'TABLE1_GRANTED',
policy_name => 'FGA_TABLE1',
AUDIT_CONDITION => 'SYS.check_ip_machine
= 1',
--audit_condition =>
SYS_CONTEXT('USERENV','SESSION_USER') <> 'TEST2',
statement_types => 'SELECT,
INSERT,UPDATE,DELETE'
);
END;
/
/***** 4.ROLLBACK *****/
-- Disable
BEGIN
DBMS_FGA.disable_policy(
object_schema => 'TEST1_OWNER',
object_name => 'TABLE1_GRANTED',
policy_name => 'FGA_TABLE1');
END;
/
-- Drop
BEGIN
DBMS_FGA.drop_policy(
object_schema => 'TEST1_OWNER',
object_name => 'TABLE1_GRANTED',
policy_name => 'FGA_TABLE1';
END;
/
-- Script disable all policy
-- Script disable all policy
select 'BEGIN
DBMS_FGA.disable_policy(object_schema => '''||object_schema||''',
object_name => '''|| object_name|| ''',
policy_name => ''' ||policy_name||''');
END;
'
from DBA_AUDIT_POLICIES;
-- Script disable all policy
select 'BEGIN
DBMS_FGA.drop_policy(object_schema => '''||object_schema||''',
object_name => '''|| object_name|| ''',
policy_name => ''' ||policy_name||''');
END;
'
from DBA_AUDIT_POLICIES;
