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 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 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('Abdul Malik');
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
Abdul Malik
|
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 ('Abdul Sami');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from employe;
NAME
Abdul Malik
Abdul Sami
|
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
privileges create table
when 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''APP_USER'''
evaluate per session
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.
|
|
