Re: Check user roles in trigger?

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 31 Mar 2004 17:08:45 -0800
Message-ID: <1080781705.693079@yasure>

Dr Drudge wrote:

> 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.

Sorry. I was running out the door and didn't type what I meant.

I'm in agreement with Sybrand that if your design was good this would be unnecessary. If you are looking for privs contained in a role look at:

ROLE_SYS_PRIVS or ROLE_TAB_PRIVS

If you are looking for roles assigned to a user look at:

DBA_ROLE_PRIVS
ROLE_ROLE_PRIVS
USER_ROLE_PRIVS If you are looking for table/object privs look at:

USER_TAB_PRIVS_RECD
USER_COL_PRIVS_RECD
USER_SYS_PRIVS

HTH

-- 
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)

Received on Wed Mar 31 2004 - 19:08:45 CST