Oracle : Grant Create table in another schema?

Question

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.

Answer 1

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 with DBA privileges.

Answer 2

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.

Answer 3

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.