Create table on another schema

Question

Situation is that user1 gives permision to user2:

GRANT CREATE ANY TABLE, SELECT ANY TABLE TO user2;

And after logging on user2, I'm trying to create table:

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

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

Answer 1

Perhaps you need to also grant CREATE ANY INDEX? You are creating an index when you add the primary key. You can quickly test this by omitting the PK constraint.

Answer 2

"create any table" is too powerful a privilege to be granting to non-DBA's. A better approach would be to create a "create table" procedure in the target schema that accepts sanitised components of the required DDL, and grant execute privilege on that to the required users.

A suitable interface would be something like ...

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

... so that you can ...

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

... and have the procedure construct and execute the DDL for you.