In Oracle 18c a new security feature is introduced called “Schema Only Accounts” , this feature will enable you to create a schema account without authentication. This is a great feature, in previous releases schema account is a highly privileged account that can perform “anything” within the database objects stored in the schema. To clarify, simply using schema account you have the power to “turn auditing off” which is a serious security issue.

To illustrate this new feature:

In a pluggable database called PDB_ORIGIN I will create a schema only account:

SQL> CREATE USER SPECIAL_SCHEMA NO AUTHENTICATION

DEFAULT TABLESPACE TS_SPECIAL_SCHEMA

TEMPORARY TABLESPACE TEMP;

SQL> alter user SPECIAL_SCHEMA quota unlimited on TS_SPECIAL_SCHEMA;

 

Its worth mentioning that you can alert existing database accounts:

SQL> ALTER USER demo NO AUTHENTICATION;

To connect to the schema only account SPECIAL_SCHEMA you need configure proxy setup:

SQL> ALTER USER SPECIAL_SCHEMA GRANT CONNECT THROUGH EMAD;

Connecting using easy connect, will throw an error that special_schema account lacks create session !

CONN emad[SPECIAL_SCHEMA]/emad_18c@//localhost:1521/pdb_origin

SQL> grant create session to SPECIAL_SCHEMA;

 

I will also grant SPECIAL_SCHEMA the “resource” role so I can start creating objects:

SQL> grant resource to SPECIAL_SCHEMA;

SQL> alter user SPECIAL_SCHEMA default role all;

Querying dba_audit_trail to find all actions performed through SPECIAL_SCHEMA account:

SQL> select USERNAME,OWNER,action_name,SQL_TEXT,current_user from dba_audit_trail where OWNER=’SPECIAL_SCHEMA’;

 

SQL> select * from proxy_users where PROXY=’EMAD’;

To find the list of schema-only accounts in your database:

SQL> select * from dba_users where authentication_type=’NONE’;