Execute Immediate Within a Stored Procedure Keeps Giving Insufficient Priviliges Error

Execute Immediate within a stored procedure keeps giving insufficient priviliges error

Oracle's security model is such that when executing dynamic SQL using Execute Immediate (inside the context of a PL/SQL block or procedure), the user does not have privileges to objects or commands that are granted via role membership. Your user likely has "DBA" role or something similar. You must explicitly grant "drop table" permissions to this user. The same would apply if you were trying to select from tables in another schema (such as sys or system) - you would need to grant explicit SELECT privileges on that table to this user.

Execute immediate within Oracle Procedure

Inside PL/SQL blocks you have only privileges which are granted to you directly. Privileges which are granted through a role (e.g. DBA) do not apply inside a PL/SQL block, i.e. a procedure.

Grant privilege directly, for example GRANT ALTER ANY TABLE TO SASDBA;

Insufficient INHERIT PRIVILEGES for a stored procedure ORACLE

Your problem is this clause:

  AUTHID CURRENT_USER

This means the user who executes the procedure does so with their own privileges. So a user who is not META cannot run the procedure unless they have the DROP ANY TABLE privilege, in which case they don't need to run the procedure as they can truncate the table anyway.

The solution is to declare the procedure with

  AUTHID DEFINER

Now the procedure executes with the privileges of its owner - META - who presumably owns the target table. Except they don't. The table is actually owned by STAGE. So STAGE needs to own the procedure as well.

As it happens, DEFINER rights are the default so we don't need to explicitly define them, except for clarity.

CREATE PROCEDURE STAGE.sp_truncate_tablex
AS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE STAGE.Tablex';
END;

Can I prevent execute immediate from inserting on a read-only user

Check the following example. Shortly, keyword is AUTHID CURRENT_USER while creating that PL/SQL program unit.

Connected as MIKE (who owns table and procedure and grant SCOTT privileges to use them):

SQL> show user
USER is "MIKE"
SQL>
SQL> create table test (id number);

Table created.

SQL> create or replace procedure p_test
  2    authid current_user
  3  is
  4  begin
  5    execute immediate 'insert into mike.test values (1)';
  6  end;
  7  /

Procedure created.

SQL> exec p_test;

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID
----------
         1

SQL> grant select on test to scott;

Grant succeeded.

SQL> grant execute on p_test to scott;

Grant succeeded.

SQL>

Connected as SCOTT:

SQL> show user
USER is "SCOTT"
SQL>
SQL> select * From mike.test;

        ID
----------
         1

SQL> exec mike.p_test;
BEGIN mike.p_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "MIKE.P_TEST", line 5
ORA-06512: at line 1


SQL>

---

Without it, SCOTT is able to insert values into MIKE's table:

SQL> connect mike/lion@orcl
Connected.
SQL> create or replace procedure p_test
  2  is                                      --> no more authid current_user
  3  begin
  4    execute immediate 'insert into mike.test values (2)';
  5  end;
  6  /

Procedure created.

SQL> connect scott/tiger@orcl
Connected.
SQL> exec mike.p_test;

PL/SQL procedure successfully completed.

SQL> select * From mike.test;

        ID
----------
         1
         2

SQL>

Oracle procedure within package with alter table throws ORA-01031:insufficient privileges

@Vivek you can add AUTHID DEFINER/CURRENT_USER within Package specification only, I think it will work after that, below is your package specification should be:

CREATE OR REPLACE PACKAGE DEVELOPER.DDL_PACKS AUTHID CURRENT_USER
AS

   PROCEDURE disbcons;

END;

Problem with privileges when creating sequences in procedure

That's because SYSTEM was granted the DBA role which allows it to create a sequence (among other things). But, privileges acquired via roles will work in anonymous PL/SQL blocks (and at SQL level, of course), but will not work in named stored procedures - which is what you have.

Solution? Grant privilege directly to user (system in this case).

Demo:

SQL> show user
USER is "SYSTEM"
SQL> CREATE OR REPLACE PROCEDURE my_proc_1
  2  IS
  3     l_seq_start_with number := 7;
  4  begin
  5     begin
  6        execute immediate 'DROP SEQUENCE MY_SEQ';
  7     exception
  8        when others then null;
  9     end;
 10     execute immediate
 11        'CREATE SEQUENCE MY_SEQ START WITH '
 12           ||to_char(l_seq_start_with)
 13           ||' INCREMENT BY 1 NOCACHE';
 14  end;
 15  /

Procedure created.

SQL> exec my_proc_1;
BEGIN my_proc_1; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.MY_PROC_1", line 10
ORA-06512: at line 1

Failed, as you already know. Grant the privilege explicitly:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> grant create sequence to system;

Grant succeeded.

Back to system:

SQL> connect system
Enter password:
Connected.
SQL> exec my_proc_1;

PL/SQL procedure successfully completed.

SQL> select my_seq.nextval from dual;

   NEXTVAL
----------
         7

SQL>

Now it works.

---

Related Topics