Grant read only access to user

GRANT Read only (select) for Other users on second Schema

SPOOL select_privs.sql
select 'grant select on abc.'||table_name|| '  to readonly;' from dba_tables where owner='ABC';

select 'grant select on abc.'||view_name||  '  to readonly;' from dba_views where owner='ABC';
spool off

Modify the script and run it
@select_privs.sql

OR

SELECT 'GRANT ALL ON '||table_name||' TO XYZ;'
FROM   ALL_TABLES
WHERE  OWNER = 'ABC';

OR

You could put a package in each schema that you need to issue the grant from which will go through
all call each GRANT statement via an EXECUTE IMMEDIATE.


e.g.

   PROCEDURE GRANT_TABLES
   IS
   BEGIN

      FOR tab IN (SELECT table_name
                  FROM   all_tables
                  WHERE  owner = ABC) LOOP
         EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON '||tab.table_name||' TO XYZ';
      END LOOP;
   END;


or you can do it as under

BEGIN
  FOR x IN (SELECT owner || '.' || table_name ownertab
            FROM   all_tables
            WHERE  owner IN ('XYZ'))
  LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.ownertab || ' TO abc_read_only';
  END LOOP;
END;
/