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'
;
