Read Only Oracle user in Oracle 23c database

In earlier releases of Oracle database editions, we can see common users, local users, or normal users, to make them read-only, we had to create special read-only roles and grant those to users, but with Oracle 23c, you can create special read-only users and can convert them to read write as well whenever required.

This read-only user can only perform select operations and completely restrict write operations

In the below example we will create read-only users in oracle 23c database and try to perform different operations 

SQL> conn sys/oracle@FREEPDB1 as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> show con_name

 

CON_NAME

------------------------------

FREEPDB1

SQL> 

use the below statement to create a read-only user in the Oracle 23c database and grant DBA privileges to the user

 

SQL> create user technodb identified by tech123 quota unlimited on users read only;

 

User created.

 

SQL> set lines 200 pages 2000 

SQL> col username for a20

SQL>select username, read_only from dba_users where username='TECHNODB';

USERNAME       REA

-------------------- ---

TECHNODB       YES

 

SQL> grant dba to technodb;

 

Grant succeeded.

 

Now we will try to create table with account

SQL>conn technodb/tech123@freepdb1

 

SQL> create table tab1 (id integer);

create table tab1 (id integer)

*

ERROR at line 1:

ORA-28194: Can perform read operations only

 

 

To convert read only user to read write, we can use below option 

SQL> alter user technodb read write;

 

User altered.

 

SQL> 

SQL> select username, read_only from dba_users where username='TECHNODB';

 

USERNAME       READ_ONLY

-------------------- --------------------

TECHNODB       NO

 

 

Now you can see you will be able to perform write operations as well

 

SQL> conn technodb/tech123@freepdb1

Connected.

SQL> 

SQL> create table tab1(id integer);

 

Table created.

 

SQL> 

 

 One thing to note that, read-only users can execute procedures that does not perform any DML/DDL statements in it.