Re: PL/SQL privileges

"Jon" <aaa_at_bbb.ccc> wrote in message news:<c8hvrf$6mc$1_at_unbe.sarenet.es>...
> Hi,
>
> A user has "create any table" privileges and the same user has a procedure
> with
>
> "execute immedate 'create table ...'"
>
> when the user tries to run the procedure, it returns the following error
>
> "ORA-01031: insufficient privileges"
>
> thanks

The user that has "create any table" privileges... is that through a role or is the system privilege granted directly to the user? If the privilege is only granted through a role, then it will not work. You will need to grant "create any table" directly to the user. (This also holds true with creating views.)

In this example, I have a user named scott2 that has a role named "DEV" and that role has "create any table". You can see that he can create a table with no problem:

  SQL> create table test1 (id number);

  Table created.

Scott2 also owns a procedure named "CR_TEST":

  create or replace procedure cr_test as   begin
    execute immediate 'create table test(id number)';   end;
  /

  Procedure created.

Now, scott2 trys to run the procedure:

  SQL> exec cr_test;
  BEGIN cr_test; END;

  *
  ERROR at line 1:

  ORA-01031: insufficient privileges
  ORA-06512: at "SCOTT2.CR_TEST", line 4
  ORA-06512: at line 1

Now, let's grant "create any table" directly to SCOTT2:

  SQL> connect system/manager
  Connected.
  SQL> grant create any table to scott2;

  Grant succeeded.

Finally, let's connect back as scott2 and try to run the procedure:

  SQL> connect scott2/tiger
  Connected.
  SQL> exec cr_test;

  PL/SQL procedure successfully completed.

Hope this helps,
Jeff Received on Thu May 20 2004 - 08:47:48 CDT