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

最新推荐文章于 2024-01-21 10:04:47 发布

jnrjian

最新推荐文章于 2024-01-21 10:04:47 发布

阅读量584

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/jnrjian/article/details/128253442

版权

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 = '<object_schema>' and table_name like '<object_name>'.

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

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

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

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;