Google it ....

Monday, May 5, 2014

EXECUTE IMMEDIATE ORA-01031: insufficient privileges

EXECUTE IMMEDIATE Feature allows you to execute DYNAMIC SQL Statements inside the PL/SQL Procedure, but here I''ll show you interesting thing about this feature.
Here is Video of these procedures - EXECUTE IMMEDIATE ORA-01031: insufficient privileges
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.

3 comments:

  1. 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;
    /

    ReplyDelete
    Replies
    1. this method cannot be used if the procedure is stored in a package. any solution for that?

      Delete
    2. In blog post described method also working on procedures which is in packages. here is little test:
      [oracle@testdb ~]$ sqlplus / as sysdba

      SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 27 16:10:40 2017

      Copyright (c) 1982, 2011, Oracle. All rights reserved.


      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, Automatic Storage Management, OLAP, Data Mining
      and Real Application Testing options

      SQL> create user qobesa identified by qobesa;

      User created.

      SQL> grant connect, resource to qobesa;

      Grant succeeded.

      SQL> connect qobesa/qobesa;
      Connected.
      SQL> create table test(id number);

      Table created.

      SQL> create or replace package test_pack as
      procedure test_proc;
      end test_pack;
      / 2 3 4

      Package created.

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

      Package body created.

      SQL> exec test_pack.test_proc;
      BEGIN test_pack.test_proc; END;

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


      SQL> exit
      Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, Automatic Storage Management, OLAP, Data Mining
      and Real Application Testing options
      [oracle@testdb ~]$ sqlplus / as sysdba

      SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 27 16:11:24 2017

      Copyright (c) 1982, 2011, Oracle. All rights reserved.


      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, Automatic Storage Management, OLAP, Data Mining
      and Real Application Testing options

      SQL> grant create table to qobesa;

      Grant succeeded.

      SQL> connect qobesa/qobesa
      Connected.
      SQL> exec test_pack.test_proc;

      PL/SQL procedure successfully completed.

      SQL> select table_name from user_tables;

      TABLE_NAME
      ------------------------------
      TEST
      TEST1

      SQL>

      Delete