8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Read-Only PDB Users in Oracle Database 23ai
Oracle database 23ai 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 23ai.
For more information see:
Hope this helps. Regards Tim...