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. 

Enhancement in Direct Path Insert - Oracle 23ai Feature

This is one another nice feature introduced in 23ai.

Oracle 23ai enhanced this feature with follow up DMLs after the direct path insert. 

When we are loading lots of data into the Datawarehouse , we typically use direct path insert , as the data load is faster ; as oracle directly writes into the data file , bypassing the buffer cache and above the high water mark.

But , we can't do any data manipulation on the underlying table without issuing COMMIT / ROLLBACK.

Now , in Oracle 23ai ,we can use DMLs to view the table contents. This features enables us to do multiple checks before committing the data into the database. 

Comments Welcome!

Here is an example. 

SQL> select banner from v$version;

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

SQL> DROP TABLE IF EXISTS emp;     

Table dropped.

SQL> create table emp 

  2  as 

  3  select object_name as emp_name , 

  4  object_type as emp_dept 

  5  from all_objects

  6  where 1 = 2;

Table created.

SQL> select count(*) from emp;

  COUNT(*)

----------

0

SQL> insert /*+ APPEND */ into emp

  2  select object_name , object_type 

  3  from all_objects;

52749 rows created.

SQL> select count(*) from emp;

  COUNT(*)

----------

     52749

SQL> update emp 

  2  set emp_dept ='HR'

  3  Where emp_dept = 'TABLE';

149 rows updated.

SQL> commit;

Commit complete.

 

Hybrid Read Only in Oracle 23ai

 "Hybrid Read only mode" one of the nicest improvements in Oracle 23ai.

With this , we can have the pluggable databases ( PDB) either as "read/write" / "read only" .

In this "read only" mode , the common users will able to update the tables , as if PDB is "open" and "read write" mode the local (PDB) users will not be able to update / insert any tables; they can "select only" from the pdb objects.

This features enables the DBAs to patch , update the database objects in a pluggable database. 

Here is an example.

There is a new column name 'IS_HYBRID_READ_ONLY' to the v$view ( v$container_topology)

Log on to CDB and review the PDB status.

Session 1:

SQL> show con_name;

CON_NAME 

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

CDB$ROOT

SQL> select con_name , open_mode , is_hybrid_read_only from v$container_topology;

CON_NAME    OPEN_MODE     IS_HYBRID_READ_ONLY    

___________ _____________ ______________________ 

CDB$ROOT    READ WRITE    NO                     

PDB$SEED    READ ONLY     NO                     

FREEPDB1    READ WRITE    NO   

Session 2 :

In this PDB , let us create a user in PDB and give him access to create a table , quota unlimited on "USERS" tablespace. Note that , the user need not created with "LOCAL" as the prefix. 

SQL> show user                    

USER is "LOCAL_MOHIDEEN"

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

Table created.

SQL> insert into emp values ( 'Zahir' , 'IT') ;

1 row created.

SQL> commit;

Commit complete.

As the PDB is open for "READ WRITE" , the local user was able to create a table and insert a record in to the table. 

Now , let us go to the session 1 and make the PDB as hybrid read only.

Session 1:

In this session , we made the PDB as "hybrid read only" and created a common user ( C##MOHIDEEN) and give him access 

to the table owned by the local user ( LOCAL_MOHIDEEN). Note that common user has to be created with C## as prefix. 

SQL> show con_name;

CON_NAME 

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

CDB$ROOT

SQL> alter pluggable database freepdb1 close immediate;

Pluggable database FREEPDB1 altered.

SQL> alter pluggable database freepdb1 open hybrid read only;

Pluggable database FREEPDB1 altered.

SQL> select con_name , open_mode , is_hybrid_read_only from v$container_topology;

CON_NAME    OPEN_MODE     IS_HYBRID_READ_ONLY    

___________ _____________ ______________________ 

CDB$ROOT    READ WRITE    NO                     

PDB$SEED    READ ONLY     NO                     

FREEPDB1    READ WRITE    YES 

Session 2 :

Now , let us go to the other session and try to insert a record in to the table (EMP) 

SQL> show user;

USER is "LOCAL_MOHIDEEN"

 

SQL> insert into emp values ('John Doe','HR');

insert into emp values ('John Doe','HR')

            *

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

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

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

This time , let us log on to the PDB as the common user ( c##mohideen) 

SQL> show user

USER is "C##MOHIDEEN"

SQL> insert into local_mohideen.emp values ( 'John Doe','HR'); 

1 row created.

SQL> commit;

Commit complete.

There we have it . 

With the cdb user , I can do read/write operations, where with the local user the database is read only. 

Hybrid Rocks! :-) 

Comments Welcome!

Oracle 23ai New Features.

There are a few new features introduced in Oracle 23ai that have been in other RDBMS systems for a while. 

These features enable compatibility with other systems and also make it easier for non-Oracle database developers. 

I welcome these features, 

    1) as it opens to all DB developers

    2) and it lead to simpler / Readable SQL

    

Here we go ...

a) No need for DUAL  

There is no need to use DUAL for SELECT that uses only expression. 

SQL> select banner from v$version;

BANNER

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

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

SQL> select sysdate ;

SYSDATE

---------

07-JUL-25

SQL> select level connect by level <=5;

     LEVEL

----------

1

2

3

4

5

b) Boolean Datatype 

  The new datatype 'boolean' simplifies the development.   

   Now, it can hold boolean values ( true/false, 1 /0 ) 

   Here is an example. 

   SQL> create table t ( c1 int , c2 boolean) ;

   Table created.

   SQL> insert into t values ( 1 , 'true');

   1 row created.

   SQL> insert into t values ( 2 , 'false');

   1 row created.

   SQL> insert into t values ( 3 , 1);

   1 row created.

  SQL> select * from t;

  C1 C2

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

1 TRUE

2 FALSE

3 TRUE

C) VALUES clause 

   Continuing the example from "Boolean Datatype", 

   We can replace those 3 individual insert SQLs with one SQL as below.

  

   SQL> delete from t;

      3 rows deleted.

SQL> insert into t values ( 1 , 'true') , ( 2 , 'false'),( 3 , 1);

3 rows created.

SQL> select * from t;

C1 C2

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

1 TRUE

2 FALSE

3 TRUE

c) Simplified Update / Delete 

The UPDATE / DELETE are handled easily with the new syntax.

As an example, we need to increase the salaries of all employees in Location ID "1700" to receive 2.25%, then we can use the simple join to accomplish it. 

SQL> select * from t_dept;

DEPARTMENT_ID DEPARTMENT_NAME      MANAGER_ID LOCATION_ID

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

   10 Administration     200        1700

   20 Marketing     201        1800

   30 Purchasing     114        1700

   40 Human Resources     203        2400

   50 Shipping     121        1500

   60 IT     103        1400

   70 Public Relations     204        2700

   80 Sales     145        2500

   90 Executive     100        1700

  100 Finance     108        1700

  110 Accounting     205        1700

  120 Treasury        1700

  130 Corporate Tax        1700

  140 Control And Credit        1700

  150 Shareholder Services        1700

  160 Benefits        1700

  170 Manufacturing        1700

  180 Construction        1700

  190 Contracting        1700

  200 Operations        1700

  210 IT Support        1700

  220 NOC        1700

  230 IT Helpdesk        1700

  240 Government Sales        1700

  250 Retail Sales        1700

  260 Recruiting        1700

  270 Payroll        1700

27 rows selected.

SQL> Update t_emp e

     set e.salary =  e.salary + ( e.salary * ( 2.25 / 100 ) )

     from t_dept d 

     Where e.department_id = d.department_id and d.location_id = 1700;  

18 rows updated.

e) "RETURNING" Clause 

We can use the "RETURNING" clause to identify the state and the future state of the impacted records.

This can be used in SELECT , INSERT , UPDATE , DELETE and MERGE statements.

In the following example, the salary of the employee with ID 111 is given a 2.25% increase. 

SQL> select employee_id , salary from t_emp where employee_id = 111;

EMPLOYEE_ID SALARY

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

111   7700

SQL> var l_proposed_sal number;

SQL> var l_current_sal number;

SQL> Update t_emp

     set salary =  salary + ( salary * ( 2.25 / 100 ) )

     Where employee_id = 111

     Returning new salary ,old salary 

     into :l_proposed_sal , :l_current_sal ;  

1 row updated.

SQL> print :l_proposed_sal ;

L_PROPOSED_SAL

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

       7873.25

SQL> print :l_current_sal ;

L_CURRENT_SAL

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

7700

Group by 'Column Position' in Oracle 23ai

In this blog post ( https://zahirmohideen.blogspot.com/2024/04/oracle-23c-new-feature-grouping-by-alias.html ), we were introduced to a minor enhancement to the group by clause. 

We can also use the column position in the group by clause in Oracle 23ai, if the parameter (group_by_position_enabled) is set to TRUE. 

From my perspective, this shortcut makes the code short, but it may not be readable or ideal in the long term. 

Again, this is my view. 

Here is an example.

SQL> create table t as select object_name , object_type from all_objects;

Table T created.

SQL> sho parameter group_by_po

NAME                      TYPE    VALUE 

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

group_by_position_enabled boolean FALSE 

SQL> select object_type , count(*) as cnt from t group by  1  having cnt > 1;

Error starting at line : 1 in command -

select object_type , count(*) as cnt from t group by  1  having cnt > 1

Error at Command Line : 1 Column : 8

Error report -

SQL Error: ORA-03162: "OBJECT_TYPE": must appear in the GROUP BY clause or be used in an aggregate function as 'group_by_position_enabled' is FALSE

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

More Details :

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

SQL> alter session set group_by_position_enabled = true;

Session altered.

SQL> select object_type , count(*) as cnt from t group by  1  having cnt > 1;

OBJECT_TYPE                CNT 

_____________________ ________ 

TABLE                     2510 

CLUSTER                     10 

INDEX                     2486 

SYNONYM                  13105 

TABLE PARTITION            656 

INDEX PARTITION            608 

SEQUENCE                    37 

DIRECTORY                   12 

PACKAGE                    701 

VIEW                      8446 

FUNCTION                   307 

PROCEDURE                   54 

TYPE                      1656 

CONTEXT                     23 

OPERATOR                    60 

DOMAIN                     109 

LOB                          3 

TABLE SUBPARTITION          33 

LOCKDOWN PROFILE             3 

PACKAGE BODY               165 

CONSUMER GROUP              18 

JOB CLASS                    3 

DESTINATION                  2 

SCHEDULE                     4 

WINDOW                       9 

SCHEDULER GROUP              4 

EVALUATION CONTEXT           2 

XML SCHEMA                  19 

INDEXTYPE                   11 

TYPE BODY                   81 

RULE SET                     2 

TRIGGER                    129 

JAVA CLASS               32754 

JAVA RESOURCE             1228 

JAVA DATA                   17 

LIBRARY                     25 

36 rows selected. 

Let me know your comments, please!