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:
Post a Comment