set serveroutput on size 1000000

ACCEPT user_value CHAR PROMPT 'Ingrese usuario a crear con solo roles de lectura : '
ACCEPT dbms_value CHAR PROMPT 'Desea visualizar o crear (V o C)? : '

declare
cursor c1 is
select username
  from dba_users
 where username not in ('SYS','PERFSTAT','QUEST','SQLTECH','MONITOR_FABAN','DBABANCO','SYSTEM','OUTLN','DBSNMP','WMSYS','OWF_MGR','RMAN','MONITORATS','ORACLE',
                        'MONITOR','&&user_value')
    ;

cursor c2(var_usuario in varchar2) is
  select owner ,
         object_name
   from dba_objects
  where owner       = var_usuario
    and object_type = 'TABLE'
     ;

 var_dbms varchar2(100);

 begin
  
   var_dbms := '&&dbms_value';    

   if var_dbms = 'C' or var_dbms = 'c' then
      execute immediate 'create user &&user_value identified by &&user_value password expire';
      execute immediate 'grant create session to &&user_value';
   elsif var_dbms = 'V' or var_dbms = 'v' then
      dbms_output.put_line('create user &&user_value identified by &&user_value password expire'||';');
      dbms_output.put_line('grant create session to &&user_value'||';');
   end if ; 

   for c1_aux in c1 loop
       if var_dbms = 'C' or var_dbms = 'c' then
          execute immediate 'create role ROLSEL_'||c1_aux.username;
          execute immediate 'grant ROLSEL_'||c1_aux.username||' to &&user_value';
       elsif var_dbms = 'V' or var_dbms = 'v' then 
          dbms_output.put_line('create role ROLSEL_'||c1_aux.username||';');
          dbms_output.put_line('grant ROLSEL_'||c1_aux.username||' to &&user_value'||';');
       end if;

       for c2_aux in c2(c1_aux.username) loop
           if var_dbms = 'C' or var_dbms = 'c' then
              execute immediate 'grant select on '||c2_aux.owner||'.'||c2_aux.object_name||' to ROLSEL_'||c1_aux.username;
           elsif var_dbms = 'V' or var_dbms = 'v' then 
              dbms_output.put_line('grant select on '||c2_aux.owner||'.'||c2_aux.object_name||' to ROLSEL_'||c1_aux.username||';');        
           end if;
       end loop;
   end loop;
 end;
/