Understanding Oracle 18c New Feature - Schema Only Account

About Schema Only Accounts

Schema is a collection of objects, while db user is an account to connect to the instance. Prior to 18c, a schema will be a db login user account, no distinct difference between both, and since 18c oracle provides a new type of user: Schema Only Account, that is only used as a schema, and cannot be used to login to instance, and could connect by its proxy user.

Note the following:

• Schema only accounts can be used for both administrator and non-administrator accounts.
• Schema only accounts can be created on the database instance only, not in Oracle Automatic Storage Management (ASM) environments.
• You can grant system privileges (such as CREATE ANY TABLE) and administrator roles (such as DBA) to schema only accounts. Schema only accounts can create objects such as tables or procedures, assuming they have had to correct privileges granted to them.
• You cannot grant the SYSDBA, SYSOPER, SYSBACKUP, SYSKM, SYSASM, SYSRAC, and SYSDG administrative privileges to schema only accounts.
• You can configure schema only accounts to be used as client users in a proxy authentication in a single session proxy. This is because in a single session proxy, only the credentials of the proxy user are verified, not the credentials of the client user. Therefore, a schema only account can be a client user. However, you cannot configure schema only accounts for a two-proxy scenario, because the client credentials must be verified. Hence, the authentication for a schema only account will fail.
• Schema only accounts cannot connect through database links, either with connected user links, fixed user links, or current user links.

How to use Schema Only Accounts

1. Create a Schema Only Account:

Syntax:

CREATE USER [USERNAME] NO AUTHENTICATION;

For example:

SQL> CREATE USER ECOMS NO AUTHENTICATION;

User created.

SQL> select username,account_status,password_versions,authentication_type from dba_users where username in ('ECOMS');

USERNAME        ACCOUNT_STATUS  PASSWORD_VERSIONS AUTHENTI
--------------- --------------- ----------------- --------
ECOMS           OPEN                              NONE

2. Alter existing user to Schema Only Account:

Syntax:

ALTER USER [USERNAME] NO AUTHENTICATION;

For example:

SQL> select username,account_status,password_versions,authentication_type from dba_users where username in ('ECOMS');

USERNAME        ACCOUNT_STATUS  PASSWORD_VERSIONS AUTHENTI
--------------- --------------- ----------------- --------
ECOMS             OPEN            11G 12C           PASSWORD

SQL> ALTER USER ECOMS NO AUTHENTICATION;

User altered.

SQL> select username,account_status,password_versions,authentication_type from dba_users where username in ('ECOMS');

USERNAME        ACCOUNT_STATUS  PASSWORD_VERSIONS AUTHENTI
--------------- --------------- ----------------- --------
ECOMS             OPEN                              NONE

SQL> conn ECOMS/ECOMS
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

SQL> conn / as sysdba
Connected.
SQL> alter user ECOMS identified by ECOMS;

User altered.

SQL> select username,account_status,password_versions,authentication_type from dba_users where username in ('ECOMS');

USERNAME        ACCOUNT_STATUS  PASSWORD_VERSIONS AUTHENTI
--------------- --------------- ----------------- --------
ECOMS             OPEN            11G 12C           PASSWORD

SQL> conn ECOMS/ECOMS
Connected.
SQL>

3. Use a proxy user to connect to Schema Only Account:

SQL> conn / as sysdba
Connected.
SQL> create user USERNAME3 identified by [USERNAME3];
User created.

SQL> grant create session to USERNAME3;
Grant succeeded.

SQL> alter user USERNAME1 grant connect through USERNAME3;
User altered.

SQL> grant dba to USERNAME1;
Grant succeeded.

SQL> create table USERNAME1.TABLENAME1(c1 number);
Table created.

SQL> insert into USERNAME1.TABLENAME1 values(123);
1 row created.

SQL> commit;
Commit complete.

SQL> conn USERNAME3/USERNAME3
Connected.

SQL> select * from TABLENAME1;
select * from TABLENAME1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn USERNAME3[USERNAME1]/USERNAME3
Connected.

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,
SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD') as AUTHENTICATION
from dual;  2    3    4    5    6

SESSION_USER    SESSION_SCHEMA  PROXY           USER       AUTHENTICATION
--------------- --------------- --------------- ---------- ---------------
USERNAME1       USERNAME1       USERNAME3       USERNAME1   NONE

SQL> select * from TABLENAME1;
        C1
----------
       123

SQL> insert into USERNAME1.TABLENAME1 values(345);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from TABLENAME1;
        C1
----------
       123
       345

SQL>