The Read-Only User is one of the new features introduced in Oracle Database version 23ai. This feature enables administrators to restrict users from making any data modifications, even if they have the required privileges for INSERT, DELETE, or UPDATE operations. Additionally, users cannot create objects such as tables, views, or procedures, even if they hold the necessary permissions, because creating an object involves altering data in the Data Dictionary tables.
When a user is in the Read-Only state, they can only connect to the database, query table data, or execute procedures, functions, and packages that do not make any changes.
Creating a Read-Only User
A user can be set to the read-only state at the time of creation:
SQL> create user VAHID identified by abc read only;
User created.
By granting the CREATE SESSION privilege, the user VAHID can connect to the database:
SQL> grant create session to VAHID;
Grant succeeded.
SQL> conn VAHID/abc@target:1521/RANPDB
Connected.
Even if VAHID is granted permissions to create tables or insert data, they cannot perform these actions while in the read-only state:
SQL> show user
USER is "SYS"
SQL> grant create table,unlimited tablespace,insert any table to VAHID;
Grant succeeded.
SQL> conn VAHID/abc@target:1521/RANPDB
Connected.
SQL> create table tbl1(id number);
'ORA-28194: Can perform read operations only'
SQL> insert into reza.tb values(1);
'ORA-28194: Can perform read operations only'
Changing User State to Read Write
To allow VAHID to perform data modifications, the user state can be changed to read-write:
SQL> show user
USER is "SYS"
SQL> alter user VAHID read write;
User altered.
After this change, the user VAHID can create tables and insert data:
SQL> conn VAHID/abc@target:1521/RANPDB
Connected.
SQL> create table tbl1(id number);
Table created.
SQL> insert into tbl1 values(1);
1 row created.
Changing Back to Read-Only State
The user state can also be reverted to read-only:
SQL> alter user VAHID read only;
User altered.
In this state, VAHID can query tables if they have the SELECT privilege:
SQL> grant select any table to VAHID;
Grant succeeded.
SQL> conn VAHID/abc@target:1521/RANPDB
Connected.
SQL> select * from reza.tb;
ID
----------
1
Additionally, they can execute procedures that do not perform data modifications:
SQL> grant execute any procedure to VAHID;
Grant succeeded.
SQL> conn VAHID/abc@target:1521/RANPDB
Connected.
SQL> exec REZA.prc1(10);
20
PL/SQL procedure successfully completed.
Viewing Read-Only Users
To list users currently in the read-only state, the following query can be used:
SQL> select username from dba_users where read_only='YES';
USERNAME
-----------
VAHID
Note: This feature is not supported for common users.
VAHID YOUSEFZADEH
Oracle database Administrator
telegram channel ID:@oracledb