Create a table in another diagram

Question

The situation is that user1 gives permission to user2:

GRANT CREATE ANY TABLE, SELECT ANY TABLE TO user2; 

And after entering user2, I try to create a table:

 CREATE TABLE user1.test(id NUMBER PRIMARY KEY); 

result ORA-01031 - insufficient privileges I can create a table on my own schema and select tables from other schemas. I thought CREATE ANY TABLE solves the problem, but it looks different. Ah, and both users have unlimited table space. What else should I guarantee?

Answer 1

Perhaps you also need to provide CREATE ANY INDEX ? When you add a primary key, you create an index. You can quickly test this by eliminating the PK restriction.

Answer 2

create any table is too powerful to provide non-DBAs. The best approach would be to create a “create a table” procedure in the target schema that will accept the sanitized components of the required DDL and provide execution privileges for the required users.

A suitable interface would be something like ...

 create procedure create_table( table_name varchar2, columns varchar2, etc varchar2) 

... so you can ...

 begin user1.create_table( table_name => 'TEST', columns => 'id NUMBER PRIMARY KEY', etc => ''); end; 

... and have the design of the procedure and execute the DDL for you.

Answer 3

GRANT CREATES ANY INDEX IN SCHEMA_NAME