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.