Skip to Main Content
  • Questions

  • current_user or session user problem with package

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, anthony.

Asked: September 10, 2004 - 1:02 pm UTC

Last updated: September 24, 2008 - 6:10 pm UTC

Version: 9.2.04

Viewed 1000+ times

You Asked

Hello Tom,

(Oracle 9.2.0.4 on Solaris 8)

this as dogged me for two days but maybe you can see it immediately . The question is what's happening in this correlated sub query using user_role_privs for a package called NOT by the owner (no AUTHID CURRENT_USER) It always seems to working as though the user_role_privs was of the invoker and not the owner I have checked this will a query from you site and we have current_user=CIS ,session_user=MARRASG, schema_user=CIS. Also, if I remove the CIS_RO role from the user MARRASG it nolonger returns any rows but it still recognises the tables upon which MARRASG has no privileges !??? So it seems the package is being run as the owner CIS but the user_role_privs table is the invokers! The worrying thing this supposed to be working in production right now! It's all very confusing. The query itself doesn't make sense as using user_role_privs will be looking at the owners role privs as there is no AUTHID CURRENT_USER as mentioned .. but even if there existed a AUTHID CURRENT_USER the invoker couldn't see the tables as there's no synonyms and no grants on these tables. Needless to say I inherited this code and this question came about because this query was returning what was expected and then we had to port a later release ( this package wasn't changed) and on the new unix machine, new oracle binaries, new database and a full db import from production with release scripts run on top we found it no longer worked as expected i.e it didn't return any menu items
for MARRASG user .. and this when you look at this below is what one would really expect so obviously we then started to question why it was working in the first place and thus my post.. but it is 'working'on two other setups


CIS user -OWNER OF PACKAGE !!!
+++++++++++++++++++++++

Username Obj Owner Obj Obj Priv Sys Priv Granted Role
-------------- ------------- ------------------------- ---------- ------------------------ --------
CIS SYS AUD$ SELECT
CIS SYS DBA_ROLE_PRIVS SELECT
CIS SYS DBA_USERS SELECT
CIS SYS UDUMP READ
CIS ALTER USER
CIS UNLIMITED TABLESPACE
CIS CONNECT
CIS PLUSTRACE
CIS RESOURCE

MARRASG user INVOKER OF PACKAGE
+++++++++++++++++
Username Obj Owner Obj Obj Priv Sys Priv Granted Role
-------------- -------------- ------------------------- ---------- ------------------------ --------
MARRASG CREATE SESSION
MARRASG CIS_RO

==========================================================
-- PROCEDURE/query IN PACKAGE
==========================================================
+++ from spec +++
+++++++++++++++++


Type t_menu is ref cursor;

Procedure i_get_menu_sp(p_menu_items OUT t_menu);

++ from body ++++
+++++++++++++++++
PROCEDURE i_get_menu_sp(p_menu_items OUT t_menu) IS

BEGIN

OPEN p_menu_items FOR
SELECT DISTINCT
mi.menu_item_id,
mi.description,
mi.parent,
mi.target,
mi.href
FROM i__menu_item mi, i__role_permission rp
WHERE mi.menu_item_id = rp.menu_item_id
AND EXISTS (SELECT granted_role
FROM user_role_privs
WHERE granted_role = rp.role_id);


END i_get_menu_sp;

==========================================================

SQL> select * from session_roles;

ROLE
------------------------------
CIS_RO

SQL> show user
USER is "MARRASG"
SQL>
SQL> variable c refcursor
SQL> set autoprint on
SQL> exec i_application_pkg.i_get_menu_sp(p_menu_items => :c)

PL/SQL procedure successfully completed.


MENU_ITEM_ID DESCRIPTION PARENT TARGET HREF ROLE_ID
------------ ------------------------------ ---------- -------------------- ------------------------
5 Raw Data Access & Export ContentFrame DataAccess.jsp CIS_RO
6 Reports ContentFrame Reports.jsp CIS_RO
7 Administration CIS_RO
8 Change Password 7 ContentFrame ChangePassword.jsp CIS_RO
11 Help CIS_RO
12 User Help 11 Help Help.html CIS_RO
13 About CIS 11 ContentFrame About.html CIS_RO
14 Exit ContentFrame Logout.jsp CIS_RO

8 rows selected.


OUTPUT NOT AS EXPECTED AS CIS USER DOES NOT HAVE THE ROLE CIS_RO GRANTED !!


AND ??????????????????????????????????????????


PROCEDURE i_get_menu_sp(p_menu_items OUT t_menu) IS

BEGIN

OPEN p_menu_items FOR
SELECT DISTINCT
mi.menu_item_id,
mi.description,
mi.parent,
mi.target,
mi.href
FROM i__menu_item mi, i__role_permission rp
WHERE mi.menu_item_id = rp.menu_item_id
AND EXISTS (SELECT granted_role
FROM user_role_privs
WHERE granted_role = 'CIS_RO'); <===== THE OWNER CIS REMEMBER DOES NOT HAVE CIS_RO ROLE
BUT MARRASG DOES .. and works as expected.


END i_get_menu_sp;

SQL> exec i_application_pkg.i_get_menu_sp(p_menu_items => :c)

PL/SQL procedure successfully completed.


no rows selected <======== AS EXPECTED


TIA for your time.
Ant


and Tom said...

the USER_ views all have a predicate like this:

....
where sa.grantee# in (userenv('SCHEMAID'),1) and sa.grantee#=ud.user#(+)
and sa.privilege#=ud.role#(+) and u1.user#=sa.grantee#
and u2.user#=sa.privilege#
group by decode(sa.grantee#,1,'PUBLIC',u1.name),u2.name
........

userenv('schemaid') in a DEFINER RIGHTS routine (the default) is always the "owner" of the procedure.

Your choices are:

a) get access to the necessary DBA_ views for this account. DBA_ views do not have this filter (so you would have to supply your own filter by username)

b) use INVOKERs rights routine (authid current_user).


I would strongly encourage (a) and strongly discourage (b)




Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

didn't make my question clear enough?

anthony, September 13, 2004 - 4:47 am UTC

HI Tom, thanks for the quick reply.

Sorry for the low review but it is only because..
I think I didn't make my question absolutely clear.
I am familiar with the user_* roles. This post was to do with the observation that Oracle was using user_role_privs in an unexpected way.

Firstly I show how it returns a result set when using a correlated sub-query referring to user_role_privs when the
owner CIS does NOT have the role that the sub-query is filtering for. BUT if I hard code the role 'CIS_RO' then it seems to 'work' as expected i.e. it doesn't return any result because the owner CIS does not have this role granted.. this is the expected behaviour. Given that I understand your answer below then either there Oracle is not treating this scenario correctly or I am misunderstanding how the correlated sub-query is working. If I drop the role from the invoker MARRASG then using the correlated sub-query no result set is returned suggesting that the use of user_role_privs is using the invoker's roles NOT the owner's roles.
Why when it is not hard coded and is part of the correlated sub-query does it find the CIS_RO role in the user_role_privs but when I hard code it it doesn't find it in user_role_privs?


Hope I have now made my question clearer.
Thanks Tom.





Tom Kyte
September 13, 2004 - 8:16 am UTC 

Oracle was using, is using USER_ROLE_PRIVS in the designed fashion -- in the expected fashion.

a stored procedure runs with the privs of the DEFINER (it is called definers rights) by default.



tell you what -- you give me a

a) simple
b) CONCISE
c) yet 100% complete test case (in the fashion I always give you guys)

that anyone on the planet could run
that clearly demonstrates only exactly what you think the issue to be

I'll be glad to take a look at it.  Your script for example would have 2 create user commands, a connect to user 1, creat tables, insert into, create procedure.  it would then demonstrate what you get (and state what you think you should get) log in as other user and so on.

You know, exactly what I would have to do in order to say anything, demonstrate anything, prove anything




The page you used to get here in the first place, to ask the question said things like:

DONT give a describe of a table
DO give me a create table

DONT give me a "select * from table"
DO give me insert intos


So, give us a clear example -- that anyone could run -- that demonstrates what you believe to be "wrong" or "unintuitive" behavior -- and we can comment maybe.

<b>this is how I expecte user_role_privs to work:</b>


ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p
  2  as
  3  begin
  4          for x in ( select * from user_role_privs )
  5          loop
  6                  dbms_output.put_line( x.username || ', ' || x.granted_role );
  7          end loop;
  8  end;
  9  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant execute on p to public;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec p
OPS$TKYTE, CONNECT
OPS$TKYTE, DBA
PUBLIC, PLUSTRACE
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> @connect scott/tiger
ops$tkyte@ORA9IR2> set termout off
scott@ORA9IR2> set termout on
scott@ORA9IR2> exec ops$tkyte.p
OPS$TKYTE, CONNECT
OPS$TKYTE, DBA
PUBLIC, PLUSTRACE
 
PL/SQL procedure successfully completed.

<b>ops$tkyte, regardless of who invoked it</b>

a few lines to help replication

anthony, September 13, 2004 - 10:13 am UTC

Tom,
Hope this enough for you.
I managed to replicate this using just dba_role_privs and user_role_privs. I just ran this line by line in order and managed to replicate my 'problem' . As I say it is either a bug or my misunderstanding of the workings of the correlated sub-query. Please run once with the correlated sub-query and again with the role hard coded.

-- BEGIN ----------

REM -- as DBA user --
REM +++++++++++++++++++

create user usr_a identified by password
/
grant create table to usr_a
/

grant connect to usr_a
/
grant resource to usr_a
/
grant select on dba_role_privs to usr_a
/

alter user usr_a default tablespace ts_user
/

alter user usr_a temporary tablespace ts_temp
/
grant create public synonym to usr_a
/

create user usr_b identified by password
/
grant create session to usr_b
/

alter user usr_a temporary tablespace ts_temp
/

create role ro_role
/

grant ro_role to usr_b
/

REM -- as usr_a --
REM +++++++++++++++
create or replace package pack_a
as

Type t_menu is ref cursor;


Procedure get_menu_p(p_menu_items OUT t_menu);
end pack_a;
/

create or replace package body pack_a
as

Procedure get_menu_p(p_menu_items OUT t_menu)
is

BEGIN

-- Correlated sub-query
/*
open p_menu_items for
SELECT distinct granted_role
FROM dba_role_privs rp
where EXISTS (SELECT granted_role
FROM user_role_privs
WHERE granted_role = rp.granted_role);
*/

-- Role hard coded
open p_menu_items for
SELECT distinct granted_role
FROM dba_role_privs rp
where EXISTS (SELECT granted_role
FROM user_role_privs
WHERE granted_role = 'RO_ROLE');


END get_menu_p;
end pack_a;
/

create public synonym pack_a for usr_a.pack_a
/

grant execute on pack_a to ro_role
/

REM execute it as usr_b
REM +++++++++++++++++++

variable c refcursor
set autoprint on
exec pack_a.get_menu_p(p_menu_items => :c)

-- END -------------

Maybe I'm overlooking something really simple here..
Anticipating your response.
Thanks a lot.

Tom Kyte
September 13, 2004 - 1:08 pm UTC

FROM dba_role_privs rp
where EXISTS (SELECT granted_role
FROM user_role_privs
WHERE granted_role = 'RO_ROLE');

is either like

where 1=0

or

where 1=1

the subquery isn't correlated. But -- I have detected what appears to be incorrect behavior when dba_role_privs is used with a correlated subquery against user_role privs -- but only in this usage pattern for some reason. Here is my test case:

@connect "/ as sysdba"
drop user usr_a cascade;
drop user usr_b cascade;


grant connect, resource to usr_a identified by usr_a;
grant select on dba_role_privs to usr_a;

grant create session to usr_b identified by usr_b;

@connect usr_a/usr_a;

create or replace procedure p( p_cursor in out sys_refcursor )
as
l_cursor sys_refcursor;
l_text long;
l_query long := '
SELECT distinct :x || granted_role
FROM dba_role_privs rp
where EXISTS (SELECT granted_role
FROM user_role_privs
WHERE granted_role = rp.granted_role)
';
begin
open p_cursor for l_query using 'Ref Cursor: ';
open l_cursor for l_query using 'In Procedure: ';
loop
fetch l_cursor into l_text;
exit when l_cursor%notfound;
dbms_output.put_line( l_text );
end loop;
close l_cursor;
end;
/

variable x refcursor
set serveroutput on
exec p(:x)
print x
grant execute on p to public;

@connect usr_b/usr_b;
exec usr_a.p(:x)
print x




........ when run:



Procedure created.

In Procedure: CONNECT
In Procedure: RESOURCE

PL/SQL procedure successfully completed.


:X||GRANTED_ROLE
--------------------------------------------------------------
Ref Cursor: CONNECT
Ref Cursor: RESOURCE


Grant succeeded.

In Procedure: CONNECT
In Procedure: RESOURCE

PL/SQL procedure successfully completed.


no rows selected



That last select is "wrong"... the schemaid is getting messed up in the execution of the correlated subquery.

Please file a tar with support with this very simple testcase that clearly shows the issue.


Your workaround (to avoid the schemaid issue) is:

l_query long := '
SELECT distinct :x || granted_role
FROM dba_role_privs rp
where EXISTS (SELECT granted_role
FROM dba_role_privs
WHERE granted_role = rp.granted_role
and grantee = ''USR_A'' )
';


getting there...the root of the problem

anthony, September 14, 2004 - 4:30 am UTC

Tom,

thanks so much for looking into this. I'm relieved you were able to replicate the experience I was having.
The correlated sub-query part was part of my test case
It was to be run after the hard-coded bit. At least now I know I'm not going mad.... well at least this not being the cause.

The funny thing is that this was actually discovered whilst porting a similar query ( like the one in first post) to another box running 9.2.0.1 and there we seem not to have had rows returned which was correct as the owner of the schema did not have the role granted. I think further investigation will be needed as no dba_role_privs where being used just two application tables with menu items in one table and a foreign key to the second table containing menu_item_id and the role that allowed access to those menu items. There was a join on these two tables to retrieve the menu items for the user ( not package owner) but the filter (EXISTS clause) was a correlated sub-query using user_role_privs i.e. only those menu lines would be showed for which the user was entitled to see given the role(s) she was assigned. I think given this requirement by the application user_role_privs was the wrong choice anyway. In any case , you have clearly shown there is a problem here. I may be in touch again if I can get a clearly reproduceable example or hear enlighteneing information from oracle support.

A tar as been filed

Thanks again Tom much appreciated.
anthony

Tom Kyte
September 14, 2004 - 8:59 am UTC

the clearly reproducible example is above, just give them my test case -- should be all they need.

Need a help

Aarti Shinde, August 26, 2005 - 3:47 am UTC

Hello,
I have written a function which will tell me whether a user has the particular role assigned or not.
I am able to select from DBA_ROLE_PRIVS table on sqlprompt but the same in procedure or function is not working. When I grant SELECT on DBA_ROLE_PRIVS it works. Why is this happening? I cannot give DBA_ROLE_PRIVS to all the users.

CREATE OR REPLACE FUNCTION F_GetRole (
P_UserName VARCHAR2,
P_Module VARCHAR2)
RETURN BOOLEAN
IS
CURSOR USER_ROLES
IS
SELECT GRANTED_ROLE
FROM SYS.DBA_ROLE_PRIVS
WHERE GRANTEE = P_UserName;
REC_USER_ROLES USER_ROLES%ROWTYPE;
ln_Count NUMBER;
ls_Role VARCHAR2(10);
BEGIN
SELECT DECODE(P_Module,'CSW','CSWROLE','USERKIT','PRINTROLE',NULL)
INTO ls_Role
FROM DUAL;
IF NVL(ls_Role,'*') = '*' THEN
RETURN FALSE;
END IF;

SELECT COUNT(1)
INTO ln_Count
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = P_UserName
AND GRANTED_ROLE = ls_Role;
IF ln_Count = 1 THEN
RETURN TRUE;
END IF;
OPEN USER_ROLES;
LOOP
FETCH USER_ROLES INTO REC_USER_ROLES;
EXIT WHEN USER_ROLES%NOTFOUND;
SELECT COUNT(1)
INTO ln_Count
FROM (
SELECT GRANTED_ROLE
FROM ROLE_ROLE_PRIVS
START WITH ROLE = REC_USER_ROLES.GRANTED_ROLE
CONNECT BY GRANTED_ROLE = ROLE )
WHERE GRANTED_ROLE = ls_Role;
IF ln_Count = 1 THEN
RETURN TRUE;
END IF;
END LOOP;
CLOSE USER_ROLES;
RETURN FALSE;
END;


Tom Kyte
August 26, 2005 - 8:23 am UTC

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

You have to grant select on dba_role_privs to the OWNER of the procedure directly.

Why not use ALL_* views?

Denis Roy, September 23, 2008 - 10:39 am UTC

The original poster was querying the USER_* views, you recommended that he use the DBA_* views to avoid the predicate issue, but this required direct grants to the DBA_* objects. I thought, if I'm logged in as a privileged user why not use a procedure which uses the ALL_* objects with definer rights and then grant execute on the procedures to other users. I didn't even get that far because when I gave it a try, my stored procedures returned an unexpected result set if they where not defined with invoker rights.

I'm trying to understand why a query executed in a procedure as the owner with definer rights returns an unexpected result set, when the same procedure defined with invoker rights returns the expected results.

Since the runtime user = definer = invoker in this case shouldn't we see the same results? Could you please shed some light on the subject?

 
ops$oracle@adev>begin
 for x in (select owner, count(*) my_count from all_tab_columns group by owner) loop
 dbms_output.put_line(x.owner||' '||x.my_count);
 end loop;
end;
/

ALEX_OWNER 2454
DBSNMP 166
DROY 42
EPM81_16_10_OWNER 1911
EPM81_OWNER 2812
EPM90_OWNER 3005
EPM91_OWNER 2445
EXFSYS 726
FLOWS_030000 5373
FLOWS_FILES 22
OPS$ORACLE 203
OUTLN 43
SCORM 363
SRDEMO 44
SYS 40655
SYSMAN 3792
SYSTEM 1404
TSMSYS 7
WMSYS 909
XDB 72


Anonymous block returns expected results.

ops$oracle@adev> create procedure test1 as
begin
 for x in (select owner, count(*) my_count from all_tab_columns group by owner) loop
  dbms_output.put_line(x.owner||' '||x.my_count);
 end loop;
end;
/

Procedure created.

ops$oracle@adev> exec test1
EXFSYS 352
FLOWS_030000 1869
OPS$ORACLE 203
SYS 11923
SYSTEM 57
WMSYS 435
XDB 22

PL/SQL procedure successfully completed.


Definer rights returns a different result set ?

ops$oracle@adev> create procedure test2 authid current_user as
begin
 for x in (select owner, count(*) my_count from all_tab_columns group by owner) loop
  dbms_output.put_line(x.owner||' '||x.my_count);
 end loop;
end;
ops$oracle@adev> /

Procedure created.

ops$oracle@adev> exec test2
ALEX_OWNER 2454
DBSNMP 166
DROY 42
EPM81_16_10_OWNER 1911
EPM81_OWNER 2812
EPM90_OWNER 3005
EPM91_OWNER 2445
EXFSYS 726
FLOWS_030000 5373
FLOWS_FILES 22
OPS$ORACLE 203
OUTLN 43
SCORM 363
SRDEMO 44
SYS 40655
SYSMAN 3792
SYSTEM 1404
TSMSYS 7
WMSYS 909
XDB 72


Invoker rights returns expected results set.
Tom Kyte
September 24, 2008 - 6:10 pm UTC

definers rights = base rights of the owner MINUS roles

invokers rights = currently enabled roles of invoker PLUS base rights of invoker

so, when you run a procedure, in definers rights - roles are not enabled.

user_* = your stuff
all_* = your stuff and stuff you can see given your current privs PLUS enabled roles
dba_* = all stuff.


ops$tkyte%ORA10GR2> select count(*) from all_objects;

  COUNT(*)
----------
     49858

ops$tkyte%ORA10GR2> set role none
  2  ;

Role set.

ops$tkyte%ORA10GR2> select count(*) from all_objects;

  COUNT(*)
----------
     40808


so, in a stored procedure by default - all_objects "has less stuff", because you can see less stuff - no roles.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library