Oracle fga 对整个schema进行细粒度审计

INSTRUCTIONS
Create FGA Policies for all tables in schema SCOTT:

begin
   support_grouped_dbms_fga.add_grouped_policy(object_schema => 'SCOTT',
                                               grouped_policy_name => 'GROUP_A');
end;
/

select policy_name, object_name, enabled from dba_audit_policies where object_schema = 'SCOTT';
 

Drop all FGA Policies with the names  'GROUP_A%' from the SCOTT schema.

 
begin
   support_grouped_dbms_fga.drop_grouped_policy(object_schema => 'SCOTT',
                                                grouped_policy_name => 'GROUP_A');
end;
/
Disable this group of FGA Policies:

 
begin
   support_grouped_dbms_fga.disable_grouped_policy(object_schema => 'SCOTT',
                                                   grouped_policy_name => 'GROUP_A');
end;
/
 
Enable a group of FGA Policies:

begin
   support_grouped_dbms_fga.enable_grouped_policy(object_schema => 'SCOTT',
                                                  grouped_policy_name => 'GROUP_A');
end;
/
 
CAUTION
This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
SAMPLE CODE
-- support_grouped_dbms_fga.sql

create or replace package support_grouped_dbms_fga as

-- required privileges (grant directly), for example :

-- grant select on dba_tables to system;
-- grant select on dba_audit_policies to system;
-- grant execute on dbms_fga to system;

-- This package allows to create a group of audit policies with the same attributes
-- for all (default) or a number of tables in a schema at the same time.

-- Use a unique grouped_policy_name for each distinct group
-- of audit policies you want to manipulate with this package.

-- There's no additional metadata stored, so the idea is to adhere to
-- the naming convention for the grouped_policy_name, then this package
-- creates the policies with the names like GROUP_NAME000001 etc.

-- The parameters are mapped directly to those available in DBMS_FGA with the same defaults.

-- There's no exception handler so all usual exceptions from dbms_fga are raised
-- as unhandled exceptions, there's one check to avoid duplicate group names per schema.

-- If you refer to column names, naturally they must exist in all tables that are in the list
-- select * from dba_tables where owner = '' and table_name like ''.

procedure add_grouped_policy(object_schema in varchar2,
                             object_name in varchar2 default '%%',
                             grouped_policy_name in varchar2 default 'POL_',
                             audit_condition in varchar2 default null,
                             audit_column in varchar2 default null,
                             handler_schema in varchar2 default null,
                             handler_module in varchar2 default null,
                             enable in boolean default true,
                             statement_types in varchar2 default 'SELECT',
                             audit_trail in binary_integer 
                                  default DBMS_FGA.DB_EXTENDED,
                             audit_column_opts in binary_integer 
                                  default DBMS_FGA.ANY_COLUMNS);

procedure drop_grouped_policy(object_schema in varchar2,
                              object_name in varchar2 default '%%',
                              grouped_policy_name in varchar2 default 'POL_');

procedure disable_grouped_policy(object_schema in varchar2,
                                 object_name in varchar2 default '%%',
                                 grouped_policy_name in varchar2 default 'POL_');
 
procedure enable_grouped_policy(object_schema in varchar2,
                                object_name in varchar2 default '%%',
                                grouped_policy_name in varchar2 default 'POL_');

table_query varchar2(200)  ;
v_table_name varchar2(30);
v_schema varchar2(30);
v_policy_name varchar2(30);
v_search_policy varchar2(30);

end;
/
show err

create or replace package body support_grouped_dbms_fga as

procedure add_grouped_policy(object_schema in varchar2,
                             object_name in varchar2 default '%%',
                             grouped_policy_name in varchar2 default 'POL_',
                             audit_condition in varchar2 default null,
                             audit_column in varchar2 default null,
                             handler_schema in varchar2 default null,
                             handler_module in varchar2 default null,
                             enable in boolean default true,
                             statement_types in varchar2 default 'SELECT',
                             audit_trail in binary_integer 
                             default DBMS_FGA.DB_EXTENDED,
                             audit_column_opts in binary_integer 
                             default DBMS_FGA.ANY_COLUMNS)
as

v_policy_counter number := 0;
v_exist number;

type Table_Cursor_type is ref cursor;
table_cursor Table_Cursor_type;

  begin
     v_schema := dbms_assert.SCHEMA_NAME(upper(object_schema));

     v_search_policy := grouped_policy_name||'%';  
     table_query := 'select count(*) from dba_audit_policies where object_schema = '''||
                     v_schema||''' and POLICY_NAME like :1';

     open table_cursor for table_query using v_search_policy;
        fetch table_cursor into v_exist;
     close table_cursor;

     if v_exist > 0 then
         raise_application_error(-20001,'Grouped Policy Name Exists');
     end if;

     table_query := 'select table_name from dba_tables where owner = '''||
                     v_schema||''' and table_name like :1';
                    
     open table_cursor for table_query using object_name;
     loop
         fetch table_cursor into v_table_name;
         EXIT WHEN table_cursor%NOTFOUND;
         v_policy_counter :=  v_policy_counter + 1;
         v_policy_name := grouped_policy_name||trim(to_char(v_policy_counter,'099999'));

         dbms_fga.add_policy(v_schema,v_table_name,v_policy_name,
                             audit_condition, audit_column,
                             handler_schema,handler_module,
                             enable, statement_types, audit_trail,
                             audit_column_opts);
     end loop;
     close table_cursor;
  end;

procedure drop_grouped_policy(object_schema in varchar2,
                             object_name in varchar2 default '%%',
                             grouped_policy_name in varchar2 default 'POL_')
         
as

type Table_Cursor_type is ref cursor;
table_cursor Table_Cursor_type;

  begin
     v_schema := dbms_assert.SCHEMA_NAME(upper(object_schema));

     v_search_policy := grouped_policy_name||'%'; 
     table_query := 'select policy_name, object_name from dba_audit_policies 
                     where object_schema = '''||v_schema||''' and policy_name like :1
                     and object_name like :2';
     
     open table_cursor for table_query using v_search_policy, object_name;
     loop
         fetch table_cursor into v_policy_name, v_table_name;
         EXIT WHEN table_cursor%NOTFOUND;

         dbms_fga.drop_policy(v_schema,v_table_name,v_policy_name);
     end loop;
     close table_cursor;
  end;

procedure enable_grouped_policy(object_schema in varchar2,
                                object_name in varchar2 default '%%',
                                grouped_policy_name in varchar2 default 'POL_')
         
as

type Table_Cursor_type is ref cursor;
table_cursor Table_Cursor_type;

  begin
     v_schema := dbms_assert.SCHEMA_NAME(upper(object_schema));

     v_search_policy := grouped_policy_name||'%'; 
     table_query := 'select policy_name, object_name from dba_audit_policies 
                     where object_schema = '''||v_schema||''' and policy_name like :1
                     and object_name like :2';
     
     open table_cursor for table_query using v_search_policy, object_name;
     loop
         fetch table_cursor into v_policy_name, v_table_name;
         EXIT WHEN table_cursor%NOTFOUND;

         dbms_fga.enable_policy(v_schema,v_table_name,v_policy_name);
     end loop;
     close table_cursor;
  end;

procedure disable_grouped_policy(object_schema in varchar2,
                             object_name in varchar2 default '%%',
                             grouped_policy_name in varchar2 default 'POL_')
         
as

type Table_Cursor_type is ref cursor;
table_cursor Table_Cursor_type;

  begin
     v_schema := dbms_assert.SCHEMA_NAME(upper(object_schema));

     v_search_policy := grouped_policy_name||'%'; 
     table_query := 'select policy_name, object_name from dba_audit_policies 
                     where object_schema = '''||v_schema||''' and policy_name like :1
                     and object_name like :2';
                    
     open table_cursor for table_query using v_search_policy, object_name;
     loop
         fetch table_cursor into v_policy_name, v_table_name;
         EXIT WHEN table_cursor%NOTFOUND;
         
         dbms_fga.disable_policy(v_schema,v_table_name,v_policy_name);
     end loop;
     close table_cursor;
  end;

end support_grouped_dbms_fga;
/
show err

SAMPLE OUTPUT
begin 
  support_grouped_dbms_fga .add_grouped_policy 
        (object_schema => 'XXX', 
         grouped_policy_name => 'XXXX'); 
    end; 
/ 
PL/SQL procedure successfully completed. 

select policy_name, object_name, enabled  
     from dba_audit_policies where object_schema = 'XXXX'; 
    
select *From dba_audit_policies where object_schema = 'ODS_HR'; 

POLICY_NAME                    OBJECT_NAME                    ENA 
------------------------------ ------------------------------ --- 
GROUP_A000001                  EMP                            YES 
GROUP_A000002                  DEPT                           YES 
GROUP_A000003                  BONUS                          YES 
GROUP_A000004                  SALGRADE                       YES 
GROUP_A000005                  DUMMY                          YES

select *  from DBA_FGA_AUDIT_TRAIL;
 

BEGIN
DBMS_FGA.drop_POLICY(
object_schema => 'XXX',
object_name => 'XXX',
policy_name => 'XXXXX' 
 );
END;