Thanks for the question, Dennis.
Asked: November 21, 2000 - 12:26 pm UTC
Last updated: January 22, 2005 - 10:12 am UTC
Version: 8.1.6
Viewed 1000+ times
You Asked
Dear Tom,
as part of the processing of a trigger, I want to check that the
invoking user -- as opposed the owner of the trigger -- is a member
of a particular role.
On my first attempt, I tried selecting from SESSION_ROLES, which
didn't return ANY rows.
My second attempt was to query USER_ROLE_PRIVS, which quickly established
that this returned the privileges of the owner of the trigger, rather than
those of the invoker.
My third attempt was to query DBA_ROLE_PRIVS, but discovered pretty
quickly that unless an explicit grant is given to the owner of the
trigger it won't work. (Unfortunately, this isn't possible.)
My fourth attempt was to try the query from within an EXECUTE IMMEDIATE
statement, but this returned an ORA-00942, i.e. table doesn't exist.
Any ideas on how I can sensibly ascertain which roles the invoker of
a trigger has been granted?
regds,
Dennis Cook
and Tom said...
A trigger always is compiled with definers rights. All queries against the data dictionary will be made under the schema of the OWNER of the trigger minus any roles that user has (eg: if the owner of the trigger issues "select count(*) from all_objects" in sqlplus and then does the same in the trigger -- they will most likely get a different count since in the trigger their roles are not active and they can see less objects they with roles active).
Since roles are disabled in the execution of the trigger -- session_roles (which lists the currently logged in users active roles) will be empty since they have no active roles in the trigger.
The dynamic access won't work either since once roles are gone in the trigger -- they are gone and you cannot get them back (until you exit the trigger). Invokers rights procedures won't help you at all here either.
I'm afraid that your only opportunity to get this information is to in fact query the DBA_ROLE_PRIVS or related DBA view. I would actually suggest not using DBA_ROLE_PRIVS but asking your admin group to write you a procedure that returns TRUE or FALSE if a given user has a given privelege (eg: a function like:
create function has_priv( p_user in varchar2,
p_priv in varchar2 ) return boolean
....
and granted you execute on that. I would also suggest they use a view more like that i have at:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:853427230099 <code>
rather then dba_role_privs which can be very expensive to evaluate.
If you need to know the roles -- you will need access to this information from the DBA tables. It is the only way.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment