Search This Blog

2012-07-19

Oracle: Alter Index Online by Other Owner and Get "insufficient privileges" Error

Product: Oracle RDBMS
Version: 11.2.0.2 and above

I have a customer who has following maintenance require, which is common for major corporate

1. Create another partition administration account with minimum privilege to move partition table and index to archive tablespace
2. This Oracle account is not the schema owner of table or index it is moving
3. The table move operation needs to be online
4. The index rebuild operation needs to be online
5. Grant minimum privilege to the partition administrator account

So this mainly to do with Oracle database privilege, and I encountered great challenge when doing #4

I though rebuild index only need "grant alter any index" privilege, but I am wrong. Due to the "ONLINE" syntax, Oracle database will create additional tables and indexes under original schema owner, so following are additional privileges require to grant to partition administrator account

grant create any table to sysman;
grant create any index to sysman;

Surprisingly, I don't need to give any quote on tablespace, or "grant unlimited tablespace" to partition administrator.

In summary, following is the solution

grant alter any index to [partition_admin];
grant create any table to [partition_admin];
grant create any index to [partition_admin];

Sample error message,

SQL> ALTER INDEX SCOTT.IDX_PRICELIST_1 REBUILD PARTITION P_20120425 NOCOMPRESS TABLESPACE users PARALLEL 2 online;
ALTER INDEX SCOTT.IDX_PRICELIST_1 REBUILD PARTITION P_20120425 NOCOMPRESS TABLESPACE users PARALLEL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
 ---------------
Complete Privilege. Assume partition administrator is SYSMAN:

select * from dba_sys_privs where grantee= 'SYSMAN';

GRANTEE PRIVILEGE            ADM
------- -------------------- ---
SYSMAN  ALTER ANY INDEX      NO
SYSMAN  CREATE ANY TABLE     NO
SYSMAN  ALTER ANY TABLE      NO
SYSMAN  CREATE TABLE         NO
SYSMAN  CREATE ANY INDEX     NO

No comments:

Blog Archive