Home » RDBMS Server » Security » DBMS_FGA ADD_POLICY (12c)
DBMS_FGA ADD_POLICY [message #661496] Tue, 21 March 2017 10:34 Go to previous message
Messages: 14
Registered: March 2017
Junior Member

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 ??

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

create or replace PROCEDURE test_email_alert (sch varchar2, tab varchar2)
msg varchar2(20000) := sch||'.'||tab||' table violation. The time is: ';
  msg := msg||to_char(SYSDATE, 'Day DD MON, YYYY HH24:MI:SS');
    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 ?
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Secure Connection
Next Topic: Commonly used root certificates
Goto Forum:

Current Time: Mon Sep 21 01:36:58 CDT 2020