Grant Select su tutte le tabelle di uno schema

Accedere come SYS e creare un user di esempio.

create user tizio
identified by tizio

default tablespace USERS

temporary tablespace TEMP

PROFILE DEFAULT

account unlock;

grant connect to tizio;

Di seguito la procedura

--script_grant_to_all_objects.sql

set serveroutput on
spool log_script_grant.log

prompt ============================
prompt Start script
prompt ============================

BEGIN
  FOR cur IN (
            
   select   owner, object_name
            FROM   all_objects
            where  object_type in ('VIEW', 'TABLE','MATERIALIZED VIEW')
            and owner not in (
            select username  from dba_users where default_tablespace  IN ('SYSAUX', 'TOOLS','TEMP','SYSTEM','CARTPLSQL','DRSYS','XDB'))
             )
  LOOP
      DBMS_OUTPUT.put_line ('GRANT SELECT ON ' || cur.OWNER || '.' || cur.OBJECT_NAME || ' TO tizio');
     EXECUTE IMMEDIATE 'GRANT SELECT ON ' || cur.OWNER || '.' || cur.OBJECT_NAME ||' TO tizio';
  END LOOP;
END;
/

prompt =================================
prompt End script
prompt =================================
spool off