Oracle Database 23c introduces significant enhancements to read-only controls, offering greater flexibility and security in managing user access and session behavior. This article explores these new features and demonstrates their advantages over previous approaches.
Key Features:
- READ_ONLY Session Parameter: In Hybrid Read-Only mode, a PDB operates in either read-only or read-write mode depending on the user type:
- Applies to any user type in any container.
- Enabled/disabled dynamically using ALTER SESSION SET READ_ONLY = [TRUE|FALSE]
- Takes precedence over user privileges.
- READ_ONLY User Property:
- Applies specifically to local users.
- Set during user creation or with ALTER USER <username> READ ONLY.
Enabling and Disabling READ_ONLY Session:
You set make the current session in read only mode using the following statement:
ALTER SESSION SET READ_ONLY = TRUE;Once this statement is executed, the user cannot create or modify data in the database.
You can set the session back into read/write mode using the following statement:
ALTER SESSION SET READ_ONLY = FALSE;Needless to say that this statement is useless if the PDB is running in read only mode.
Creating a READ_ONLY User:
You can create a database user with read only flag using the following statement. Such a user is able only to retrieve data from the database objects.
CREATE USER read_only_user IDENTIFIED BY password READ ONLY;The flag can be turned on/off for existing database users using the following statement:
ALTER USER existing_user READ ONLY;Challenges in Pre-23c Versions:
Restricting write operations for specific users or sessions required complex workarounds. The common practice was to start the entire PDB in read only mode.
Advantages of Oracle 23c Read-Only Controls:
- Simplicity: Dynamically control read-only behavior without complex privilege management.
- Flexibility: Temporarily restrict write access for testing, administration, or application development.
- Security: Enforce read-only access for specific users or sessions without compromising overall privileges.
- Application Development: Tailor read/write behavior within different application components using the same user or session.