Blogs

Oracle 23ai: Read-Only Users and Sessions

Overview:

• Oracle 23ai Database introduces a new feature to control whether a user or session is enabled for read-write operations, irrespective of the privileges of the user that is connected to the database
• The READ_ONLY user only applies to a PDB local database user
• The READ_ONLY session applies to any type of user for any type of container
• The capability to disable and re-enable the read-write capabilities of any user or session without revoking and re-granting privileges provides you with more flexibility to temporarily control the privileges of users or sessions for testing, administration, or application development purposes
• It also gives you a simple way to control the read-write behavior within different parts of an application that are used by the same user or session
• You can set the access of a local user to a PDB to READ ONLY or READ WRITE with the ALTER USER or CREATE USER statement
• After READ ONLY access is enabled for a PDB user, whenever that user connects to the PDB, the session operates as if the database is open in read-only mode and the user cannot perform any write operation
• A new column (READ_ONLY) has been added to dictionary views *_USERS to show if READ_ONLY is enabled/disabled for a database user

In this blog, I’ll demonstrate enable/disable READ ONLY for a PDB local database user.

 

Prerequisites:

• Oracle Database 23ai Free Developer Release
• A pluggable database with HR database schema

 

A Demo Steps:

1. Connect to a PDB using HR local database user and run a DML statement.

– Dictionary view *_USERS currently shows that READ_ONLY is disabled for HR user.

 

2. Connect to a PDB using a SYSTEM common user and enable READ ONLY for HR users using the below SQL Command.

SQL> alter user &USERNAME read only;

 

3. Reconnect to a PDB using the HR local database user and run a DML statement.

– Dictionary View *_USERS currently shows that READ_ONLY is enabled for HR user.

– You will get the below error message.

SQL Error: ORA-28194: Can perform read operations only

 

4. Using the SYSTEM Database Session, disable READ ONLY for HR users using the below SQL Command.

SQL> alter user &USERNAME read write;

 

5. Reconnect to a PDB using the HR Local Database User and run a DML statement. DML statement will run without error.

– Dictionary view *_USERS currently shows that READ_ONLY is disabled for HR user.

More from this Author