Tuesday, December 17, 2013

How to grant a user read-only rights to a schema in Oracle 11g

There are two ways to grant a user read-only privileges on a single schema in Oracle:

1) Retrieve all the objects and grant SELECT privileges on each object to the user in question

2) Create a role and to that role grant SELECT privileges on each object in the schema and then grant that role to the user.

Script below can spool all the necessary GRANT statements to a file, given username of the user (or role) to grant priileges to and given the nschema name:
set pages 0;
set linesize 100;
set feedback off; 
set verify off; 


SELECT 'GRANT SELECT ON ' || table_name || ' TO &&new_user;' FROM dba_tables WHERE owner=upper('&&schema_name');
SELECT 'GRANT SELECT ON ' || view_name || ' TO &&new_user;' FROM dba_views WHERE owner=upper('&&schema_name');
set serveroutput off;
spool off;

No comments:

Post a Comment