I have a user in charge of deploying DDL on other schemas than its own.
This user can create a table on those schemas, it can also create sequences on those schemas.
But when I try to create a table with a GENERATED IDENTITY column on those schemas,
the user encounters an ORA-01031: privileges insuffisants
When this user try the same create table on its own schema he can do it properly.
This user was given the following rights :
GRANT CREATE ANY SEQUENCE TO ddl_role;
GRANT CREATE ANY TABLE TO ddl_role;
Here is an example of the query executed :
CREATE TABLE "sch1"."tab1"
( "col1" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE)
TABLESPACE "sch1_data";
The following queries work :
CREATE TABLE "sch1"."tab1"
( "col1" NUMBER )
TABLESPACE "sch1_data";
CREATE SEQUENCE "sch1"."seq1";
References :
https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1
Best Answer
In order to create a table using the
GENERATED BY...
mechanism you need also theSELECT ANY SEQUENCE
privilege, in addition toCREATE ANY SEQUENCE
.I don't know why that is the case, since you are able to create a standalone sequence. I suspect it has to do with setting up the link between the table and the sequence.
Here is an example:
And in the other schema:
If I revoke the
SELECT ANY SEQUENCE
privilege from userpg_test
then the table creation will fail.