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";