As Oracle databases evolve to support modern application architectures and cloud-native workloads, fine-grained security control has become just as important as performance and availability. In the previous discussion on Hybrid Read-Only Mode for Pluggable Databases (PDBs), we saw how Oracle allows a database to behave as read-only or read/write depending on who is connected.
Now, let’s take this concept one level deeper.
In this article, we’ll explore Read-Only Users and Read-Only Sessions, a powerful feature that allows DBAs to control write access at the user level or even at the individual session level, without revoking privileges or restructuring application roles.
Why Read-Only Control at User and Session Level Matters
Setting a PDB to read-only or hybrid read-only is effective, but it’s often too coarse-grained for real-world applications.
Consider a typical application environment:
- Multiple application users
- Some users only read data
- Others perform updates or maintenance
- Certain operations require temporary write access
- Security policies must be enforced without downtime
Traditionally, DBAs had to:
- Revoke privileges
- Re-grant privileges later
- Bounce sessions
- Create additional roles or schemas
This approach is error-prone, time-consuming, and risky—especially in test, development, and cloud environments.
Read-only users and sessions solve this problem by allowing temporary, granular, and reversible control over write operations.
What Are Read-Only Users?
A read-only user is a database user whose sessions are restricted to read-only operations, regardless of the privileges they normally have.
Key characteristics:
- The user can connect successfully
- SELECT statements work normally
- INSERT, UPDATE, DELETE, and DDL operations are blocked
- No privileges are revoked
- Privileges remain intact and can be re-enabled instantly
This is extremely useful for:
- Testing scenarios
- Temporarily locking down application users
- Enforcing security during maintenance windows
- Protecting production data without breaking applications
What Are Read-Only Sessions?
A read-only session applies the same restriction—but only to a single session, not the entire user.
Key characteristics:
- The restriction applies immediately
- Only affects the current session
- Other sessions of the same user are unaffected
- Can be toggled on or off dynamically
This is ideal when:
- A maintenance operation must run safely
- A powerful user (even SYS) needs temporary protection
- You want zero risk during troubleshooting or analysis
Benefits Over Traditional Privilege Management
Read-only users and sessions provide several advantages:
✔ No Privilege Revocation
You don’t need to revoke or regrant privileges, avoiding mistakes and audit complexity.
✔ Fine-Grained Control
You can target:
- A single user
- A specific session
- A short time window
✔ Safer Application Management
Applications continue to work normally without errors caused by missing privileges.
✔ Cloud-Friendly Security
In cloud environments with frequent operations, this provides flexible and non-disruptive control.
How to Configure Read-Only Users
At the user level, read-only is controlled using a simple command:
ALTER USER username READ ONLY;
To revert the user back to normal behavior:
ALTER USER username READ WRITE;
Important Behavior to Understand
- The change only applies to new sessions
- Existing sessions remain unchanged
- Users must reconnect (or sessions must be killed) for the read-only mode to take effect
This design ensures session stability and avoids unexpected disruptions.
How to Configure Read-Only Sessions
At the session level, read-only mode is controlled by a session parameter:
ALTER SESSION SET read_only = TRUE;
To restore write capability:
ALTER SESSION SET read_only = FALSE;
Unlike user-level control:
- Session-level changes take effect immediately
- No reconnect is required
- The scope is limited to that session only
Monitoring Read-Only Users and Sessions
Oracle provides clear visibility into read-only settings.
Viewing Read-Only Users
A new column is available in the data dictionary:
DBA_USERS.READ_ONLY
You can check it using:
SELECT username, read_only
FROM dba_users;
USERNAME REA
----------------------- ---
SYS NO
SYSTEM NO
PDBADMIN NO
HR YES
SYSRAC NO
XS$NULL NO
LBACSYS NO
This helps DBAs quickly identify which users are restricted.
Viewing Read-Only Session Status
Session-level read-only is exposed as a parameter:
SHOW PARAMETER read_only;
This confirms whether the current session is operating in read-only mode.
Example 1: Read-Only User in Action
Step 1: Normal User Behavior
A development user (DEVTEST) is granted privileges on HR.EMPLOYEES and successfully performs an update.
Result:
- Update succeeds
- Changes can be committed or rolled back
Step 2: Enable Read-Only for the User
As an administrator:
ALTER USER devtest READ ONLY;
The active session is terminated or the user logs out.
Step 3: User Reconnects
The same user reconnects and attempts the same update.
Result:
- Operation fails
- Error indicates that only read-only operations are allowed
This confirms that:
- Privileges still exist
- Enforcement happens transparently
Step 4: Other Users Are Not Affected
The schema owner or other users can still perform updates normally.
This proves that the restriction is user-specific, not database-wide.
Step 5: Restore Write Access
As an administrator:
ALTER USER devtest READ WRITE;
The user reconnects and regains full write access.
Example 2: Read-Only Session Control
Step 1: Enable Read-Only at Session Level
Even a highly privileged user (such as SYS) can restrict their own session:
sqlplus system/*****@orclpdb1
SQL*Plus: Release 23.26.1.0.0 - Production on Fri Feb 6 09:33:56 2026
Version 23.26.1.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Fri Feb 06 2026 09:02:36 +05:30
Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production
Version 23.26.1.0.0
SQL> alter user hr read only;
User altered.
Step 2: Attempt a Write Operation
An update is attempted within the same session.
sqlplus hr/******@orclpdb1
SQL*Plus: Release 23.26.1.0.0 - Production on Fri Feb 6 09:42:44 2026
Version 23.26.1.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Fri Feb 06 2026 09:01:37 +05:30
Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production
Version 23.26.1.0.0
SQL> UPDATE hr.regions SET region_name = 'Europe' WHERE region_id = 5;
UPDATE hr.regions SET region_name = 'Europe' WHERE region_id = 5
*
ERROR at line 1:
ORA-28194: Can perform read operations only
Help: https://docs.oracle.com/error-help/db/ora-28194/
Result:
- Operation fails
- Only read-only operations are permitted
This is extremely useful during:
- Investigations
- Diagnostics
- Production troubleshooting
Step 3: Restore Session Write Capability
The session is unlocked using:
sqlplus system/*****@orclpdb1
SQL*Plus: Release 23.26.1.0.0 - Production on Fri Feb 6 09:47:20 2026
Version 23.26.1.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Fri Feb 06 2026 09:33:56 +05:30
Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production
Version 23.26.1.0.0
SQL> alter user hr read write;
User altered.
Write operations are immediately restored—no reconnect required.
Real-World Use Cases
🔹 Development and Testing
Prevent accidental updates while testing queries or reports.
🔹 Application Maintenance
Allow administrators to work safely while applications remain protected.
🔹 Security Enforcement
Temporarily lock down users during audits or incident response.
🔹 Cloud Operations
Enable fine-grained control without downtime or role reconfiguration.
How This Complements Hybrid Read-Only Mode
Read-only users and sessions work perfectly alongside Hybrid Read-Only PDB Mode:
- Hybrid mode controls who can write at the database level
- Read-only users and sessions control who can write at the user or session level
Together, they provide a layered security model that is:
- Flexible
- Non-disruptive
- Cloud-ready
Final Thoughts
Read-only users and sessions represent a major step forward in Oracle’s security and manageability capabilities. Instead of managing access with blunt tools like privilege revocation or database-wide restrictions, DBAs now have precision control over how and when data can be modified.
For environments that demand high availability, strong security, and operational agility, this feature is not just convenient—it’s essential.
If you’re already using hybrid read-only PDBs, read-only users and sessions are the perfect next layer to complete your database security strategy.
