Check user roles in trigger?

From: Dr Drudge <drdrudge_at_hotmail.com>
Date: 31 Mar 2004 07:13:42 -0800
Message-ID: <748419a0.0403310713.2a88c516@posting.google.com>

I have a requirement to check a user's role before allowing update of a column. Briefly, some roles are always permitted to update, others are permitted only if the "OLD" value is NULL.

I first tried a trigger [Oracle 8.1.7, Windows] (here is the interesting snip):

---

• some roles have priv to enter a new HC_NUMBER if( :OLD.HC_NUMBER is NULL ) then begin select NULL into cDummy from DBA_ROLE_PRIVS p where p.GRANTEE = USER and p.GRANTED_ROLE in ('ENROLLSUPER','FINANCE','ENROLL_R','ENROLL_RW') and rownum=1 ;

	EXCEPTION
	WHEN NO_DATA_FOUND THEN
	    	RAISE_APPLICATION_ERROR(-20100,'PERMISSION DENIED ON ENTRY OF HC

NUMBER ');
 end;                  

 else -- user is updating an existing hc number
.
.

---

but this won't compile:
PLS-00201: identifier 'SYS.DBA_ROLE_PRIVS' must be declared

I really wanted to use USER_ROL_PRIVS, but of course the trigger is owned by someone other that the current user so that won't work.

Any ideas on a fix or a better way?

TIA Received on Wed Mar 31 2004 - 09:13:42 CST