Who this is for: anyone who’s been asked to prove a reporting or analytics account is truly read-only, and realized that a pile of privilege grants was never actually a guarantee of that.
A security review asked us to list every account with write access to a production schema, and the reporting account was supposed to be the easy one to clear. SELECT only, no INSERT, UPDATE, or DELETE grants, set up that way for years. Except when I actually queried DBA_ROLE_PRIVS for that account, it also had db_developer_role attached, granted about eighteen months earlier by someone troubleshooting a one-off issue and never revoked. db_developer_role carries object creation and DML privileges well past SELECT. Nobody had used them. But “nobody has used them” and “nobody can use them” are two different guarantees, and the auditor wanted the second one, in writing.
That’s what sent me looking at Oracle 23ai’s read-only user feature, a genuinely new enforcement mechanism, not just another privilege to grant. I turned it on for that account the same week, feeling good about finally having a real answer for the auditor. Two nights later a scheduled report job failed at 2 a.m. with ORA-28194, because the report procedure staged its intermediate results in a global temporary table before running the final SELECT, and that staging step is itself DML.
Why This Matters
Before 23ai, “read-only” in Oracle was always a privilege story. You either withheld INSERT, UPDATE, and DELETE grants, or granted the narrower READ object privilege introduced in 12c instead of SELECT, since READ lets a user query a table but not lock its rows with SELECT … FOR UPDATE. Both approaches work, right up until someone grants a broader role to that account for an unrelated reason and forgets to revoke it. Privilege-based read-only is only as strong as the discipline of everyone who ever touches that account’s grants, and in a database that’s been running for years, that discipline eventually slips.
Oracle 23ai adds a second, independent mechanism: a READ ONLY state on the user account itself, set with CREATE USER or ALTER USER. It doesn’t touch what the account is granted. Instead, it makes every session that account opens behave as if the database were open in read-only mode, so any DML the session attempts fails, regardless of what privileges are sitting in DBA_ROLE_PRIVS. It’s the difference between telling someone not to write on the whiteboard and taking the marker away entirely.
Reproducing the privilege-based gap
You can see the old failure mode directly. Set up an account the traditional way, with only SELECT granted, then watch a later, unrelated grant quietly restore write access.
create user rpt_user identified by RptUserPwd#2026;grant create session to rpt_user;grant select on app.orders to rpt_user;-- months later, someone troubleshooting a build issue does this-- and never comes back to revoke itgrant db_developer_role to rpt_user;conn rpt_user/RptUserPwd#2026insert into app.orders (order_id, status) values (999999, 'test');-- succeeds. no error. no warning. "read-only" was never enforced,-- only assumed.
Nothing in that sequence is unusual. It’s exactly the kind of grant that gets added under time pressure and never audited again, and it’s precisely what the security review was worried about.
Setting up and testing an account-level read-only user
The 23ai feature is a PDB user attribute, set with a READ ONLY or READ WRITE clause on CREATE USER or ALTER USER. It’s checked with a new READ_ONLY column on DBA_USERS.
alter user rpt_user read only;select username, read_onlyfrom dba_userswhere username = 'RPT_USER';USERNAME READ_ONLY----------- ----------RPT_USER YES
With that flag set, the same account, still carrying db_developer_role, still granted SELECT, INSERT, whatever you left in place, can no longer write.
conn rpt_user/RptUserPwd#2026insert into app.orders (order_id, status) values (999999, 'test');*ERROR at line 1:ORA-28194: Can perform read operations onlycreate table scratch (id number);*ERROR at line 1:ORA-28194: Can perform read operations onlyselect order_id from app.orders where order_id = 1 for update;*ERROR at line 1:ORA-28194: Can perform read operations only
Notice the third one. SELECT … FOR UPDATE fails too, which is a stricter guarantee than the old READ privilege gave you on its own, since READ already blocked row locking but only on the objects it was explicitly granted against. The account-level flag blocks it everywhere, on every object, no matter what’s granted. Querying still works fine, and so does executing PL/SQL, as long as the PL/SQL itself doesn’t attempt DML. A procedure that only reads and returns data runs normally under a read-only session; a procedure with an INSERT or UPDATE buried in it fails with the same ORA-28194, at the exact line where the write happens.
What actually broke, and the fix
That last detail is what caught our report job. The nightly report built a multi-step summary by inserting intermediate rows into a global temporary table, then ran the final SELECT against it, a completely normal pattern for anything more complex than a single query can express cleanly. But an INSERT into a GTT is still DML. The read-only restriction doesn’t check which table you’re writing to or whether the data is session-private; it blocks the statement itself. So the procedure died on its own staging insert, long before it ever touched a table anyone would call sensitive.
The fix wasn’t to loosen the read-only flag. It was to stop asking a read-only account to write at all, even to its own scratch space. I rewrote the staging logic as a set of WITH clause subqueries feeding the final SELECT directly, which is what it should have been in the first place; the GTT was a convenience from years ago, not a requirement. Where a report’s logic is genuinely too complex to express without a staging step, the better long-term pattern is to run that staging phase under a separate, narrowly-scoped loader account that still has write access to its own private schema, and keep the read-only flag on the account the application and its users actually connect through. That keeps the guarantee the security review wanted intact for the account that matters, without pretending every internal ETL job can be rewritten as a single query overnight.
Where This Requires Care
This is documented specifically as a PDB user attribute; it’s set per pluggable database user, so factor that into how you plan it across a multitenant environment rather than assuming one flag covers a whole CDB.
It’s an all-or-nothing switch for the account, not a table-level control. You can’t flip it on for most objects and leave one audit table writable; if any part of what the account does genuinely needs to write, that logic has to live somewhere else, under a different account.
It doesn’t replace privilege hygiene, it adds a second layer on top of it. Flip the account back to READ WRITE and every privilege it was ever granted, including the ones nobody remembers granting, is immediately usable again. Keep pruning grants as you normally would.
Audit every code path the account actually exercises before flipping this on in production, not just the queries you expect it to run. Scheduled jobs, background procedures, and anything with an implicit staging step are exactly where a surprise DML statement hides, and ORA-28194 will find it for you, usually at the worst possible time, as it did for us.
Test the change against a copy of the production workload first if you can, particularly for accounts tied to scheduled or background jobs rather than interactive users, since those are the ones least likely to get exercised again before the next scheduled run finds the problem for you.
Quick Reference
- ALTER USER username READ ONLY (or READ WRITE) sets an account-level flag, independent of any privileges granted; check it via DBA_USERS.READ_ONLY.
- A read-only account gets ORA-28194 on any DML, any DDL, and any SELECT … FOR UPDATE, even against objects it has explicit write privileges on.
- The restriction blocks the statement, not the target: writes to global temporary tables and other scratch objects fail exactly like writes to permanent tables.
- PL/SQL that only reads runs fine under a read-only session; PL/SQL with any embedded DML fails at that line with the same error.
- This is a PDB user attribute and an all-or-nothing account switch, not a per-table control; genuinely write-needing logic belongs on a separate account.
My Take
I like having an actual, enforced guarantee to hand the next auditor instead of a privilege list and a promise that nobody granted anything extra. That’s a real improvement over how Oracle read-only accounts worked for the last twenty-some years. But the 2 a.m. page was a useful reminder that “read-only” at the account level means something more absolute than most of us have been assuming it meant, and any app or job that’s been quietly relying on scratch writes for a long-established workaround needs an actual look before you flip the switch, not after.
