Generate Grant Select Statements (grant_select.gen)

What This Script Does

This generator script creates GRANT SELECT statements to provide read access on tables to specified users or roles. It excludes system schemas for safety.

The Script

rem  grant_select.gen
rem
select 'grant select on ' || owner || '.' || table_name ||
       ' to ' || upper('&grantee') || ';'
  from sys.dba_tables
 where owner like upper('&owner')
   and table_name like upper('&table_name')
   and owner not in ('SYS','SYSTEM','MDSYS','CTXSYS','XDB','ORDDATA','APEX_040200')
 order by owner, table_name
/

Usage

-- Basic usage
@grant_select.gen

-- When prompted, enter:
-- owner: Schema owner pattern (use % for wildcard)
-- table_name: Table name pattern (use % for wildcard)
-- grantee: User or role to receive the grant

Parameters

The script prompts for:

&owner - Schema owner pattern (use % for wildcard)
&table_name - Table name pattern (use % for wildcard)
&grantee - User or role to receive the grant

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_TABLES
-- Plus GRANT privilege on the tables

Sample Output

grant select on HR.EMPLOYEES to READ_ONLY_ROLE;
grant select on HR.DEPARTMENTS to READ_ONLY_ROLE;
grant select on HR.JOBS to READ_ONLY_ROLE;

Key Features

Flexible Filtering: Use wildcards to grant on multiple tables
System Schema Protection: Excludes critical system schemas
Clean Syntax: Simple GRANT statements ready for execution
Ordered Output: Sorted by owner and table name

Common Use Cases

Grant Read Access to All Tables in Schema

@grant_select.gen
-- Enter: HR for owner, % for table_name, READ_ONLY_ROLE for grantee

Grant Access to Specific Table Pattern

@grant_select.gen
-- Enter: % for owner, %_LOOKUP for table_name, APP_USER for grantee

Revoke Privileges Generator - Revoke granted privileges
Create Synonym Generator - Create synonyms after granting access