I use the SYSTEM user to grant CREATE ANY TABLE to user TEST, but when I try to execute
create table other.dummy ...
I still get ORA-01031: insufficient privileges
Oracle : Grant Create table in another schema? claims this should work.
I tried to also grant CREATE ANY INDEX since the table has PK and therefore includes an index, but that didn't change anything.
GRANT ALL PRIVILEGES did the trick but I'd prefer something more limited.
The actual CREATE TABLE statement is:
CREATE TABLE OTHER.DUMMY_ENTITY (
ID NUMBER GENERATED by default on null as IDENTITY PRIMARY KEY,
NAME VARCHAR2(30)
)
What privileges do I need to grant beyond CREATE ANY TABLE?
When you grant the privilege CREATE ANY TABLE to a specific user, the user will be able to create any table in the database, as long as the creation of such table is compatible with the statement you are running. In your case, you are not just creating a table.
Let's simulate your scenario, by creating a user with such privilege and then trying to create the table in another schema.
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 5 10:54:17 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> show user
USER is "SYS"
SQL>
SQL> create user test_grant identified by "Oracle_123" ;
User created.
SQL> grant create session, create any table to test_grant ;
Grant succeeded.
SQL> exit
Now, I am connecting with test_grant to create a table as yours in the schema test
sqlplus test_grant/"Oracle_123"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 5 10:55:28 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> create table test.t1_privs ( c1 number generated by default on null as identity primary key , c2 varchar2(1) ) ;
create table test.t1_privs ( c1 number generated by default on null as identity primary key , c2 varchar2(1) )
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> create table test.t2_privs ( c1 number, c2 varchar2(1) ) ;
Table created.
As you can see, I can create a table in other schema, but not the one you want to create. Obviously elements inside your create table statement require other privileges, so let's analyse them
Let's give the user those any privileges
SQL> grant create any index, create any sequence to test_grant ;
Grant succeeded.
Try again
sqlplus test_grant/"Oracle_123"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 5 11:06:47 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Fri Nov 05 2021 11:03:31 +01:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> create table test.t1_privs ( c1 number generated by default on null as identity primary key , c2 varchar2(1) ) ;
create table test.t1_privs ( c1 number generated by default on null as identity primary key, c2 varchar2(1) )
*
ERROR at line 1:
ORA-01031: insufficient privileges
So, what is happening ?
When you create a table in another schema with a column as identity, you need not only the create any table and the create any sequence privileges, you also need the select any sequence privilege
SQL> grant select any sequence to test_grant ;
Grant succeeded.
sqlplus test_grant/"Oracle_123"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 5 11:31:44 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Fri Nov 05 2021 11:29:36 +01:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> create table test.t1_privs ( c1 number generated by default on null as identity primary key, c2 varchar2(1) ) ;
Table created.