Thursday, 11 June 2026

Oracle 26ai Read-Only Users and Sessions

One very common production request sounds simple:

"Can we give this user access, but make sure they cannot change anything?"

Before Oracle AI Database 26ai, DBAs usually handled this by creating a separate reporting user, granting only SELECT, removing DML privileges, or depending on carefully designed roles. That approach works when access is cleanly designed from the beginning. But in real production systems, users often collect privileges over time. Support users get emergency grants, application users may have broader access than expected, and batch accounts sometimes have privileges that nobody wants to touch during an incident.

Oracle AI Database 26ai introduces a more practical control. A user or a session can now be made read-only, even if that user already has privileges that would normally allow write operations. This gives DBAs a safer way to temporarily stop changes without revoking and re-granting privileges.

This feature is useful during maintenance windows, application testing, audit checks, troubleshooting, and controlled production investigations. But it should be used carefully, especially with connection pools and existing application sessions.


The Real Problem This Feature Solves

The purpose of this feature is not just to create another reporting user. DBAs have always been able to create a user and grant only SELECT.

The real value is this:

Oracle 26ai allows DBAs to temporarily block write operations without removing the user’s existing privileges.

That is very useful in production.

Imagine an application support user that already has multiple object grants, schema-level access, or even powerful roles. In older versions, making that user read-only usually meant changing grants, creating another user, or depending on application-side discipline.

With Oracle 26ai, the user can be marked as read-only. The grants remain in place, but Oracle blocks write activity while the restriction is active.

Oracle documentation also highlights an important point: read-only restriction can override privilege grants, including schema grants, system grants, and even the DBA role.

That makes it a strong operational safety switch.


User-Level Read-Only Control

User-level read-only control is useful when the restriction must follow the user account itself.

For example, during a release freeze, a DBA may want to allow an application support user to continue querying data but prevent accidental INSERT, UPDATE, DELETE, CREATE, or other write operations.

This is controlled using CREATE USER or ALTER USER.

A read-only user can still run queries, but write operations fail with: 

ORA-28194: Can perform read operations only

This error is important from a troubleshooting point of view. If an application suddenly starts failing with ORA-28194, do not immediately assume it is a missing privilege issue. The user may already have the required privileges, but the read-only restriction may be active.

One key technical point: user-level read-only mode applies to local users only. It is not meant for common users across the CDB.


Session-Level Read-Only Control

Session-level read-only control is different.

It does not permanently change the database user. It only affects the current session. This is useful when the same user account is used for both read-only and read-write parts of an application.

For example, an application may enable read-only mode for a reporting flow and disable it before returning to normal transactional activity.

However, this must be handled carefully with connection pools. If an application sets a session to read-only and returns that session to the pool without resetting it, the next request using that same connection may fail unexpectedly on DML.

This is one of the most important production caveats.

For pooled applications, the cleanup logic must reset the session before the connection is returned to the pool.

Another technical point: session-level read-only mode cannot be enabled if the session already has an active transaction. The session should commit or rollback first.


READ ONLY vs READ_ONLY

This small syntax difference can confuse DBAs.

For user-level control, Oracle uses: 

READ ONLY
READ WRITE

For session-level control, Oracle uses the initialization parameter: 

READ_ONLY = TRUE
READ_ONLY = FALSE

So the concept is similar, but the syntax and scope are different.

  • User-level read-only is controlled through ALTER USER.
  • Session-level read-only is controlled through ALTER SESSION.

Consolidated SQL Reference

Use this single SQL block for understanding. 

--------------------------------------------------------------------------------
-- Oracle 26ai Read-Only Users and Sessions
-- Consolidated DBA Reference
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- 1. Create a local read-only user
--------------------------------------------------------------------------------

CREATE USER report_user
  IDENTIFIED BY "StrongPassword#2026"
  READ ONLY;

GRANT CREATE SESSION TO report_user;

GRANT SELECT ON app.orders TO report_user;
GRANT SELECT ON app.customers TO report_user;

--------------------------------------------------------------------------------
-- 2. Make an existing local user read-only
--------------------------------------------------------------------------------

ALTER USER app_support READ ONLY;

--------------------------------------------------------------------------------
-- 3. Allow the user to perform write operations again
--------------------------------------------------------------------------------

ALTER USER app_support READ WRITE;

--------------------------------------------------------------------------------
-- 4. Check read-only status of users
--------------------------------------------------------------------------------

SELECT username,
       account_status,
       read_only,
       common
FROM   dba_users
WHERE  username IN ('REPORT_USER', 'APP_SUPPORT');

--------------------------------------------------------------------------------
-- 5. Test read-only behavior from the application/support user
--------------------------------------------------------------------------------

-- Connect as the user
-- CONN app_support/password@app_pdb

-- Read operation should work
SELECT COUNT(*)
FROM   app.orders;

-- Write operation should fail if the user is read-only
UPDATE app.orders
SET    status = 'TEST'
WHERE  order_id = 1001;

-- Expected error:
-- ORA-28194: Can perform read operations only

--------------------------------------------------------------------------------
-- 6. Enable read-only mode for the current session
--------------------------------------------------------------------------------

-- Important:
-- Session must not have an active transaction.
-- Commit or rollback before enabling session-level read-only mode.

ROLLBACK;

ALTER SESSION SET READ_ONLY = TRUE;

--------------------------------------------------------------------------------
-- 7. Run read-only activity in the session
--------------------------------------------------------------------------------

SELECT customer_id,
       SUM(amount) AS total_amount
FROM   app.orders
GROUP  BY customer_id;

--------------------------------------------------------------------------------
-- 8. Disable read-only mode for the current session
--------------------------------------------------------------------------------

ALTER SESSION SET READ_ONLY = FALSE;

--------------------------------------------------------------------------------
-- 9. Check the READ_ONLY session parameter
--------------------------------------------------------------------------------

SELECT name,
       value,
       isses_modifiable
FROM   v$parameter
WHERE  name = 'read_only';

--------------------------------------------------------------------------------
-- 10. Audit changes related to read-only user management
--------------------------------------------------------------------------------

CREATE AUDIT POLICY audit_user_readonly_change
  ACTIONS CREATE USER, ALTER USER, DROP USER;

AUDIT POLICY audit_user_readonly_change;

SELECT event_timestamp,
       dbusername,
       action_name,
       sql_text
FROM   unified_audit_trail
WHERE  action_name IN ('CREATE USER', 'ALTER USER', 'DROP USER')
AND    UPPER(sql_text) LIKE '%READ ONLY%'
ORDER  BY event_timestamp DESC;

--------------------------------------------------------------------------------
-- 11. Simple before-and-after capture for change records
--------------------------------------------------------------------------------

SPOOL readonly_status_before.log

SELECT username,
       account_status,
       read_only,
       common
FROM   dba_users
WHERE  username IN ('APP_SUPPORT', 'REPORT_USER');

SPOOL OFF

-- Apply change
ALTER USER app_support READ ONLY;

SPOOL readonly_status_after.log

SELECT username,
       account_status,
       read_only,
       common
FROM   dba_users
WHERE  username IN ('APP_SUPPORT', 'REPORT_USER');

SPOOL OFF;

--------------------------------------------------------------------------------
-- 12. Revert after maintenance or validation
--------------------------------------------------------------------------------

ALTER USER app_support READ WRITE;

SELECT username,
       read_only
FROM   dba_users
WHERE  username = 'APP_SUPPORT';





  Where This Helps in Production




  This feature is very useful when you need temporary control without
    redesigning privileges.




  Good production use cases include:



    
  
  • 
    
      Application maintenance freeze
    
  
    • 
  
  • 
    
      Production investigation by support users
    
  
    • 
  
  • 
    
      Developer validation with read-only access
    
  
    • 
  
  • 
    
      Audit or compliance checks
    
  
    • 
  
  • 
    
      Temporary blocking of batch account writes
    
  
    • 
  
  • 
    
      Safe data comparison after migration
    
  
    • 
  
  • 
    
      Controlled testing of application read paths
    
  
    • 




  For example, before a release, DBAs may want to prevent manual changes from
    support users while still allowing them to validate data. Instead of
    removing grants, the user can be made read-only and later switched back to
    read-write mode.




  This makes the change simpler and easier to reverse.






  Troubleshooting ORA-28194




  When someone reports:



ORA-28194: Can perform read operations only



  the first question should not be:




  

    “Does the user have the required privilege?”
  





  The better first question is:




  

    “Is the user or session currently read-only?”
  





  Start by checking
    DBA_USERS.READ_ONLY.




  If the user is not marked read-only, then check whether the application is
    setting session-level read-only mode using:



ALTER SESSION SET READ_ONLY = TRUE



  This is especially important in JDBC, UCP, WebLogic, Tomcat, and other
    connection pool-based applications.




  The issue may not be a database privilege problem. It may be a session
    state problem.




Connection Pool Risk




  This is probably the biggest practical caveat.




  Session-level read-only mode is useful, but it can create problems if the
    application does not reset the connection properly.




  Example flow:



    
  
  1. 
    
      Application sets session to read-only.
    
  
    2. 
  
  2. 
    
      Reporting query runs successfully.
    
  
    3. 
  
  3. 
    
      Connection is returned to the pool.
    
  
    4. 
  
  4. 
    
      Another request picks the same connection.
    
  
    5. 
  
  5. 
    
      That request tries to perform DML.
    
  
    6. 
  
  6. 
    
      DML fails with ORA-28194.
    
  
    7. 




  From the DBA side, this can look confusing because the database user may
    show READ_ONLY = NO in
    DBA_USERS, but the session
    itself may still be read-only.




  For application teams, the rule should be very clear:




  

    If the application enables
      READ_ONLY=TRUE, it must
      reset it to
      READ_ONLY=FALSE before
      returning the connection to the pool.
  





What This Feature Does Not Do




  Read-only mode is not a performance feature.




  It does not make queries faster. It does not reduce CPU usage. It does not
    prevent large reports from consuming TEMP. It does not isolate reporting
    workload from OLTP workload.




  A read-only user can still run a very expensive query.




  So this feature should not be confused with reporting workload
    isolation.




  If the requirement is to protect production from reporting load, then DBAs
    should look at other architecture options such as:



    
  
  • 
    
      Active Data Guard reporting
    
  
    • 
  
  • 
    
      Read-only PDB clone
    
  
    • 
  
  • 
    
      Materialized views
    
  
    • 
  
  • 
    
      Separate reporting database
    
  
    • 
  
  • 
    
      Data warehouse or lakehouse platform
    
  
    • 




  Read-only users stop writes. They do not isolate reads.




Oracle vs PostgreSQL Comparison




  PostgreSQL handles this area differently.




  In PostgreSQL, read-only access is usually designed through roles and
    grants. A DBA creates a role, grants
    CONNECT, grants
    USAGE on schema, grants
    SELECT on tables, and
    manages default privileges for future objects.




  PostgreSQL also supports read-only transactions using
    BEGIN READ ONLY or session
    defaults like
    default_transaction_read_only.




  But this is not exactly the same as Oracle 26ai user-level read-only
    control.




  The key difference is this:




  

    PostgreSQL read-only access is usually privilege-design driven. Oracle
      26ai adds a read-only switch that can override existing write privileges
      for a local user.
  





  That makes Oracle 26ai useful for temporary operational control, especially
    when users already have complex privilege history.




Risks and Caveats




  DBAs should test this feature before using it in production.




  Some areas need attention:



    
  
  • 
    
      Existing sessions may need to be recycled depending on the application
      behavior.
    
  
    • 
  
  • 
    
      Application users may perform hidden writes such as audit inserts or login
      timestamp updates.
    
  
    • 
  
  • 
    
      Session-level read-only mode must be reset in connection pools.
    
  
    • 
  
  • 
    
      Automation must include rollback steps.
    
  
    • 
  
  • 
    ORA-28194 should be
      added to troubleshooting runbooks.
    
  
    • 
  
  • 
    
      This feature should not replace proper privilege design.
    
  
    • 




  A common mistake would be using this as a long-term fix for badly designed
    access. It is better treated as an additional safety control, not as a
    replacement for least privilege.




Quick Takeaways



    
  
  1. 
    
      Oracle AI Database 26ai introduces read-only control at user and session
      level.
    
  
    2. 
  
  2. 
    
      User-level
      READ ONLY applies to
      local users.
    
  
    3. 
  
  3. 
    
      Session-level
      READ_ONLY applies to the
      current session.
    
  
    4. 
  
  4. 
    
      Read-only restriction can override existing write privileges.
    
  
    5. 
  
  5. 
    
      Blocked write operations fail with
      ORA-28194.
    
  
    6. 
  
  6. 
    
      This is useful for maintenance, support access, testing, and audit
      scenarios.
    
  
    7. 
  
  7. 
    
      It does not improve query performance or isolate reporting workload.
    
  
    8. 




Conclusion




  Oracle 26ai read-only users and sessions give DBAs a practical control that
    was missing in earlier privilege-management patterns. Instead of revoking
    grants, rebuilding roles, or creating temporary users every time a support
    request comes in, DBAs can now temporarily suppress write capability at the
    user or session level.




  The strongest use case is production safety. During maintenance, release
    validation, audit checks, or troubleshooting, a user can keep existing
    access but lose the ability to change data. That is cleaner than emergency
    privilege changes and safer than trusting every session to behave
    correctly.




  But this feature needs discipline. User-level read-only changes should be
    captured, audited, and reverted through change control. Session-level
    read-only mode must be handled carefully in connection pools, otherwise one
    request can leave a session read-only for the next request.




  Also, this is not a reporting performance solution. Heavy
    SELECT statements can still
    affect production, even if the user is read-only.






  My recommendation is simple: test this in a lower environment, validate
    application behavior, add
    ORA-28194 to your
    troubleshooting checklist, and document a clear rollback step. Used
    properly, Oracle 26ai read-only users and sessions can become a useful
    safety switch for production DBAs. 

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/dbseg/release-changes.html#GUID-4E73B6DD-FC9F-44AA-9B06-C82C03CFEC3D

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/dbseg/configuring-privilege-and-role-authorization.html#GUID-52FCBBC1-C02B-4EC7-B462-77BA0D2240E7






























Labels:
,


















No comments:















Post a Comment


















        

      









Subscribe to:
Post Comments (Atom)