Re: Check user roles in trigger?

Dr Drudge wrote:

> 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
Why not look all_?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)