SESSION_ROLES and Triggers

[MadJock]

Hi,

I am writing a trigger that will write audit information on UPDATE, INSERT or DELETE for a given table. This audit info should only be written for certain roles (the roles to audit are held in a table called AUDITROLE.

I don't want to query AUDITROLE for each row, so instead I set a package variable that determines whether to audit or not.

Excerpt From Package Spec

CREATE OR REPLACE PACKAGE pkg_audit
AUTHID CURRENT_USER AS

g_auditOn NUMBER(1);

FUNCTION AuditIsOn RETURN NUMBER;
END;

Excerpt From Package Body:

CREATE OR REPLACE PACKAGE BODY pkg_audit AS

FUNCTION AuditIsOn IS
  v_count NUMBER(3);
BEGIN
 IF g_auditOn IS NULL THEN
   -- true on first use only
   SELECT COUNT(1)
   INTO v_count
   FROM session_roles, auditrole
   WHERE auditrole.role_name = session_roles.role;

   IF v_count <> 0 THEN
     -- user has at least one role hat should be audited
     g_auditOn := 1;
   ELSE
     -- doesn't belong to any audit roles
     g_auditOn := 0;
   END IF;
  END IF;

  RETURN g_auditOn;
END; --function

END; --pkg

Excerpt From Trigger

IF pkg_audit.AuditIsOn = 0 THEN
  RETURN;
ELSE
  -- audit code here
END IF;

Now the problem!! I gather that session_roles is not available when using definer or invoker rights. This means that the SELECT in pkg_audit will always have a count of zero: i.e. I will never get an audit. Doing a 'select count(*) from session_roles' within the trigger will result in a zero. Doing the same in a query window will return the correct value.

I would imagine that what I'm trying to achieve is not that unusual - does anyone have any methods to fix this or alternative strategies?

Thanks in advance,

Graeme


"Just beacuse you're paranoid, don't mean they're not after you

 

[MadJock]

This:

http://asktom.oracle.com/pls/ask/f?..._P8_DISPLAYID,F4950_P8_CRITERIA:1066232644810

from ASKTom....

Oh well

"Just beacuse you're paranoid, don't mean they're not after you