Grant user to create table oracle

Question

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

Wernfried Domscheit · Accepted Answer · 2024-07-03 07:44:51Z

1

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.

answered Jul 3, 2024 at 7:44

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Grant user to create table oracle

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related