CODEWITHSUNDEEP
oracle-database
Thomas Carlton
Thomas Carlton

Reputation: 5976

Oracle : Grant Create table in another schema?

I have two users : Schema1 and Schema2

How to grant Create Table privilege On Schema2 to Schema1 ?

I have been turning around and I got confused. I tried :

From Schema2 I tried,

GRANT CREATE TABLE TO SCHEMA1 WITH ADMIN OPTION;

But it seems like this grants Creating table to Schema1 in its own Schema and not in the Schema2.

Any ideas please ?

Thank you.

Upvotes: 13

Views: 144302

Answers (3)

A B
A B

Reputation: 4158

The only other way to allow a non-DBA user to create a table in another schema is to give the user the CREATE ANY TABLE system privilege.

This privilege can only be given to SCHEMA1 by a user having the CREATE ANY PRIVILEGE privilege.

Upvotes: 22

blantomat
blantomat

Reputation: 41

Better solution (minimizes the security threat that comes with CREATE ANY TABLE privilege...)

  • Create a procedure on schema2 that takes a table definition as a "input" parameter (e.g. p_tab_def in varchar2(4000).
  • Inside put an execute_immediate(p_tab_def); statement. You MUST check the p_tab_def first in order to defend yourself from other DDL statements than "CREATE TABLE [...]". (e.g. you could use a simple check by checking first two words -> it must be "CREATE TABLE").
  • GRANT EXECUTE ON schema2.procedure_name TO schema1;

It's a simple concept ... I've used such concepts in my previous job.

Upvotes: 4

René Nyffenegger
René Nyffenegger

Reputation: 40603

You want to grant create ANY table:

grant create any table to schema1;

The any "modifier" allows to create tables in other than own schemas.

Upvotes: 7

Related Questions