1

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

  • Why do you quote the names? Are sure the table space "sch1_data" (in lower case) exist? Do you have permissions on the tablespace? Try GRANT UNLIMITED TABLESPACE TO ddl_role – Wernfried Domscheit Apr 16 at 20:47
  • Tablespace exists and unlimited quota is already given to the user. Btw I could also unquote the names the problem remains the same. – Hybris95 Apr 16 at 21:39
  • Is it possible to modify how this sequence is created ? And wouldn't this cause trouble for other users who don't have access to the creator's schema ? – Hybris95 Apr 17 at 8:55
  • 1
    Sorry, I was wrong. The sequence DOES get created into the schema of the TABLE (so in "sch1" in your example. But: the creating user must also have the SELECT ANY SEQUENCE right. I'm not clear why that is a requirement. (I deleted my other comment) – Albert Godfrind Apr 17 at 9:05
  • 1
    About quoted names: it is better to use unquoted names: that makes them case-insensitive. If you call a table "tab1" you are giving it a lower-case name, and all applications will need to use the quoted identifier. Queries like SELECT ... FROM TAB1 will fail. But so will queries like select ... from tab1. The only reason for using quoted identifiers is if you have names with special characters or spaces - like "SPECIAL TABLE%1". – Albert Godfrind Apr 17 at 9:20
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.