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.