DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Oracle 23ai — Read-Only User Feature

#oracle #sql #linux #database

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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

Changing Back to Read-Only State
The user state can also be reverted to read-only:

SQL> alter user VAHID read only;
User altered.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Note: This feature is not supported for common users.

VAHID YOUSEFZADEH
Oracle database Administrator
telegram channel ID:@oracledb

Top comments (0)