Firstly at a logical high level in Oracle a user is an account used by a real person to connect to the database and do work on other users (I know) data. A schema is an account that owns objects such as tables, views, procedures etc.
BUT, a user intended to be a logical user, used by a real person to connect and do things in the database can also have objects. A schema intended to only own the objects of the application
In Oracle when you create a user or schema you use the CREATE USER command. For instance:
SQL> create user pete_u identified by pete_u;
User created.
SQL> create user pete_s identified by pete_s;
User created.
SQL>
I create a user and a schema. For the user i can grant privileges to login and to access other objects or what is needed. For the schema I also create a user. The intention with the schema is to not log in; even for creating objects and for the schema to own objects that are needed and make grants on those objects to other users and schemas.
From an Oracle perspective a user is a schema and a schema is a user BUT, each user has a schema created at the same time as the user and each schema has a user created at the same time. We cannot separate the two except for making a user/schema a schema only account; i.e. it cannot be logged into. More likely we need to manage our accounts and never log into schemas and never create objects in users.
Oracle does not really have a true separation between a schema and a user.
From 18c we can create a schema only account but in truth this could simply be altered and a password added. A schema only account is created as follows:
SQL> create user pete_o no authentication;
User created.
SQL>
As we can see below the password is just zeros:
SQL> select password,spare4 from sys.user$ where name='PETE_O';
PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
S:0000000000000000000000000000000000000000FAD4D632D13FC635FD36;T:000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000FA258C558BB96EE9061BA13F6547AC98
SQL>
It was possible before 18c to simply create a user with the undocumented IDENTIFIED BY VALUES clause to achieve the same thing and indeed I have done this for over 20 years and advised others the same.
The key to this design is to also use proxy to connect to the schema. So that no one ever directly logs into the schema but proxies to it to manage it and create objects. We are still connected in a sense to our own user (logged in) and accessing the schema without authenticating to the schema.
So, In Oracle a user is a schema and a schema is a user.
Oracle also supports a CREATE SCHEMA statement BUT this does not create a schema without a user or add a schema to an existing user. We cannot separate the two. The CREATE SCHEMA command can be used to add multiple objects at the same time to an existing user in a sense creating a schema. Or if the user does not exist then it creates the user account. Lets try all the possible types of CREATE SCHEMA. First lets try creating a schema for an existing user:
SQL> create schema pete_o create table test1 (col1 number);
create schema pete_o create table test1 (col1 number)
*
ERROR at line 1:
ORA-02420: missing schema authorization clause
SQL>
OK, so what if we change that to not use an existing user:
SQL> create schema pete_x create table test1 (col1 number);
create schema pete_x create table test1 (col1 number)
*
ERROR at line 1:
ORA-02420: missing schema authorization clause
SQL>
Still does not work; lets create a schema PETE_X and authenticate via the user PETE_O:
SQL> create schema pete_x authorization pete_o create table test1 (col1 number);
create schema pete_x authorization pete_o create table test1 (col1 number)
*
ERROR at line 1:
ORA-02420: missing schema authorization clause
SQL>
Still does not work. If we just authorise in PETE_O which exists
SQL> create schema authorization pete_o create table test1 (col1 number);
create schema authorization pete_o create table test1 (col1 number)
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier
SQL>
Hmm, should work. The account PETE_O exists and we are in essence adding objects via the CREATE SCHEMA command. What does this error mean?
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.
So, I think we can do two things. First grant create table and quota to PETE_O so it has the right to create a table and second set the current_schema to PETE_O:
SQL> grant create table to pete_o;
Grant succeeded.
SQL> grant unlimited tablespace to pete_o;
Grant succeeded.
SQL> alter session set current_schema=pete_o;
Session altered.
SQL> select sys_context('userenv','current_schema') from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
PETE_O
SQL> create schema authorization pete_o create table test1 (col1 number);
create schema authorization pete_o create table test1 (col1 number)
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier
SQL>
Still does not work
So, it would seem that alter session set current_schema does not work as we actually need to be connected as the user who CREATE SCHEMA is executed for not pretending to be that user:
SQL> alter user pete_o grant connect through pete_u;
User altered.
SQL> grant create session to pete_o;
Grant succeeded.
SQL> grant create session to pete_u;
Grant succeeded.
SQL>
Now connect to PETE_O and try again:
SQL> connect pete_u[pete_o]/pete_u@//192.168.56.33:1539/xepdb1
Connected.
SQL> create schema authorization pete_o create table test1 (col1 number);
Schema created.
SQL>
OK, we finally get CREATE SCHEMA to work. What a faff.
This is not necessary. We should just proxy to PETE_O in my case as the schema only account and simply create the tables, views, procedures we need. We still need separate grants to the schema for each object type. Schema is not a separate thing.
The Oracle documentation for the CREATE SCHEMA says:
This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user (see CREATE USER). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.
Oracle 18c CREATE SCHEMA
All of the statements for multiple tables, views etc still need to be executed. The only saving is a semi-colon after each one. Not worth the hassle; just create users and if there are no objects it is a user and if there are it is a schema. For schemas set an impossible password or make it schema only (same thing). Do not mix the two.
#oracleace #sym_42 #oracle #database #security #create #schema
