8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23c » Here

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>

Check out Pete Finnigan's post about Read Only Users in 23c.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.