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"
- 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
SYSDGadministrative 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 🙂