Read-Only PDB Users in Oracle Database 23c

Oracle database 23c allows us to make PDB users read-only, which makes a connected session act like the database is opened in read-only mode, preventing the session from performing write operations.

Read-Only Users

We create a new test user and make it read-only. We grant DB_DEVELOPER_ROLE to the user, which gives it lots of object creation privileges.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop user if exists testuser2 cascade;

create user testuser2 identified by testuser2 quota unlimited on users read only;

grant db_developer_role to testuser2;

We check the DBA_USERS view and we can see the user is read-only.

column username format a20

column read_only format a10

select username,

       read_only

from   dba_users

where  username = 'TESTUSER2';

USERNAME             READ_ONLY

-------------------- ----------

TESTUSER2            YES

SQL>

We connect to the test user and try a DDL statement, which fails.

conn testuser2/testuser2@//localhost:1521/freepdb1

create table t1 (id number);

*

ERROR at line 1:

ORA-28194: Can perform read operations only

SQL>

We switch the test user to read-write.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

alter user testuser2 read write;

We connect to the test user and try some DDL and DML statements, which all work as expected.

conn testuser2/testuser2@//localhost:1521/freepdb1

SQL> create table t1 (id number);

Table created.

SQL> insert into t1 values (1), (2), (3);

3 rows created.

SQL> update t1 set id = id;

3 rows updated.

SQL> delete from t1 where id = 3;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

We switch the test user to read-only again.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

alter user testuser2 read only;

We connect to the test user and try some DML actions, which all fail.

conn testuser2/testuser2@//localhost:1521/freepdb1

SQL> insert into t1 values (3);

                 *

ERROR at line 1:

ORA-28194: Can perform read operations only

SQL> update t1 set id = id;

            *

ERROR at line 1:

ORA-28194: Can perform read operations only

SQL> delete from t1 where id = 3;

                 *

ERROR at line 1:

ORA-28194: Can perform read operations only

SQL> select * from t1;

        ID

----------

         1

         2

SQL>

We can see that when the user is set to read-only we can't run DDL or DML statements, but we can still query the objects.

Execute PL/SQL

A read-only user can execute any PL/SQL so long as it doesn't perform DDL or DML.

We connect to a privileged user and create two procedures, one of which performs some DML.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

create or replace procedure testuser2.my_proc1 as

begin

  dbms_output.put_line('Hello');

end;

/

create or replace procedure testuser2.my_proc2 as

begin

  insert into t1 values (4);

  commit;

end;

/

We connect to the test user and try to execute the procedures. Notice that the second procedure, which contains DML, fails.

conn testuser2/testuser2@//localhost:1521/freepdb1

SQL> set serveroutput on

SQL> exec my_proc1;

Hello

PL/SQL procedure successfully completed.

SQL>

SQL> exec my_proc2;

*

ERROR at line 1:

ORA-28194: Can perform read operations only

ORA-06512: at "TESTUSER2.MY_PROC2", line 3

ORA-06512: at line 1

SQL>

The read-only user also stops us from performing actions like SELECT ... FOR UPDATE, as shown below.

declare

  l_id  number;

begin

  select id

  into   l_id

  from   t1

  for update;

end;

/

*

ERROR at line 1:

ORA-28194: Can perform read operations only

ORA-06512: at line 4

SQL>

Source: oracle-base.com