create index online fails with ORA-01031: insufficient privileges

The developer team made a package to create the indexes, which actually first checks whether the index exists, if not, creates it.

fact@MATRIX> desc pkg_ddl
..........
PROCEDURE CREATE_OBJECT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_OBJECT                       VARCHAR2                IN
 P_TYPE                         VARCHAR2                IN
 P_STATEMENT                    VARCHAR2                IN
..........

P_OBJECT –> Object name
P_TYPE –> Object type
P_STATEMENT –> create ddl command

E.g

fact@MATRIX> exec pkg_ddl.create_object('BIG_INDX','INDEX','CREATE INDEX BIG_INDX ON BIG (Object_id,data_object_id) TABLESPACE test ONLINE');

Not creating object BIG_INDX of type INDEX because it already exists

PL/SQL procedure successfully completed.

When the developer team was trying the same they got

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "FACT.PKG_DDL", line 62
ORA-06512: at line 1

I traced the session and below important things to notice –

..................
PARSING IN CURSOR #245672968 len=35 dep=2 uid=94 oct=26 lid=94 tim=4140876192 hv=2361072276 ad='2862acc4' sqlid='bgp3cdy6bq6nn'
LOCK TABLE "BIG" IN ROW SHARE MODE 
END OF STMT
PARSE #245672968:c=0,e=1478,p=0,cr=5,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=4140876189
EXEC #245672968:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=4140876499
CLOSE #245672968:c=0,e=8,dep=2,type=0,tim=4140876598
..................
..................
PARSING IN CURSOR #251345096 len=172 dep=2 uid=94 oct=1 lid=94 tim=2900040930 hv=3279484524 ad='28622214' sqlid='c58jnyr1rjxmc'
create table "FACT"."SYS_JOURNAL_79518" (C0 NUMBER, C1 NUMBER,  opcode char(1), partno number,  rid rowid, primary key( C0, C1 , rid )) organization index TABLESPACE "TEST"
END OF STMT
.....................

So, while creating the index with ONLINE option, an IOT is created internally.

Checked for the privilege and the user was granted “CREATE TABLE” but from within RESOURCE role.Stored PL/SQL requires a direct grant and therefore the procedure fails with ‘insufficient privileges’. So granted ‘CREATE TABLE’ system privilege directly to the user, after which the index got created.

fact@MATRIX> exec pkg_ddl.create_object('BIG_INDX','INDEX','CREATE INDEX BIG_INDX ON BIG (Object_id,data_object_id) TABLESPACE test ONLINE');
CREATE INDEX BIG_INDX ON BIG (Object_id,data_object_id) TABLESPACE test ONLINE

PL/SQL procedure successfully completed.

The trace showed

...............
PARSING IN CURSOR #251345096 len=172 dep=2 uid=94 oct=1 lid=94 tim=2900040930 hv=3279484524 ad='28622214' sqlid='c58jnyr1rjxmc'
create table "FACT"."SYS_JOURNAL_79519" (C0 NUMBER, C1 NUMBER,  opcode char(1), partno number,  rid rowid, primary key( C0, C1 , rid )) organization index TABLESPACE "TEST"
END OF STMT
PARSE #251345096:c=0,e=10655,p=0,cr=2,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=2900040927
BINDS #251346228:
................
PARSING IN CURSOR #246147596 len=217 dep=3 uid=94 oct=9 lid=94 tim=4141333243 hv=947652630 ad='285df19c' sqlid='b8rjapww7s20q'
CREATE UNIQUE INDEX "FACT"."SYS_IOT_TOP_79520" on "FACT"."SYS_JOURNAL_79519"("C0","C1","RID") INDEX ONLY TOPLEVEL TABLESPACE "TEST" STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) NOPARALLEL
END OF STMT
................
PARSING IN CURSOR #249331404 len=43 dep=2 uid=0 oct=12 lid=0 tim=4143109340 hv=592606027 ad='eda0984' sqlid='0b4k2ncjp4wub'
drop table "FACT"."SYS_JOURNAL_79519" purge
END OF STMT
................