--Script to create the PPSNEPL_READ_ONLY role.
--Provides read only access to PPSNEPL schema objects.

DROP ROLE PPSNEPL_READ_ONLY_ROLE;

CREATE ROLE PPSNEPL_READ_ONLY_ROLE NOT IDENTIFIED;

--suppress display of messages for building command file
SET TERM      OFF 
SET ECHO      OFF 
SET HEADING   OFF 
SET FEEDBACK  OFF 
SET PAGESIZE  0   
SET LINESIZE  1000 
SET VERIFY    OFF 

spool grant_PPSN_read_only_role.sql
SELECT  'GRANT SELECT ON PPSNEPL.'  || TABLE_NAME||  ' TO  PPSNEPL_READ_ONLY_ROLE; ' FROM ALL_TABLES WHERE OWNER ='PPSNEPL';
spool off

--reset display of messages for verification
SET TERM      ON 
SET ECHO      ON 
SET HEADING   ON 
SET FEEDBACK  ON 
SET PAGESIZE  300  
SET VERIFY    ON

spool grant_PPSN_read_only_role.log
start  grant_PPSN_read_only_role.sql
GRANT PPSNEPL_READ_ONLY_ROLE TO SYSTEM WITH ADMIN OPTION;
spool off


