Using CREATE SCHEMA AUTHORIZATION

Question

Can I use CREATE SCHEMA AUTHORIZATION for something other than the current user's schema?

I can do the following:

CREATE USER MAIN_USER 
IDENTIFIED BY main_user_pass;
GRANT CREATE SESSION TO MAIN_USER;
GRANT CREATE TABLE TO MAIN_USER;
ALTER SESSION SET CURRENT_SCHEMA = MAIN_USER;

Query 1:

SELECT USER FROM DUAL;

Result 1:

SYS

Query 2:

SELECT sys_context( 'userenv', 'current_schema') FROM dual;

Result 2:

MAIN_USER

I can do this:

CREATE SCHEMA AUTHORIZATION SYS
   CREATE TABLE new_product 
      (color VARCHAR2(10)  PRIMARY KEY);

Result:

Schema AUTHORIZATION created.

But when I try to do this, an error appears:

CREATE SCHEMA AUTHORIZATION MAIN_USER
   CREATE TABLE new_product 
      (color VARCHAR2(10)  PRIMARY KEY); 

Result:

ORA-02421: missing or invalid schema authorization identifier
02421. 00000 -  "missing or invalid schema authorization identifier"
*Cause:    the schema name is missing or is incorrect in an authorization
           clause of a create schema statement.
*Action:   If the name is present, it must be the same as the current
           schema.

Answer 1

Can I use CREATE SCHEMA AUTHORIZATION for something other than the current user's schema?

No, you can't. The documentation says:

Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in a single transaction.

This statement lets you populate your schema ...

Specify the name of the schema. The schema name must be the same as your Oracle Database username.

You have to be connected as the schema owner, so user returns MAIN_USER. Just changing your current schema with ALTER SESSION SET CURRENT_SCHEMA is not sufficient.

---

It also says:

To issue a CREATE SCHEMA statement, you must have the privileges necessary to issue the included statements.

and you have granted CREATE TABLE so that should work once you connect as that user. But it means you can't rely on the privileged SYS user's CREATE ANY privs to bypass the schema grants, which might have been an advantage had it been allowed to work as you hoped; if you want your user to end up without those privileges you'll have to grant them, run CREATE SCHEMA as that user, then revoke them again. Or go back to individual CREATE object statements, which you can run for another user as SYS - but without the all-or-nothing single-transaction benefit you get from CREATE SCHEMA.

Answer 2

The error message is pretty clear: you can't do that. From the documentation:

The schema name must be the same as your Oracle Database username.

Setting current_schema only changes the default schema name prepended to an object reference in a SQL command that isn't fully qualified, so after setting it to MAIN_USER, this command:

select * table table_a;

would be interpreted as

select * from main_user.table_a;

instead of

select * from sys.table_a;

Setting current_schema doesn't actually change your logged in identity or affect your privileges in any way, and if sys can't execute a command like that against another schema then nobody can do it.