Fine Grain Auditing example

This is the simplest example of FGA package.
Lets assume you want to audit who selects ID column on a table REDDTA.T1.

exec dbms_fga.add_policy (-
object_schema=>'REDDTA',-
object_name=>'T1',-
policy_name=>'T1_REDDTA',-
audit_column => 'ID',-
statement_types => 'SELECT'-
);

As a result you get something like this:

1* SELECT policy_name, object_name, statement_type, os_user, db_user FROM dba_fga_audit_trail

POLICY_NAME OBJECT_NAME STATEME OS_USER DB_USER
----------- ----------- ------- -------------------- --------------------
T1_REDDTA T1 SELECT oracle REDDTA



DBMS_FGA.ADD_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2,
audit_condition VARCHAR2,
audit_column VARCHAR2,
handler_schema VARCHAR2,
handler_module VARCHAR2,
enable BOOLEAN,
statement_types VARCHAR2,
audit_trail BINARY_INTEGER IN DEFAULT,
audit_column_opts BINARY_INTEGER IN DEFAULT);

What's worth to know:
audit_column - The columns to be checked for access. These can include hidden columns. The default, NULL, causes audit if any column is accessed or affected.
statement_types - The SQL statement types to which this policy is applicable: INSERT, UPDATE, DELETE, or SELECT only.

Procedure to move outline between instances or schemas

This procedure can be used to move outline between schemas and/ora databases.


CREATE OR REPLACE procedure SYSTEM.outline_get (source_inst in varchar2, outline_name in varchar2, source_owner varchar2, dest_owner varchar2)
is
TYPE typoutput_1 IS REF CURSOR;
replace_user varchar2(64);
output_1 typoutput_1;
sql_to_tune_orig long;
sql_to_tune long;
sql_to_run varchar2(4000);
outline_creator varchar2(4000);
outline_exists number;
outline_prv_exists number;
cursor replace_user_table is
select user_table_name from system.ol$hints where user_table_name is not null;
user_tbl_name varchar2(64);
user_tbl_name_fixed varchar2(64);

begin
/* checking whether temporary private outline exists, drop if exists */
    select count(*) into outline_prv_exists from system.ol$ where ol_name = 'PRV1_TMP';
        if outline_prv_exists > 0 then
            dbms_output.put_line ('Dropping private outline PRV1_TMP');
            execute immediate 'drop private outline PRV1_TMP';
        end if;

/* get sql statement the outline is created for and modify each schema occurance */
sql_to_run :=
    'select sql_text from dba_outlines@'||SOURCE_INST||' where name='''||outline_name||'''';
open output_1 for sql_to_run;
fetch output_1 into sql_to_tune_orig;
sql_to_tune := REPLACE(sql_to_Tune_orig,upper(SOURCE_OWNER),upper(DEST_OWNER));

/* Create temporary private outline for query */
execute immediate ('create private outline PRV1_TMP on '||sql_to_tune);

/* Replace rows from ol$hint from source */
execute immediate ('delete from system.ol$hints where ol_name = ''PRV1_TMP''');
COMMIT;
sql_to_run := 'insert into system.ol$hints select * from outln.ol$hints@'||SOURCE_INST||' where ol_name = '''||outline_name||'''';
execute immediate (sql_to_run);
COMMIT;
update system.ol$hints set ol_name = 'PRV1_TMP';
COMMIT;

/* Change USER_TABLE_NAME for each row */
open replace_user_table;
loop
fetch replace_user_table into user_tbl_name;
user_tbl_name_fixed := replace(user_tbl_name,upper(SOURCE_OWNER),upper(DEST_OWNER));
update system.ol$hints set user_table_name = user_tbl_name_fixed where user_table_name = user_tbl_name;
commit;
exit when replace_user_table%NOTFOUND;
end loop;

/* Refresh private outline after changes */
dbms_outln_edit.refresh_private_outline('PRV1_TMP');

/* Check if outline exists, skip is exists, create if no */
select count(*) into outline_exists from outln.ol$ where ol_name = ''||outline_name||'';
 if outline_exists > 0 then
 dbms_output.put_line ('PUBLIC WITH THIS NAME EXISTS!!!!!');
    else
    execute immediate ('create outline '||outline_name||' from private PRV1_TMP');
    execute immediate ('drop private outline PRV1_TMP');
 end if;
end;
/

How to find value used in bind variables

If sql statement used bind variables is still in shared pool, and you have Oracle 10g, you can find what values variables have in the following way:

select sql_id, name, position,value_string from v$sql_bind_capture where sql_id='';