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
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 ?
 
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Secure Connection
Next Topic: Commonly used root certificates
Goto Forum:
  


Current Time: Tue Jan 21 08:17:09 CST 2020