Insufficient Privileges When Creating Tables in Oracle SQL Developer

Insufficient Privileges Create table

you or your dba should logon sys, and issue :

SQL> grant create any table to anonymous;

SQL> grant create table to anonymous;

SQL> grant resource to anonymous;

to have creating table privilege.

the difference between create table and create any table is that
if you have create table privilege then you can create a table in your
own schema.but if you have create any table system privilege then you
can create table in any schema.also to create an external table the
valid privilege is create any table if you use create table then it
will show an error.

ORA-01031: insufficient privileges-creating a table?

If table A references tables B and C (which now reside in a different schema), I presume you're talking about foreign key constraints. If that's so, then privileges you granted won't help. You need to grant the REFERENCES privilege, i.e.

grant references on b to y;

Why can my Oracle SQL user not create table, despite having CREATE TABLE permissions?

To create a table with an identity column you must also have the CREATE SEQUENCE privilege.

For example, if the administrator creates this user:

create user test_user identified by test_user;
grant create session, create table, create any table to test_user;

This statement will fail for that user:

TEST_USER@orclpdb> create table Course(
  2  CId int GENERATED BY DEFAULT AS IDENTITY,
  3  CName VARCHAR2(50)
  4  );
create table Course(
*
ERROR at line 1:
ORA-01031: insufficient privileges

But after the administrator runs this grant:

grant create sequence to test_user;

The user will be able to create the table:

TEST_USER@orclpdb> create table Course(
  2  CId int GENERATED BY DEFAULT AS IDENTITY,
  3  CName VARCHAR2(50)
  4  );

Table created.

CREATE ANY TABLE not sufficient for creating 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

Identity column contains a sequence
Primary Key contains an index.

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.

Oracle - insufficient privileges

Boy, you granted all possible (and impossible) privileges :)

Believe or not, those - actually - work. Have a look what happens on my 11g XE:

SQL> show user
USER is "SYS"
SQL> create user user1 identified by password1;

User created.

SQL> grant connect to user1 ;

Grant succeeded.

SQL> grant create session to user1 ;

Grant succeeded.

SQL> grant resource to user1 ;

Grant succeeded.

SQL> grant unlimited tablespace to user1 ;

Grant succeeded.

SQL> grant all privileges to user1 ;

Grant succeeded.

SQL> grant dba to user1 ;

Grant succeeded.

SQL> connect user1/password1@xe
Connected.

Session altered.

SQL> create table test (id number);

Table created.

SQL>

See? Everything seems to be fine - I'm connected as USER1 and have created a table. Would you mind posting the same output as I did, but this time ran on YOUR database so that we could see what you did and how Oracle responded?

By the way, I'd suggest you not to do it the way you did: when creating users, grant only minimum set of privileges he needs. If it turns out that he needs something else, you can easily grant it. Granting DBA role to it is, huh, kind of dangerous. This is how I usually do that:

SQL> connect sys@xe as sysdba
Enter password:
Connected.

Session altered.

SQL> drop user user1 cascade;

User dropped.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

SQL> create user user1 identified by password1
  2  default tablespace users
  3  temporary tablespace temp
  4  profile default
  5  quota unlimited on users;

User created.

SQL> grant create session to user1;

Grant succeeded.

SQL> grant create table to user1;

Grant succeeded.

SQL> connect user1/password1@xe
Connected.

Session altered.

SQL> create table test (id number);

Table created.

SQL>

Grant privilege to create table INSIDE oracle sql procedure

So thank you everyone for your answers. I resolved the problem by simply adding AUTHID CURRENT_USER to the proc declaration. Thank you again.