Grant rights to create a table with an identity column on another schema

Question

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

Answer 1

In order to create a table using the GENERATED BY... mechanism you need also the SELECT ANY SEQUENCE privilege, in addition to CREATE 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:

SQL> connect pg_test/pg_test@graphdb
Connected.

SQL> select privilege from user_sys_privs;

PRIVILEGE
-------------------------------------------------------------------------------
CREATE ANY TABLE
CREATE SESSION
CREATE ANY SEQUENCE
SELECT ANY SEQUENCE
ALTER SESSION

5 rows selected.

SQL> create table scott.t1 (i number GENERATED BY DEFAULT ON NULL AS IDENTITY);
Table created.

And in the other schema:

SQL> connect scott/tiger@graphdb
Connected.

SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ ---------------------------------
...
T1                             TABLE
ISEQ$$_81682                   SEQUENCE

50 rows selected.

If I revoke the SELECT ANY SEQUENCE privilege from user pg_test then the table creation will fail.