Execute Immediate fails even with CREATE table grant

Question

I have a problem where I am creating a table using the execute immediate command in the stored procedure. However I get the error of "insufficient privileges". I checked other threads and made sure that the user has "CREATE TABLE" privilege granted to it. However I still keep seeing the same error.

SQL> select * from USER_SYS_PRIVS;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MYUSER            CREATE VIEW                              NO
MYUSER            UNLIMITED TABLESPACE                     NO

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

11 rows selected.

The Dummy procedure I created is :

create or replace procedure sp_dummy
   as
   begin
      execute immediate 'Create table Dummy99_99 (Dummy_Field number)';
   end sp_dummy;
   /

Detailed error :

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "MYUSER.SP_DUMMY", line 4
ORA-06512: at line 1

Is there something wrong I am doing ?

Fungi

Note that a PL/SQL procedure use the definer's rights by default, i.e. it runs with the rights of the user that compiled the procedure and not with the rights of the user that runs/invokes the procedure. This might or might not be relevant in your case. – Astronomical

This error is not relevant to " CREATE TABLE" privilege as CREATE TABLE Priv will be only executed on runtime. This is related to CREATE PROCEDURE privilege. Try GRANT CREATE PROCEDUTE TO MYUSER; and test again. Let me know if this helps – Monjan

@AvrajitRoy - the error is coming from execution of the procedure, not its creation - though that isn't stated clearly in the question. The error stack has PL/SQL errors, from the code block. If the create procedure priv was missing (and it isn't, you can see it in session_privs) you'd just get the ORA-01031 from the create procedure without the ORA-06512 traces. – Archi

yupps missed that trace :P. In this case it should run fine as CREATE TABLE Priv is present. This can happen only when trying to create table in another schema. In that case CREATE ANY TABLE priv is must to do that. – Monjan

@AvrajitRoy - but it isn't trying to create the table in another schema; it's because the privilege is granted through a role. – Archi

Yeaah got it. PRIV Granted through roles doesnt work in PLSQL :) – Monjan

Same request #996698 – Mezzorilievo

Answer 1

You only have create view granted directly to your user. The other system privileges you can see are coming from a role, and roles are disabled in definer's-rights stored procedures. Look in user_role_privs to see while roles you've been granted, and you can see which privileges each role gives you in role_sys_privs (with the role name as the grantee). There could be several layers of roles too.

You would see the same error if you did set role none before trying to create a table statically. Demo with minimal set-up:

create role myrole;
grant create session, create table, create procedure to myrole;
create user myuser identified by mypasswd;
grant myrole to myuser;
grant create view, unlimited tablespace to myuser;

Then as that user:

SQL> connect myuser/mypasswd
Connected.
SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MYUSER                         UNLIMITED TABLESPACE                     NO
MYUSER                         CREATE VIEW                              NO

2 rows selected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE

5 rows selected.

SQL> Create table Dummy99_99 (Dummy_Field number);

Table created.

SQL> drop table Dummy99_99 purge;

Table dropped.

SQL> set role none;

Role set.

SQL> Create table Dummy99_99 (Dummy_Field number);
Create table Dummy99_99 (Dummy_Field number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

And with your stored procedure version:

SQL> connect myuser/mypasswd
Connected.
SQL> create or replace procedure sp_dummy
  2  as
  3  begin
  4    execute immediate 'Create table Dummy99_99 (Dummy_Field number)';
  5  end sp_dummy;
  6  /

Procedure created.

SQL> exec sp_dummy;
BEGIN sp_dummy; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "MYUSER.SP_DUMMY", line 4
ORA-06512: at line 1

To be able to create the table dynamically from a stored procedure, your DBA will need to grant create table directly to your user:

grant create table to myuser;

Then trying the procedure again:

SQL> connect myuser/mypasswd
Connected.
SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MYUSER                         UNLIMITED TABLESPACE                     NO
MYUSER                         CREATE TABLE                             NO
MYUSER                         CREATE VIEW                              NO

SQL> exec sp_dummy;

PL/SQL procedure successfully completed.

SQL> desc Dummy99_99
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY_FIELD                                        NUMBER

Notice that user_sys_privs now shows that create table has been granted directly, which it didn't before, or in the question.

However, it is very unlikely you would ever really want to create objects dynamically, as the schema should be well defined and stable - changes of this type should be controlled and be part of a release process. But as an exercise, you need the direct grant.

Archi

Answer 2

When using execute immediate, procedure must explicitly tell oracle that it must run with privileges of a particular user.

AUTHID CURRENT_USER, to use the privileges of user running the procedure. AUTHID DEFINER, to use the privileges of owner of the procedure.

This is done using AUTHID option while creating a procedure.

CREATE OR REPLACE PROCEDURE PROC_NAME AUTHID CURRENT_USER
IS
.....

I faced a similar issue and got the understanding from: Execute Immediate within a stored procedure keeps giving insufficient priviliges error

Outweigh

Answer 3

If you are connected as myuser user, you should be able to create the procedure, and execute it to create the table.

The only privileges required to do the task are:

• CREATE SESSION
• CREATE TABLE
• CREATE PROCEDURE

And then execute the procedure after connecting to the user:

SQL> CREATE USER TEST IDENTIFIED BY TEST;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO TEST;

Grant succeeded.

SQL> conn TEST/TEST@pdborcl;
Connected.
SQL> show user
USER is "TEST"
SQL> CREATE OR REPLACE PROCEDURE sp_dummy
  2  AS
  3  BEGIN
  4    EXECUTE immediate 'Create table Dummy99_99 (Dummy_Field number)';
  5  END sp_dummy;
  6  /

Procedure created.

SQL> EXEC sp_dummy;

PL/SQL procedure successfully completed.

SQL> select * from dummy99_99;

no rows selected

Pyrophosphate

Answer 4

The necessary concessions are as follows:

GRANT CREATE TABLE TO "USER";
GRANT EXECUTE ANY PROCEDURE TO "USER" ;

Revenge

Welcome to Stack Overflow! While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, as this reduces the readability of both the code and the explanations! – Ludwog