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 asDBA
) 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
, andSYSDG
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