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