The READ-ONLY user cannot perform any DDL or DML activities.
Create a Read-Only PDB User: To create a Read-Only PDB use the new READ ONLY clause in the CREATE USER statement.
Connect to PDB user
SQL> ALTER SESSION SET CONTAINER = my_pdb;
Create readonly “hr_user”
SQL> CREATE USER hr_user IDENTIFIED BY passwordxxx READ ONLY;
SQL> CREATE USER hr_user IDENTIFIED BY passwordxxx READ ONLY;
Grant create session to hr_user
SQL> GRANT CREATE SESSION TO hr_user;
Note that the “hr_user” cannot be able to perform below tasks
SQL> GRANT CREATE SESSION TO hr_user;
Note that the “hr_user” cannot be able to perform below tasks
- User cannot run INSERT, DELETE, UPDATE or MERGE.
- User Cannot create or modify tables, indexs, views or procedures
- User cannot change roles or privileges
- User cannot modify session-level settings
Run below view to see the user is read-only or not
SQL> SELECT username, read_only from dba_users where username='HR_USER';
USERNAME READ_ONLY
-------------------- -----------------
HR_USER YES
SQL> Connect hr_user/paswordxxx;
Connected.
SQL> CREATE TABLE employee_test (emp_id number, emp_name varchar2(50));
*
ERROR at line 1:
ORA-28194: Can perform read operations only
SQL> DELETE FROM employee;
*
ERROR at line 1:
ORA-28194: Can perform read operations only
Note that READ-ONLY users can execute PL/SQL if it doesn’t have any DDL or DML.
The below procedure rev_salary has update statement and cannot perform the operation.
SQL> SELECT username, read_only from dba_users where username='HR_USER';
USERNAME READ_ONLY
-------------------- -----------------
HR_USER YES
SQL> Connect hr_user/paswordxxx;
Connected.
SQL> CREATE TABLE employee_test (emp_id number, emp_name varchar2(50));
*
ERROR at line 1:
ORA-28194: Can perform read operations only
SQL> DELETE FROM employee;
*
ERROR at line 1:
ORA-28194: Can perform read operations only
Note that READ-ONLY users can execute PL/SQL if it doesn’t have any DDL or DML.
The below procedure rev_salary has update statement and cannot perform the operation.
SQL> exec REV_SALARY;
ERROR at line 1:
ORA-28194: Can perform read operations only
ORA-06512: at "HR_USER.REV_SALARY", line 3
ORA-06512: at line 1
The READ-ONLY user can run a SELECT query without any issues.
SQL> SELECT emp_id, emp_name from employee;
EMP_ID EMP_NAME
-------------- ----------------------------
1 test_user1
2 test_user2
3 test_user3
SQL>
The Read-Only PDB Users provide a clean way to enforce non-modifiability of users at the database level. This helps with read intensive applications, as these users restricted to only SELECT and users cannot perform any DDL or DML activities.
Thanks & Regards,
