DDL execution from inside PL/SQL is failing

Question

I am executing a DDL from inside a stored proc :

v_sql_stmt := 'ALTER INDEX PK_TEST REBUILD ONLINE';
EXECUTE IMMEDIATE (v_sql_stmt);

All objects - the index, the table (on which the index is built), and the proc (with the above 2 lines) belong to the same schema. Also, while executing the stored proc, I am logging into that same schema and executing the proc as the owner.

However, every time I run it, I am getting the error : ORA-01031: insufficient privileges for this statement

I have had the DBA grant CREATE ANY TABLE and CREATE ANY INDEX privileges explicitly to the user, because the PL/SQL may not be able to resolve the grants given via roles.

But am still getting the error in this line after the grants. The other parts of the proc is executing OK, as I can see from dbms_output tracing at various points.

Can anyone please help with ideas ?

Answer 1

You must grant ALTER ANY INDEX or ALTER INDEX on the specific index.

But if the procedure is created by default by the owner of the index and the procedure is also run by the same user there is no need to grant any additional privilege.

Here a small test case with Oracle 12.1 EE:

SQL> --
SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> create user myuser identified by "myuser" quota unlimited on users;

User created.

SQL> grant create session, create table, create procedure to myuser;

Grant succeeded.

SQL> connect myuser/myuser
Connected.

Session altered.

SQL> show user
USER is "MYUSER"
SQL> create table mytable as select * from all_objects where object_id < 1000;

Table created.

SQL> create index myindex on mytable(object_name);

Index created.

SQL> create procedure myproc
  2  as
  3  begin
  4   execute immediate 'alter index myindex rebuild online';
  5  end;
  6  /

Procedure created.

SQL> show errors
No errors.
SQL> exec myproc;

PL/SQL procedure successfully completed.

SQL> show errors
No errors.
SQL> 

Answer 2

The only conclusion I can see is that at least one of the facts you've stated is not actually true. Let me rephrase the stated facts:

A) The index, table, and procedure are all in the same schema

B) You are connecting to that schema to execute the procedure

If A and B are both true, you should not need any specific granted privileges to execute the rebuild command

C) The DBA has granted CREATE ANY TABLE, CREATE ANY INDEX, and ALTER ANY INDEX (per comment on other answer) to this user

These privileges should only be relevant if A and B are not both true. If they're not both true, and at least two schemas are involved, which one did the DBA grant the privs to

D) It is the ALTER INDEX command that is throwing the error

If A, B, and C are true, then I have to suspect that D is not

To eliminate possibilities, it would be good to show hard evidence of these facts -- i.e. query results that demonstrate they are true.

I'd also try executing the ALTER as a standalone command, and in an anonymous block. This will at least clarify whether executing it via procedure is part of the problem.

Answer 3

When I removed the ONLINE clause from the ALTER INDEX statement, it is working without error. Dont understand why !!! But happy its working :(