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.
