Read Only USER and SESSION in Oracle 23 ai

In this post ( https://zahirmohideen.blogspot.com/2025/07/hybrid-read-only-in-oracle-23ai.html) , I described about placing PDBs in "hybrid read only".

In this post , we will another "read only" feature from Oracle 23ai. 

This is "read only user and session" .

This enhancement allow the DBAs to control the read/write privileges of user/session across the different parts of the application and 

also enables to perform temporarily to control their privileges for testing , administration , application migration/upgrade purposes. 

Here is an example. 

A column ( READ_ONLY) has been added to the view "DBA_USERS". 

In this example , we will be using the user "MOHIDEEN". 

By default , when the user is created , the user has "READ WRITE" privilege on their schema. 

SQL> show user

USER is "SYSTEM"

SQL> col username format a15

SQL> select username , read_only from dba_users order by created desc FETCH FIRST 5 ROWS ONLY;

USERNAME REA

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

MOHIDEEN NO

AV NO

HRREST NO

CO NO

SH NO

Here , I sign in as "MOHIDEEN" and perform the table creation , updates as usual.

oracle@vbox Desktop]$ sqlplus mohideen@freepdb1

SQL> DROP TABLE IF EXISTS emp;   

Table dropped.

SQL> create table emp as select object_name as emp_name , object_type as emp_dept from all_objects;

Table created.

SQL> select count(*) from emp;

  COUNT(*)

----------

     52709

SQL> update emp set emp_dept ='HR' Where emp_dept = 'TABLE';

141 rows updated.

SQL> commit;

Commit complete.

SQL> exit

Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.7.0.25.01

Now , I sign in as "SYSTEM" and make the user "MOHIDEEN" as read only.

[oracle@vbox Desktop]$ sqlplus system/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Sun Aug 10 18:37:29 2025

Version 23.7.0.25.01

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

Last Successful login time: Sun Aug 10 2025 18:32:37 +00:00

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.7.0.25.01

SQL> alter user mohideen read only;

User altered.

SQL> col username format a15

SQL> select username , read_only from dba_users order by created desc FETCH FIRST 5 ROWS ONLY;

USERNAME REA

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

MOHIDEEN YES

AV NO

HRREST NO

CO NO

SH NO

Let us go back into the database as "MOHIDEEN" and perform the update operation on the table that I created. As the error clearly states , I can perform only read ( aka SELECT ) operations , not any write operation. 

oracle@vbox Desktop]$ sqlplus mohideen@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Sun Aug 10 18:39:54 2025

Version 23.7.0.25.01

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

Enter password: 

Last Successful login time: Sun Aug 10 2025 18:35:45 +00:00

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.7.0.25.01

SQL> update emp set emp_dept ='TECH SUPPORT' Where emp_dept = 'VIEW';

update emp set emp_dept ='TECH SUPPORT' Where emp_dept = 'VIEW'

       *

ERROR at line 1:

ORA-28194: Can perform read operations only

Help: https://docs.oracle.com/error-help/db/ora-28194/

SQL> select count(*) from emp;

  COUNT(*)

----------

     52709

     

We will reset user to allow write operations.

As in the example , user can perform read write operation. 

[oracle@vbox Desktop]$ sqlplus system/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Sun Aug 10 18:41:21 2025

Version 23.7.0.25.01

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

Last Successful login time: Sun Aug 10 2025 18:37:29 +00:00

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.7.0.25.01

SQL> alter user mohideen read write;

User altered.

[oracle@vbox Desktop]$ sqlplus mohideen@freepdb1

SQL> update emp set emp_dept ='DEV' Where emp_dept = 'INDEX';

115 rows updated. 

Here is an example of setting the "read only" option at the session level. 

SQL> show user

USER is "MOHIDEEN"

SQL> alter session set read_only=true;

Session altered.

SQL> update emp set emp_dept ='TECH SUPPORT' Where emp_dept = 'VIEW';

update emp set emp_dept ='TECH SUPPORT' Where emp_dept = 'VIEW'

*

ERROR at line 1:

ORA-28193: Can perform read operations only

Help: https://docs.oracle.com/error-help/db/ora-28193/

SQL> alter session set read_only=false;

Session altered.

SQL> update emp set emp_dept = 'OPS' where emp_dept = 'PROCEDURE';

35 rows updated.

Comments Welcome.