problem obtaining user_role_privs via another schema's package
2822481Apr 16 2007 — edited Apr 18 2007I have encountered strange behaviour when trying to get hold of the current users ROLE privileges using a packagised function owned by another schema/user.
I have created multiple user accounts in our oracle RDBS 9.0.4. Each user is granted one of two roles: READ_ROLE, WRITE_ROLE(which incorparates READ_ROLE) granting it access to our "MAIN" user/schema that OWNS all the tables, views, packages etc.
when logged on as one such user i can easily find the role/s granted to it with
1. select granted_role from user_role_privs;
or
2. select role from session_roles;
All well and good. However, i would like to create a re-usable function stored in a package owned by the MAIN schema which returns a boolean determining permission status for the current USER.
e.g.
function has_write_role return boolean is
v_username v$session.username%type;
begin
--check if current user has write role
select 'x' into vCheck
from session_roles s
where s.role = 'WRITE_ROLE';
--if you get this far then user has write role
return true;
exception
when no_data_found then
return false;
end;
However, i must be missing some crucial understanding of the way these things work because the above function returns false when logged on as any user with the WRITE_ROLE and, upon further investigation, the USER_ROLE_PRIVS and SESSION_ROLES views only contain data for the "MAIN" owner schema's roles and privileges. i.e. the above functions SQL is accessing these views as the package owner.
Is this is intentional behaviour or have i missed a trick? All users are granted execute on the package ("checks") in question and a public synonym exists for it,
so it is always called as below :
if checks.has_write_role then
do_some_plsql;
end if;
Any help or suggestions would be much appreciated.