2 Replies Latest reply on Mar 12, 2013 2:34 PM by User374238

    Creating objects only with role grants.

      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.
          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.
            Thanks for the answer.