Read-only user Creation in Oracle

• Ashiwini
• 26 Jul, 2024
• 0 Comments
• 2 Mins Read

Read-only user Creation in Oracle

Sometimes the DBA requires  to create the read-only user for a particular schemas.While creating the user,one should always keep in mind that the user should have minimum privileges.  There is no any direct command to create the read-only user in oracle.
Sometimes I find people grants  “read any table ”  privileges to create the read-only  user ,which is not correct.

Step 1 : Create User PDBUSER

SQL> create user PDBUSER identified by PDBUSER;
User created.

Step 2 : Grant session and create synonym privileges

SQL> grant create session ,create synonym to PDBUSER ;
Grant succeeded.

Step 3 : Make script to grant select privileges to PDBUSER

Created the script with the help of spool by selecting the tables of the HR schemas .

SQL> SPOOL select_privs.sql
SQL> select 'grant select on hr.'||table_name|| ' to PDBUSER;' from dba_tables where owner='HR';
SQL> select 'grant select on hr.'||view_name|| ' to PDBUSER;' from dba_views where owner='HR';
SQL> spool off

Now , we will check the spool “select _privs.sql” and prepare this as script for grant permission.

Step 4 : Run the script to grant the permission

SQL> @select_privs.sql

The script “select_privs.sql” script after modification is .

grant select on hr.REGIONS to PDBUSER; 
grant select on hr.LOCATIONS to PDBUSER; 
grant select on hr.DEPARTMENTS to PDBUSER; 
grant select on hr.JOBS to PDBUSER; 
grant select on hr.EMPLOYEES to PDBUSER; 
grant select on hr.JOB_HISTORY to PDBUSER; 
grant select on hr.COUNTRIES to PDBUSER; 
grant select on hr.EMP_DETAILS_VIEW to PDBUSER;

Step 5 : Create the synonym

Connect to “PDBUSER” schemas and create the synonym so that the “PDBUSER” user can access the “HR” schemas without any dot(.) like select * from employees instead of “select * from HR.employees” .Here again we use the above method.

SQL> conn PDBUSER/PDBUSER@orcl
SQL> SPOOL synonym_privs.sql
SQL> select 'create synonym '||view_name|| ' for HR.'||view_name||';' from all_views where owner='HR';
SQL> select 'create synonym '||table_name|| ' for HR.'||table_name||';' from all_tables where owner='HR';
SQL> spool off

Now we have the script to create the synonym

SQL>@synonym_privs.sql

Now we have successfully created a read-only user for a particular schemas.

Step 6 : Check the tables

SQL> select Count(*) from employees ;

COUNT(*)
----------
107

SQL> select Count(*) from hr.employees;

COUNT(*)
----------
107

SQL> show user
USER is "PDBUSER