Schema Only Accounts Oracle Database 18C

New type of schema can be created in 18C where user has no password.

CREATE USER username NO AUTHENTICATION;

Schema user_noauth creation without password

CREATE USER user_noauth NO AUTHENTICATION;
GRANT CREATE SESSION TO user_noauth;

To connect to this type of schema you need to grant proxy authentication to other user.

CREATE USER tomasz IDENTIFIED BY tomasz;
GRANT CREATE SESSION TO tomasz;

ALTER USER user_noauth GRANT CONNECT THROUGH tomasz;

New column AUTHENTICATION_TYPE in dictionary can be found in DBA|ALL|USER_USERS for this feature

SELECT username, account_status, authentication_type 
  FROM dba_users
 WHERE username IN ('TOMASZ', 'USER_NOAUTH');

USERNAME      ACCOUNT_STATUS  AUTHENTICATION_TYPE
------------- --------------- --------------------
USER_NOAUTH   OPEN            NONE    
TOMASZ        OPEN            PASSWORD

Connect to no authentication account via proxy authentication

SQL> CONNECT tomasz[user_noauth]\tomasz
Connected.

SQL> show user
USER is "USER_NOAUTH"

Limitations

  • 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.

Hava a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.