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.