This content has been marked as final. Show 2 replies
Is there a way? Yes. But it's probably not what you want...
will work. Any user that wants to execute the A.PROC procedure will need to have the ability to call B.GET_DATE (either directly or via a role).
SQL> ed Wrote file afiedt.buf 1 create or replace procedure a.proc 2 authid current_user 3 is 4 l_dt date; 5 begin 6 execute immediate 'select b.get_date from dual' into l_dt; 7 dbms_output.put_line( l_dt ); 8* end; SQL> / Procedure created. SQL> set serveroutput on; SQL> exec a.proc; 06-FEB-13 PL/SQL procedure successfully completed.
In general, since roles can be enabled or disabled in a session, password protected, made default or non-default, etc. you can't use them when you're creating persistent objects. Otherwise, if A has two sessions open and in one session B_STUFF is enabled and in the other the role is disabled, Oracle would have a very hard time figuring out whether the A.PROC procedure should be valid or not. And then what happens when both of those sessions are terminated-- which set of roles should Oracle then use for A's persistent objects? That's why Oracle generally depends on privileges that are granted directly to a user when you're trying to create persistent objects.
Thanks for the answer.