This discussion is archived
2 Replies Latest reply: Mar 12, 2013 7:34 AM by user374238 RSS

Creating objects only with role grants.

user374238 Newbie
Currently Being Moderated
Good afternoon, everybody.

I'm having a problem to compile some procedures in a scheme, let's call it scheme A.

This scheme A is receiving privileges to execute a function(B.get_date) of other scheme, let's call it scheme B, through a role B_STUFF.
When I try to compile a procedure from scheme A that uses the function B.get_date, it doesn't compile because there are
insufficient privileges for scheme A to execute that function, but I already gave that privilege through the role B_STUFF.
Nevertheless it only works when I give direct grant execute on function B.get_date to schema A.

Here is the example of my problem:

create function B.get_date return date
return sysdate;

grant execute on function B.get_date to B_STUFF;

grant B_STUFF to A;

create procedure A.proc
....uses the function B.get_date....

Invalid object.

Is there a way to do this without having to give direct privilege of an object to a schema?

Thanks for the attention...
  • 1. Re: Creating objects only with role grants.
    Justin Cave Oracle ACE
    Currently Being Moderated
    Is there a way? Yes. But it's probably not what you want...
    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;
    PL/SQL procedure successfully completed.
    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).

    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.

  • 2. Re: Creating objects only with role grants.
    user374238 Newbie
    Currently Being Moderated
    Thanks for the answer.


  • Correct Answers - 10 points
  • Helpful Answers - 5 points