Home » SQL & PL/SQL » SQL & PL/SQL » oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb)
| oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661533] |
Wed, 22 March 2017 14:32  |
 |
jokrasa
Messages: 14
Registered: March 2017
|
Junior Member |
|
|
my goal is to run a select to a table and based on the certain rows dynamically create an audit trail or policy on them.
so..
I need to recreate the following stmt with binding parameters wrapped in a function to Loop with :
BEGIN
DBMS_FGA.ADD_POLICY (
object_schema => 'I_SCHEMA',
object_name => 'RECIPIENT',
policy_name => 'CHK_I_SCHEMA_RECIPIENT',
audit_column => 'CARRIER_NO',
audit_condition => 'CARRIER_NO = ''20'' ',
handler_schema => 'SYSADMIN_FGA',
handler_module => 'TEST_EMAIL_ALERT(''I_SCHEMA'',''RECIPIENT'',''TEST_CHK_SCHEMA_RECIPIENT'')',
enable => TRUE,
statement_types => 'SELECT, UPDATE',
audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED);
END;
/
First a procedure that calls the function...
CREATE OR REPLACE PROCEDURE audit_slac_tables
AS
--DECLARE
emailer VARCHAR2(40):='audit_email_alert';
isSuccess VARCHAR2(40);
CURSOR myCursor IS SELECT SCHEMA as sch, TABLE_NAME as tab, FILTER_COLUMN as col, WHERE_COND as pred FROM SLAC_REDACTION_TABLE slac where slac.table_name='RECIPIENT';
BEGIN
FOR curRec IN myCursor
LOOP
isSuccess := set_policy(curRec.sch ,curRec.tab, curRec.col, curRec.pred, emailer);
DBMS_OUTPUT.PUT_LINE('isSuccess = :' || isSuccess);
END LOOP;
commit;
END audit_slac_tables;
then the set_policy function that gets called/Looped:
create or replace
function set_policy
( sch VARCHAR2 ,
tab VARCHAR2,
col VARCHAR2,
pred VARCHAR2,
emailer VARCHAR2
)
return VARCHAR2 is
policy_sql_stmt varchar2(1000);
BEGIN
--policy_sql_stmt :=
-- 'BEGIN
-- DBMS_FGA.ADD_POLICY (
-- object_schema => '''||sch||''',
-- object_name => '''||tab||''',
-- policy_name => ''CHK_'||sch||'_'||tab||''',
-- audit_column => '''||col||''',
-- audit_condition => '':pred'',
-- handler_schema => ''SYSADMIN_FGA'',
-- handler_module => '''||emailer||'('''||''||sch||''||''','''||''||tab||''||''',''''CHK_'||sch||'_'||tab||''')'',
-- enable => TRUE,
-- statement_types => ''SELECT, UPDATE'',
-- audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED);
-- return policy_name
-- END;
-- get_policy';
policy_sql_stmt :=
'BEGIN
SYS.DBMS_FGA.ADD_POLICY (
object_schema => '':s'',
object_name => '':t'',
policy_name => ''CHK_:s_:t'',
audit_column => '':c'',
audit_condition => '':p'',
handler_schema => ''SYSADMIN_FGA'',
handler_module => '''||emailer||'('''':s'''','''':t'''',''''CHK_:s_:t'''')'',
enable => TRUE,
statement_types => ''SELECT, UPDATE'',
audit_trail => SYS.DBMS_FGA.DB + SYS.DBMS_FGA.EXTENDED);
END;';
DBMS_OUTPUT.PUT_LINE('policy_sql_stmt = :' || policy_sql_stmt);
EXECUTE IMMEDIATE policy_sql_stmt USING sch,tab,col,pred;
RETURN 'success';
END;
if I call it...
exec AUDIT_SLAC_TABLES;
I get the following bewildering error
Error starting at line : 6 in command -
exec AUDIT_SLAC_TABLES
Error report -
ORA-06550: line 12, column 18:
PLS-00201: identifier 'SYS.DBMS_FGA' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
ORA-06512: at "GAPLITE.SET_POLICY", line 51
ORA-06512: at "GAPLITE.AUDIT_SLAC_TABLES", line 27
ORA-06512: at line 1
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Why a reference problem where the script DBMS_FGA.ADD_POLICY never had a problem?
I can run this script ( listed 1st above ) but not dynamically... it loses trhe contextual reference to the SYS packages somehow ??
|
|
|
|
|
|
|
|
|
|
|
|
| Re: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661599 is a reply to message #661533] |
Fri, 24 March 2017 11:35  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You don't need dynamic SQL, unless I'm missing something here:
create or replace
function set_policy(
sch VARCHAR2,
tab VARCHAR2,
col VARCHAR2,
pred VARCHAR2,
emailer VARCHAR2
)
return VARCHAR2
is
policy_sql_stmt varchar2(1000);
begin
sys.dbms_fga.add_policy(
object_schema => sch,
object_name => tab,
policy_name => 'CHK_|| sch || '_' || tab,
audit_column => col,
audit_condition => pred,
handler_schema => 'SYSADMIN_FGA',
handler_module => emailer || '(''' || sch || ''',''' || tab || ''','''CHK_' || sch || '_' || tab || ''')',
enable => TRUE,
statement_types => 'SELECT, UPDATE',
audit_trail => SYS.DBMS_FGA.DB + SYS.DBMS_FGA.EXTENDED
);
return 'success';
end;
/
SY.
|
|
|
|
| Re: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661749 is a reply to message #661599] |
Thu, 30 March 2017 10:04 |
|
jokrasa
Messages: 14
Registered: March 2017
|
Junior Member |
|
|
So I went with this..
THe function that gets called by the procedure to set the policy...
create or replace
function set_policy
( sch VARCHAR2 ,
tab VARCHAR2,
colm VARCHAR2,
pred VARCHAR2,
emailer VARCHAR2
)
return VARCHAR2 is
policy_sql_stmt varchar2(1000);
BEGIN
policy_sql_stmt :=
'BEGIN
SYS.DBMS_FGA.ADD_POLICY (
object_schema => :s,
object_name => :t,
policy_name => ''CHK_:s_:t'',
audit_column => :c,
audit_condition => :p,
handler_schema => ''SYSADMIN_FGA'',
handler_module => '''||emailer||'(:s,:t,''''CHK_:s_:t'''')'',
enable => TRUE,
statement_types => ''SELECT, UPDATE'',
audit_trail => SYS.DBMS_FGA.DB + SYS.DBMS_FGA.EXTENDED);
END;';
--DBMS_OUTPUT.PUT_LINE('policy_sql_stmt = :' || policy_sql_stmt);
BEGIN
EXECUTE IMMEDIATE policy_sql_stmt USING sch,tab,colm,pred;
--EXECUTE IMMEDIATE policy_sql_stmt USING pred;
EXCEPTION
WHEN OTHERS THEN
BEGIN
--dbms_output.put_line('set_policy error code: '||SQLCODE);
--dbms_output.put_line(DBMS_UTILITY.FORMAT_CALL_STACK);
RETURN ('set_policy error code: '||SQLCODE);
END;
END;
RETURN 'success';
END;
The Procedure that calls it....
CREATE OR REPLACE PROCEDURE audit_slac_tables
AS
--DECLARE
emailer VARCHAR2(40):='audit_email_alert';
isSuccess VARCHAR2(40);
CURSOR myCursor
IS
SELECT SCHEMA AS sch,
TABLE_NAME AS tab,
FILTER_COLUMN AS colm,
WHERE_COND AS pred
FROM SLAC_REDACTION_TABLE slac;
--WHERE slac.table_name IN ('RECIPIENT','CARD');
BEGIN
FOR curRec IN myCursor
LOOP
BEGIN
--emailer := getEmailer(curRec.sch ,curRec.tab);
isSuccess := set_policy(curRec.sch ,curRec.tab, curRec.colm, curRec.pred, emailer);
DBMS_OUTPUT.PUT_LINE('Proc isSuccess = :' || isSuccess);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Proc error code: '||SQLCODE);
dbms_output.put_line('Proc error msg: '||SQLERRM);
--dbms_output.put_line(DBMS_UTILITY.FORMAT_CALL_STACK);
--dbms_output.put_line('================================================');
CONTINUE;
END;
--dbms_output.put_line('================================================');
END LOOP;
COMMIT;
END audit_slac_tables;
The emailer
create or replace
function get_emailer
( sch VARCHAR2 ,
tab VARCHAR2
)
return VARCHAR2 is
emailer_sql_stmt varchar2(1000);
BEGIN
emailer_sql_stmt :=
'CREATE OR REPLACE PROCEDURE audit_email_alert (sch varchar2, tab varchar2, pol varchar2)
AS
msg varchar2(20000) := ''' || sch || '.' || tab || ' table violation. The time is: '';
BEGIN
msg := msg||to_char(SYSDATE, ''Day DD MON, YYYY HH24:MI:SS'');
SYS.UTL_MAIL.SEND (
sender => ''john.okrasa@alithya.com'',
recipients => ''john.okrasa@alithya.com'',
subject => ''Table modification on '||sch||'.'||tab||''',
message => msg);
END audit_email_alert;';
--DBMS_OUTPUT.PUT_LINE('emailer_sql_stmt = :' || emailer_sql_stmt);
EXECUTE IMMEDIATE emailer_sql_stmt;
EXCEPTION
WHEN OTHERS
THEN dbms_output.put_line('get_emailer error code: '||SQLCODE);
RETURN 'audit_email_alert';
END;
the key issue here was that I was assuming the user had implicit privileges on the sys.dbms_fga package, it was not the case... so I needed to Connect sys/xxx Grant execute on sys.dbms_fga to <username>;
Thanks !
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 12:16:41 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
