Q
Problem solve Get help with specific problems with your technologies, process and projects.

Creating tables in specific schemas

I am an Oracle DBA. I want one of my users to create tables in another user's schema. When I am granting "create any table" to the user, he is able to create tables even in system schema. How can I create tables only in some specific schemas?
There is no system privilege that lets a user create a table in just one schema. The CREATE TABLE system privilege lets you create tables in your schema. The CREATE ANY TABLE system privilege lets you create tables in any user's schema. So these privileges do not facilitate your goal.

You can create a stored procedure owned by someone who has CREATE ANY TABLE system privileges. You can have this stored procedure ensure that the appropriate schema is being used and then create the table for you. Then grant EXECUTE on this stored procedure to the user you want to create tables in another's schema. The following should help you get started:

CREATE OR REPLACE PROCEDURE create_tables ( create_string
VARCHAR2(100)) AS
DECLARE
  usernm VARCHAR2(30);
  schema VARCHAR2(30);
BEGIN
  -- Get user executing this procedure
  SELECT user INTO usernm FROM dual;
  -- Get schema owner of table
  schema := SUBSTR(create_string,1,INSTR(create_string,'.')-1);
  schema := SUBSTR(schema,INSTR(schema,' ',-1));
  schema := UPPER(schema);

  -- Verify user can create a table in this schema
  IF (user='JOHN' AND schema='BOB') 
  OR (user='BILL' AND schema='JANE') THEN
     EXECUTE IMMEDIATE create_string;
  ELSE
     RAISE_APPLICATION_ERROR(-20001,'You are not allowed to create
tables in this schema.');
  END IF;
END;
/

GRANT EXECUTE ON create_tables TO john,bill;

In the above code, the owner of this stored procedure has CREATE ANY TABLE. Users JOHN and BILL have been granted execute privileges on this stored procedure. They call the stored procedure as follows (in SQL*Plus):

EXEC system.create_tables('CREATE TABLE bob.table1 (ID NUMBER)');

The stored procedure determines who called it and it parses the given input string for the schema of the table. It then checks to determine if the user can create the table. If so, the stored proc creates the table. If not, an error is raised.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close