Execute Immediate Command Fails with DBA role: ORA-01031: insufficient privileges

advertisements

_____________________________________________________________________________________________________________________

Error Description:

The procedure with execute immediate command is failing with ORA-01031: insufficient privileges. The procedure is trying to create a table and drop the table. The owner of the procedure has DBA role. Still the user cannot create the table or drop the table through execute immediate command. The user is able to create and drop the table using SQL*Plus prompt.

Solution Description

Even though DBA role is given to the user, you have to explicitly grant the privileges whichever you used in the procedure. If you used create table command in execute immediate, you have to grant create any table privilege explicitly. See one example here.

1. Granting the DBA to user

SQL> connect / as sysdba

Connected.

SQL> grant dba to scott;

Grant succeeded.

2. Creating the procedure with execute immediate.

SQL> connect scott

Enter password:

Connected.

SQL> create or replace procedure testproc as

srpt varchar2(2000);

begin

srpt:='create table test(col1 number(2))';

execute immediate srpt;

srpt:='drop table test';

execute immediate srpt;

end; 

/

Procedure created.

3. Executing the procedure and giving the error.

SQL>  exec testproc;

BEGIN testproc; END;

*

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "SCOTT.TESTPROC", line 5

ORA-06512: at line 1

4. Again granting the specific privileges to the user and execution succeded.

SQL> connect / as sysdba

Connected.

SQL> grant create any table, drop any table to scott;

Grant succeeded.

SQL> connect scott

Enter password:

Connected.

SQL> show user

USER is "SCOTT"

SQL>  exec testproc;

PL/SQL procedure successfully completed.

_____________________________________________________________________________________________________________________

Website Stats