In earlier Oracle Database releases, we could set a database or tablespace or table to be read-only.
A new feature added in Oracle Database 23ai is the ability to configure READ ONLY access for a PDB user via READ ONLY clause in ALTER USER or CREATE USER statements.
After read-only access is enabled for a particular user, the user’s connected session operates as if the database is open in read-only mode and the user cannot perform any type of DML operations.
Let us look at an example of the READ ONLY User new feature.
Note that this option is not available in Oracle Database 19c and returns an error when we issue the ALTER USER READ ONLY command.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> alter user lab read only;
alter user lab read only
*
ERROR at line 1:
ORA-00922: missing or invalid option
Create the user – note the new DB_DEVELOPER_ROLE role.
[oracle@db23c bin]$ sqlplus system/Oracle_4U@freepdb1
SQL*Plus: Release 23.0.0.0.0 - Production on Thu Nov 9 19:54:10 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Thu Nov 09 2023 19:53:37 -05:00
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> create user USER1 identified by Oracle_4U;
User created.
SQL> grant db_developer_role to USER1;
Grant succeeded.
SQL> alter user USER1 quota unlimited on users;
User altered.
SQL> select read_only from dba_users where username='USER1';
REA
---
NO
Connect as USER1 and create the test table.
[oracle@db23c bin]$ sqlplus user1/Oracle_4U@freepdb1
SQL*Plus: Release 23.0.0.0.0 - Production on Thu Nov 9 19:56:13 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> create table mytab as select * from all_tables;
Table created.
SQL> quit
Disconnected from Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
Issue the ALTER USER command to make USER1 a read-only user. Note that the read-only attribute could have been assigned to the user as part of the CREATE USER statement as well.
[oracle@db23c bin]$ sqlplus system/Oracle_4U@freepdb1
SQL*Plus: Release 23.0.0.0.0 - Production on Thu Nov 9 19:57:04 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Thu Nov 09 2023 19:54:10 -05:00
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> alter user USER1 read only;
User altered.
SQL> select read_only from dba_users where username='USER1';
REA
---
YES
DML statements issued by the user now return an error – “ORA-28194: Can perform read operations only”.
SELECT queries issued by the user still complete successfully.
[oracle@db23c bin]$ sqlplus user1/Oracle_4U@freepdb1
SQL*Plus: Release 23.0.0.0.0 - Production on Thu Nov 9 19:57:23 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Thu Nov 09 2023 19:56:13 -05:00
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> delete mytab;
delete mytab
*
ERROR at line 1:
ORA-28194: Can perform read operations only
Help: https://docs.oracle.com/error-help/db/ora-28194/
SQL> select count(*) from mytab;
COUNT(*)
----------
120
