Oracle 23ai : The all new Hybrid Read-Only for pluggable databases (PDBs)

 

The latest Oracle database version, Oracle 23ai, introduced a new open mode called Hybrid Read-Only for pluggable databases (PDBs). Local users can only connect with read-only access when a PDB is set to this new mode. However, common users are not restricted and can still perform write operations.

Command to convert to this mode

SQL> ALTER PLUGGABLE DATABASE TECHNOPDB OPEN HYBRID READ ONLY;
Pluggable database altered.

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TECHNOPDB                      READ WRITE NO

Testing Write Access with common user - SYS

Now, we connect to TECHNOPDB as the SYS user and test write operations:

sqlplus "sys/sys@target:1521/TECHNOPDB as sysdba"

SQL> show user;
USER is "SYS"

SQL> select OPEN_MODE from v$pdbs;
OPEN_MODE
----------
READ WRITE

SQL> create table tech_user.test_tab(id number);
Table created.

As we can see, the SYS user sees the OPEN_MODE as READ WRITE, even though the PDB was opened in HYBRID READ ONLY mode.

Testing Write Access as any Local User

Now, let us repeat the test with a local user named VAHID:

sqlplus "tech_local/test123@target:1521/TECHNOPDB"

SQL> show user;
USER is "tech_local"

SQL> select OPEN_MODE from v$pdbs;
OPEN_MODE
----------
READ ONLY

SQL> create table tech_user.test_tab(id number);
ORA-16000: Attempting to modify database or pluggable database that is open for read-only access.

As expected, the local user (TECH_LOCAL) is not allowed to perform write operations.

Checking Hybrid Read-Only Mode Status

The view V$CONTAINER_TOPOLOGY can help to check the HYBRID READ ONLY mode:

SQL> select IS_HYBRID_READ_ONLY, CON_NAME, OPEN_MODE from V$CONTAINER_TOPOLOGY;

IS_HYBRID_READ_ONLY  CON_NAME   OPEN_MODE
-------------------- ---------- ----------
YES                  ORCLORPDB  READ WRITE