DBA Hub

📋Steps in this guide1/2

Read-Only PDB Users in Oracle Database 23ai/26ai

Oracle database 23ai/26ai 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.

oracle 23configurationintermediate
by OracleDba
21 views
1

Read-Only Users

We create a new test user and make it read-only. We grant to the user, which gives it lots of object creation privileges. We check the view and we can see the user is read-only. We connect to the test user and try a DDL statement, which fails. We switch the test user to read-write. We connect to the test user and try some DDL and DML statements, which all work as expected. We switch the test user to read-only again. We connect to the test user and try some DML actions, which all fail. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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;

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>

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

create table t1 (id number);
*
ERROR at line 1:
ORA-28194: Can perform read operations only


SQL>

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

alter user testuser2 read write;

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>

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

alter user testuser2 read only;

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>
2

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. We connect to the test user and try to execute the procedures. Notice that the second procedure, which contains DML, fails. The read-only user also stops us from performing actions like , as shown below. Check out Pete Finnigan's post about Read Only Users in 23ai/26ai . For more information see: - Setting Read Only Access for a PDB User - Read Only Users in 23ai Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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;
/

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!