0 Replies Latest reply on Jan 8, 2020 5:40 AM by 3693033

    Conditional FGA Audit Alert

    3693033

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