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

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.

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

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

  1. 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.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google