Friday, July 22, 2011
Create Read-only user for a Schema
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 . Here is demo for creating read-only users.
In this demo, I have created a user name “neer” and given access to select only “hr” schemas tables.
Step 1 : Create User “NEER”
SQL> create user neer identified by neer;
Step 2 : Grant session and create synonym privileges
SQL> grant create session ,create synonym to neer ;
Step 3 : Make script to grant select privileges to neer
Here I have created the script with the help of spool by selecting the tables of the HR schemas .
SQL> SPOOL C:\select_privs.sql
SQL> select 'grant select on hr.'||table_name|| ' to neer;' from dba_tables where owner='HR';
SQL> select 'grant select on hr.'||view_name|| ' to neer;' 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
The script “select_privs.sql” script after modification is .
grant select on hr.REGIONS to neer;
grant select on hr.LOCATIONS to neer;
grant select on hr.DEPARTMENTS to neer;
grant select on hr.JOBS to neer;
grant select on hr.EMPLOYEES to neer;
grant select on hr.JOB_HISTORY to neer;
grant select on hr.COUNTRIES to neer;
grant select on hr.EMP_DETAILS_VIEW to neer;
Step 5 : Create the synonym
Connect to “NEER” schemas and create the synonym so that the “NEER” 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 neer/neer@noida
SQL> SPOOL C:\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
Now we have successfully created a read-only user for a particular schemas.
Step 6 : Check the tables
SQL> select Count(*) from employees ;
SQL> select Count(*) from hr.employees;
SQL> sho user
USER is "NEER"