Read-only user Creation.

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 user name Whale and given  access to select only  HR  schemas tables.

Step  1  :  Create User  WHALE

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

Step  2  :  Grant session and create synonym privileges

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

Step  3  :  Make script to grant  select privileges to whale 

reated 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 whale;' from dba_tables where owner='HR';
SQL> select 'grant select on hr.'||view_name||  '  to whale;' 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 whale;                                             
grant select on hr.LOCATIONS to whale;                                           
grant select on hr.DEPARTMENTS to whale;                                         
grant select on hr.JOBS to whale;                                                
grant select on hr.EMPLOYEES to whale;                                           
grant select on hr.JOB_HISTORY to whale;                                         
grant select on hr.COUNTRIES to whale;                                                                
grant select on hr.EMP_DETAILS_VIEW to whale;                                    

Step  5  :  Create the synonym   
 
Connect to "whale" schemas and create the synonym so that the "whale" 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 whale/whale@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 "whale"