Granting everything from one schema to another

Creation of a readonly user/Schema in production.  

Many time you face issue when you need to provide readonly access to one schema to another.

Here is the simple way to give access. 

Script to create user and give dictonary grant

create user fusion_read_only_user identified by readonly;

grant create session to fusion_read_only_user ;

grant select any table to fusion_read_only_user ;

This will only grant read-only to soruce schama tables, you would need to connect to another schema owner to grant them read-only access.  Optionally, you can add read-only dictionary acces:

grant select any dictionary to fusion_read_only_user 

Script to grant select on all tables 

select 

concat(concat('GRANT SELECT ON EMR_INTEGRATION.', TABLE_NAME), ' to EMR_INTEGRATION_RO;')

From all_tables

where owner = 'Source Schema'

Script to grant select on all packages

select 'GRANT DEBUG ON ' || OBJECT_NAME || ' to EMR_INTEGRATION_RO;'

from

all_objects

WHERE OBJECT_TYPE = 'PACKAGE'

and  OWNER = 'Source Schema'

;

Script to grant select on all Types

select 'GRANT DEBUG ON ' || OBJECT_NAME || ' to EMR_INTEGRATION_RO;'

from

all_objects

WHERE OBJECT_TYPE = 'TYPE'

and  OWNER = 'Source Schema'

;

Script to grant select on all Views

select 'GRANT SELECT ON ' || OBJECT_NAME || ' to EMR_INTEGRATION_RO;'

from

all_objects

WHERE OBJECT_TYPE = 'VIEW'

and  OWNER = 'Source Schema'

;