Schema only account with Oracle 18.3

With Oracle 18.3, we have the possibility to create schemas without a password. Effectively in a perfect world, we should not be able to connect to application schemas. For security reasons it is a good thing that nobody can connect directly to the application schema.

A good way is to use proxy connections, in fact connect as app_user but using the psi_user password for example:

Let’s create a user named app_user:

SQL> connect sys@pdb as sysdba
Enter password: 
Connected.

SQL> create user app_user identified by app_user
  2  quota unlimited on users;

User created.

SQL> grant create session , create table to app_user;

Grant succeeded.

Let’s create a proxy user named psi_user:

SQL> create user psi_user identified by psi_user;

User created.

SQL> grant create session to psi_user;

Grant succeeded.

We allow the proxy connection to the app_user:

SQL> alter user app_user grant connect through psi_user;

User altered.

Now we can connect via the proxy user using the following syntax:

SQL> connect psi_user[app_user]/psi_user@pdb 
Connected.

We can see we are connected as user app_user but using the psi_user password:

SQL> select sys_context('USERENV','SESSION_USER') as session_user,
sys_context('USERENV','SESSION_SCHEMA') as session_schema,
sys_context('USERENV','PROXY_USER') as proxy,
user
from dual;

SESSION_USER	SESSION_SCHEMA	        PROXY		USER
APP_USER	APP_USER		PSI_USER	APP_USER

But there is a problem, if the app_user is locked the proxy connection does not work anymore:

SQL> connect sys@pdb as sysdba
Enter password: 
Connected.
SQL> alter user app_user account lock;

User altered.

SQL> connect psi_user[app_user]/psi_user@pdb
ERROR:
ORA-28000: The account is locked.

Warning: You are no longer connected to ORACLE.

The good solution is to use the schema only Oracle 18c new feature:

We drop the old accounts:

SQL> connect sys@pdb as sysdba
Enter password: 
Connected.
SQL> drop user psi_user cascade;

User dropped.

SQL> drop user app_user cascade;

User dropped.

And we recreate them in the following way, we first create the schema owner with no authentication:

SQL> create user app_user no authentication
  2  quota unlimited on users;

User created.

SQL> grant create session , create table to app_user;

Grant succeeded.

We create the proxy user as before:

SQL> create user psi_user identified by psi_user;

We allow the proxy user to connect to the app_user:

SQL> alter user app_user grant connect through psi_user;

User altered.

We now can connect via psi_user:

SQL> connect psi_user[app_user]/psi_user@pdb
Connected.

And as the app_user has been created in no authentication, you receive the classical ORA-01017 error when you try to connect directly with the app_user account:

SQL> connect app_user/app_user@pdb
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Using no authentication is a good protection, but you cannot grant system privileges to such users:

SQL> grant sysdba to app_user;
grant sysdba to app_user
*
ERROR at line 1:
ORA-40366: Administrative privilege cannot be granted to this user.

We can try to alter the app_user with a password and grant it to sysdba but it does not work:

SQL> alter user app_user identified by password;

User altered.

SQL> grant sysdba to app_user;

Grant succeeded.

SQL> alter user app_user no authentication;
alter user app_user no authentication
*
ERROR at line 1:
ORA-40367: An Administrative user cannot be altered to have no authentication
type.

SQL> revoke sysdba from app_user;

Revoke succeeded.

SQL> alter user app_user no authentication;

User altered.

To understand correctly the behavior, I made the following test:

SQL> connect sys@pdb as sysdba
Enter password: 
Connected.

I remove the no authentication:

SQL> alter user app_user identified by app_user;

User altered.

Now I can connect on the app_user schema, I create a table and insert some values:

SQL> connect app_user/app_user@pdb
Connected.
SQL> create table employe (name varchar2(10));

Table created.

SQL> insert into employe values('Larry');

1 row created.

SQL> commit;

Commit complete.

I reset the app_user to no authentication:

SQL> connect sys@pdb as sysdba
Enter password: 
Connected.
SQL> alter user app_user no authentication;

User altered.

I connect with the proxy user, I can display the employe table content:

SQL> connect psi_user[app_user]/psi_user@pdb
Connected.
SQL> select * from employe;

NAME
----------
Larry

The table belongs to the app_user schema:

SQL> select object_name, object_type, owner from all_objects where object_name ='EMPLOYE';

OBJECT_NAME	OBJECT_TYPE	OWNER
EMPLOYE		TABLE		APP_USER

SQL> insert into employe values ('Bill');

1 row created.

SQL> commit; 

Commit complete.

SQL> select * from employe;

NAME
----------
Larry
Bill

What is the behavior in the audit trail ?

We create an audit policy to detect any table creation:

SQL> create audit policy psi_user_audit_policy
  2  privileges create table
  3  when 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''APP_USER'''
  4  evaluate per session
  5 container=current

Audit policy created.

SQL> audit policy psi_user_audit_policy whenever successful;

Audit succeeded.

If now we have a look at the unified_audit_trail view:

SQL> select event_timestamp, dbusername, dbproxy_username from unified_audit_trail where object_name = 'SALARY' and action_name = 'CREATE TABLE'

EVENT_TIMESTAMP		DBUSERNAME	DBPROXY_USERNAME
16-OCT-18 03.40.49	APP_USER	PSI_USER

We can identify clearly the proxy user in the audit trail.

Conclusion:

The schema only accounts is an interesting new feature. In resume we can create a schema named app_user and set the authentication to NONE, the consequence is that you cannot be logged in. We can create a proxy account named psi_user which connects through app_user and we can create tables , views … to this app_user schema.