Forum Stats

  • 3,839,356 Users
  • 2,262,486 Discussions
  • 7,900,947 Comments

Discussions

Conditional FGA Audit Alert

I need to send different message body content in an alert email, based on which of the two possible values the updated column, ACTIVE_IND, is set to, 'Y' or 'N'. It's my misfortune that my audit_column must also be used in the audit_condition. Because audit_condition is limited to simple logic, I created two policies, one firing on 'Y' and the other firing on 'N'. I realize there is no concept of :OLD and :NEW like there is in a trigger. Nevertheless, it seems strange to me that I get both emails on every update regardless of whether it's set to 'Y' or 'N'. It's as though DBMS_FGA is gathering both the :OLD and :NEW values, and firing on both (below).

I finally abandoned this, and performed the logic in the audit handler instead, using SYS_CONTEXT('USERENV', 'CURRENT_BIND') to detect the value it's being set to, but it still generates both emails every time (see further below).

Can this even be done in a fine-grained audit policy? If not, is it possible to generate an email from an update trigger, where I can use :OLD and :NEW

BEGIN  DBMS_FGA.ADD_POLICY (    object_schema     => 'POP',    object_name       => 'POP_USER',    policy_name       => 'AUD$UPDATE_POP_USER_ACTIVE_IND_Y',      audit_condition   => 'ACTIVE_IND = Q'['Y']',    audit_column      => 'ACTIVE_IND',    handler_schema    => 'FGA_ADMIN',    handler_module    => 'AUD$UPDATE_POP_USER_ACTIVE_IND',    enable            => TRUE,    statement_types   => 'UPDATE');END;/BEGIN  DBMS_FGA.ADD_POLICY (    object_schema     => 'POP',    object_name       => 'POP_USER',    policy_name       => 'AUD$UPDATE_POP_USER_ACTIVE_IND_N',       audit_condition   => 'ACTIVE_IND = Q'['N']',    audit_column      => 'ACTIVE_IND',    handler_schema    => 'FGA_ADMIN',    handler_module    => 'AUD$UPDATE_POP_USER_ACTIVE_IND',    enable            => TRUE,    statement_types   => 'UPDATE');END;

/

CREATE OR REPLACE PROCEDURE aud$update_pop_user_active_ind (sch VARCHAR2, tbl VARCHAR2, pol VARCHAR2) AUTHID DEFINER AS    v_rowid VARCHAR2(18);  v_count PLS_INTEGER := 0;  v_current_bind VARCHAR2(4000);  msg VARCHAR2(4000);BEGIN  SELECT SYS_CONTEXT('USERENV', 'CURRENT_BIND') INTO v_current_bind FROM dual;  msg := v_current_bind;    SELECT   substr(SYS_CONTEXT('USERENV', 'CURRENT_BIND'), -18, 18)  INTO v_rowid  FROM dual;  ELSE   v_rowid := 'AAAR7HAANAAAADzAAA';  END IF;    SELECT    CASE active_ind WHEN 'Y' THEN       Q'[The user status of ]' || upper(fname) || ' ' || upper(lname) || Q'[ was changed from Active to Inactive]'    ELSE       Q'[The user status of ]' || upper(fname) || ' ' || upper(lname) || Q'[ was changed from Inactive to Active]'    END     INTO msg  FROM pop_user  WHERE ROWID = v_rowid;/