Thứ Năm, 30 tháng 8, 2018

Cấu hình audit FGA trên Oracle database

/***** 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;

--select * from DBA_COMMON_AUDIT_TRAIL where db_user='USER1';

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
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;


ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master