CODEWITHSUNDEEP
oracle-databasesql-grant
Lev
Lev

Reputation: 833

Grant user to create table oracle

In my oracle 23ai instance, as ADMIN user I created this user:

CREATE USER IF NOT EXISTS USER000 IDENTIFIED BY MyHardP4ssword';

Then I granted him some privileges:

GRANT RESOURCE, CONNECT, CREATE SESSION, CREATE USER, ALTER SESSION, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE ROLE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, UNLIMITED TABLESPACE TO USER000 WITH ADMIN OPTION;

I connect to USER000.

Then I create another user (I dont know in advance what user I will create, but e.g sales):

CREATE USER sales IDENTIFIED BY MyhardP4ssword;

Now, connected as USER000 I want to create tables on sales. How can I do that?

I tried:

GRANT CREATE SESSION,CREATE TABLE TO sales;

ALTER SESSION SET CURRENT_SCHEMA = sales

but when I create the table:

CREATE TABLE sale (
saleID NUMBER,
productID NUMBER);

I got this:

SQL Error [1031] [42000]: ORA-01031: privilegios insuficientes

Upvotes: 0

Views: 41

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

The CREATE TABLE privilege grants to create a table in your schema, i.e. in schema USER000 only.

You would need to grant CREATE ANY TABLE. However, as the name implies this grants to create tables in any schema.

As far as I know, you cannot limit this privilege to certain schema, but maybe I am wrong and such function has been added in most recent version of Oracle.

Upvotes: 1

Related Questions