DBA Hub

📋Steps in this guide1/3

Read-only user Creation in Oracle

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

oracle configurationintermediate
by OracleDba
12 views
1

Overview

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. Created the script with the help of spool by selecting the tables of the HR schemas . Now , we will check the spool “select _privs.sql” and prepare this as script for grant permission.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
SQL> create user PDBUSER identified by PDBUSER;
User created.

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

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
2

Section 2

The script “select_privs.sql” script after modification is . 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. Now we have the script to create the synonym

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> @select_privs.sql

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;

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
3

Section 3

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL>@synonym_privs.sql

SQL> select Count(*) from employees ;

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

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

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

SQL> show user
USER is "PDBUSER

Comments (0)

Please to add comments

No comments yet. Be the first to comment!