EXECUTE IMMEDIATE ORA-01031: insufficient privileges

An interesting experiment on EXECUTE IMMEDIATE Oracle Feature. It allows you to execute DYNAMIC SQL Statements inside the PLSQL Procedure. For SYNTAX and more information about this you can find out in Oracle Documentation.

Now we are going to Create one Database User and then we will write one PLSQL procedure with EXECUTE IMMEDIATE statement. Lets see,

– Login as SYSDBA and Create the user called ‘sumit’

SQL> create user sumit identified by sumit;

User created.

SQL> grant connect,resource to sumit;

Grant succeeded.

Database user ‘sumit’ has been created successfully and granted CONNECT, RESOURCE roles to sumit user.

The user called ‘sumit’ has been created and granted some roles. Now connect as sumit user and Create a PLSQL procedure with
EXECUTE IMMEDIATE Statement.

SQL> conn sumit/sumit
 Connected.


 /*  Create a procedure to create table called t with attributes id,name with

          EXECUTE IMMEDIATE Statement */

SQL> create or replace procedure p
   2             is
   3             begin
   4              EXECUTE IMMEDIATE ‘CREATE TABLE T(ID NUMBER, NAME VARCHAR2(20))’;
   5             end;
   6             /

Procedure created.

SQL>  — Created the procedure called “P”. Let me execute this procedure…
 SQL> execute p;
 BEGIN p; END;

*
 ERROR at line 1:
ORA-01031: insufficient privileges
 ORA-06512: at “sumit.P”, line 4
 ORA-06512: at line 1

Oops!, got an error “insufficient privileges” - Do we have this privilege?, YES. we have. It has been grouped under RESOURCE role and the RESOURCE role has been granted to this user (FYI: We have queried the Data Dictionary table to see the privileges that are mapped under RESOURCE role. SEE ABOVE). Then, What is stopping us?

Let me grant the CREATE TABLE privilege directly to the sumit user.

SQL> conn sys@orcl as sysdba
 Enter password: *******
 Connected.
 SQL> grant CREATE TABLE to sumit;

Grant succeeded.

SQL> conn sumit/sumit
 Connected.
 SQL> — Let me execute the procedure now
 SQL> execute p;

PL/SQL procedure successfully completed.

SQL> — Wow! Table ‘T’ got created.
 SQL> desc t;
  Name                                      Null?    Type
  —————————————————————————–
  ID                                                       NUMBER
  NAME                                                 VARCHAR2(20)

 The Conculsion is,

In STORED PROCEDURE, roles are DISABLED. Hence any PRIVILEGES granted by a ROLE to USER, will NOT be in effect. The PRIVILEGES MUST be directly granted to the user.

Or u can user the  "authid current_user" clause.