EXECUTE IMMEDIATE ORA-01031: insufficient privileges

EXECUTE IMMEDIATE Feature allows you to execute DYNAMIC SQL Statements inside the PL/SQL Procedure, but here i show you interesting thing about this feature.
Here is Video of these procedures
let’s demonstrate small demo

SQL> create user qobesa identified by qobesa;

User created.

SQL>  grant connect,resource to qobesa;

Grant succeeded.

Our user called qobesa has privileges to connect database, create table and others because resource role contains privileges such as ‘create table’, test it

sqlplus qobesa/qobesa

SQL> create table test (id number);

Table created.

let’s create simple procedure which creates table with execute immediate

SQL> create or replace procedure test_proc
  2  is
  3  begin
  4  EXECUTE IMMEDIATE 'CREATE TABLE TEST1(ID NUMBER)';
  5  end;
  6  /

Procedure created.

and execute it

SQL> exec test_proc
BEGIN test_proc; END;

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

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. Then, What is stopping us?
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.
Therefore we can grant create table privilege to our user and will work, test it

SQL> conn sys / as sysdba
Enter password: 
Connected.
SQL> grant create table to qobesa;

Grant succeeded.
SQL> conn qobesa/qobesa
Connected.
SQL> exec test_proc

PL/SQL procedure successfully completed.

It’s all, good luck.

One thought on “EXECUTE IMMEDIATE ORA-01031: insufficient privileges

  1. Guru

    Kindly use the following option ..it will invoke user rights and executes without error.

    create or replace procedure test_proc
    AUTHID CURRENT_USER =====>
    is
    begin
    EXECUTE IMMEDIATE ‘CREATE TABLE TEST1(ID NUMBER)’;
    end;
    /

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s