Creating Schema-Only Accounts in Oracle 18c

In this post, we will create a schema-only user or we can say that user with no authentication, this feature introduced in Oracle 18c. Sometimes developers want to create a user which contain data only but not allowed to log in.

[oracle@18c ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 30 09:32:16 2018
Version 18.2.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0

SQL> alter session set container=PDB18C2;

Session altered.

Now creating the user without password.

SQL> create user nopassword NO AUTHENTICATION;

User created.

SQL> grant create session,create table,unlimited tablespace to nopassword;

Grant succeeded.

Create a dummy table for testing.

SQL> create table nopassword.test ( id number);

Table created.

SQL> conn nopassword@PDB18C2
Enter password:
ERROR:
ORA-01005: null password given; logon denied

Warning: You are no longer connected to ORACLE.
SQL>

How to check authentication type for schema

SQL> select username, authentication_type from dba_users where username='NOPASSWORD';

USERNAME     AUTHENTICA
---------    -------------
NOPASSWORD   NONE

Change authentication type to a password.

SQL> alter user nopassword identified by oracle;

User altered.

SQL> select username, authentication_type from dba_users where username='NOPASSWORD';

USERNAME     AUTHENTICA
-----------  -----------
NOPASSWORD   PASSWORD

SQL>

Stay tuned for more articles on Oracle 18c 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp