Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1080747461.536746_at_yasure>...
> 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_?
Which all_? view are you referring? There is no ALL_ROLS_PRIVS.
I cannot find an equivalent.
Received on Wed Mar 31 2004 - 14:44:34 CST
