Oracle grant select from One schema to other schema

set heading off pagesize 0 feedback off echo off verify off linesize 200

ACCEPT schema_name CHAR prompt 'SCHEMA_NAME refers to the schema to duplicate privileges from :'
ACCEPT grantee_name CHAR prompt 'GRANTEE_NAME refers to the SCHEMA/role to grant privileges to :'

spool new_&&grantee_name&&schema_name..sql
spool &&grantee_name&&schema_name..sql

select (CASE
WHEN
object_type in ('VIEW','MATERIALIZED VIEW') THEN 'GRANT SELECT on '
WHEN
OBJECT_TYPE IN ('SEQUENCE') THEN 'GRANT SELECT ON '
WHEN
OBJECT_TYPE LIKE 'DIRECTORY' THEN 'GRANT READ ON DIRECTORY '
ELSE 'NOT AN APPROPRIATE OBJECT'END ) ||OWNER||'."'||OBJECT_NAME||'" TO &&grantee_name ;'
from dba_objects a
where owner='&&schema_name'
AND OBJECT_TYPE IN ('DIRECTORY','VIEW','MATERIALIZED VIEW','SEQUENCE')
and not exists (select 1 from dba_tab_privs b where a.object_name=b.table_name
and b.owner=a.owner and b.grantee='&&grantee_name');



-- Now we do tables...we did not do tables from all objects because we need to

-- limit it to tables and not external tables or nested tables



select 'grant select on '||a.owner||'."'||a.table_name||'" to &&grantee_name ;' from dba_tables a
where owner='&&schema_name' and a.table_name not in
(select axt.table_name from dba_external_tables axt
where axt.owner=a.owner)
and a.table_name not in
(select nst.table_name from dba_nested_tables nst
where nst.owner=a.owner)
and not exists (select 1 from dba_tab_privs b where
a.table_name=b.table_name
and b.owner=a.owner
and b.grantee='&&grantee_name');

-- Now we do the external tables

select 'grant select on '||owner||'."'||table_name||'" to &&grantee_name ;' from dba_external_tables a
where owner='&&schema_name'
and not exists (select 1 from dba_tab_privs b where a.table_name=b.table_name
and b.owner=a.owner and b.grantee='&&grantee_name');

-- Now we tackle objects not owned by the schema_name
-- exclude directories because they require a DIRECTORY key word in grant

select 'grant select on ' || owner||'."'||table_name||'" to &&grantee_name ;'
from dba_tab_privs a
where grantee='&&schema_name' and not exists (select 1 from dba_tab_privs b
where a.privilege=b.privilege and a.privilege='SELECT'
and a.table_name=b.table_name and b.grantee='&&grantee_name')
and not exists (select 1 from dba_directories c
where c.directory_name=a.table_name
and a.owner=c.owner)
;



spool off;
set heading on verify on feedback on echo on
prompt check script &&grantee_name&&schema_name..sql
undef schema_name
undef grantee_name