Home » RDBMS Server » Security » DBMS_FGA ADD_POLICY (12c)
DBMS_FGA ADD_POLICY [message #661496] Tue, 21 March 2017 10:34 Go to next message
jokrasa
Messages: 14
Registered: March 2017
Junior Member
http://docs.oracle.com/cd/E25054_01/network.1111/e16543/auditing.htm

How could you pass args to the email alert below if you are calling it from the 'CHK_HR_EMP' policy to trigger it ?

In the parameter handler_module => 'EMAIL_ALERT', I would be looking for a way to pass The object schema and name so as to specify which object triggered the email in the message.

That way I could use the same email alert procedure for multiple policies... Otherwise I have to Hard code the message and create one for each policy.

It looks like a question of figuring out the syntax for referencing/binding to the arguments ??

BEGIN
 DBMS_FGA.ADD_POLICY (
  object_schema      =>  'HR',
  object_name        =>  'EMPLOYEES',
  policy_name        =>  'CHK_HR_EMP',
  audit_column       =>  'SALARY', 
  handler_schema     =>  'SYSADMIN_FGA',
  handler_module     =>  'EMAIL_ALERT(object_schema, object_name )',
  enable             =>   TRUE,
  statement_types    =>  'SELECT, UPDATE',
  audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED); 
END;
/




create or replace PROCEDURE test_email_alert (sch varchar2, tab 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');
UTL_MAIL.SEND (
    sender      => 'youremail@example.com',
    recipients  => ''youremail@example.com',
    subject     => 'Table modification on '||sch||'.'||tab,
    message     => msg);
END test_email_alert;



Also is there a way to view the DBMS_FGA.ADD_POLICY you created by the SQLPLUS CLI?

in SQL Developer ?
Re: DBMS_FGA ADD_POLICY [message #661499 is a reply to message #661496] Tue, 21 March 2017 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65202
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

dba_audit_policies
dba_audit_policy_columns

Re: DBMS_FGA ADD_POLICY [message #661747 is a reply to message #661499] Thu, 30 March 2017 09:54 Go to previous messageGo to next message
jokrasa
Messages: 14
Registered: March 2017
Junior Member
Thank You !
Re: DBMS_FGA ADD_POLICY [message #661748 is a reply to message #661747] Thu, 30 March 2017 09:56 Go to previous messageGo to next message
jokrasa
Messages: 14
Registered: March 2017
Junior Member
So I went with this..

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;
Re: DBMS_FGA ADD_POLICY [message #661752 is a reply to message #661748] Thu, 30 March 2017 10:46 Go to previous message
BlackSwan
Messages: 25719
Registered: January 2009
Location: SoCal
Senior Member
the EXCEPTION handler code is nothing more than a useless, hardcoded BUG which should be totally removed & NEVER used again.
Previous Topic: Secure Connection
Next Topic: Grant user full access to other schema
Goto Forum:
  


Current Time: Wed Nov 22 19:52:10 CST 2017

Total time taken to generate the page: 0.05433 seconds