Script to grant privileges in oracle

oracle_grant_access.sql

-- executar como usuario OWNER

set serveroutput on

DECLARE

  v_owner VARCHAR(10) := TRIM(UPPER('GPOP'));
  v_comando VARCHAR2(1000);
  
  -- Cursor de DRL nas tabelas
  CURSOR cur_grants_drl_table IS
    SELECT 'GRANT SELECT ON ' || v_owner || '.' || table_name || ' TO R' || v_owner || '_DRL'
    FROM user_tables
    ORDER BY 1;
    
  -- Cursor de DRL nas sequences
  CURSOR cur_grants_drl_sequence IS
    SELECT 'GRANT SELECT ON ' || v_owner || '.' || sequence_name || ' TO R' || v_owner || '_DRL'
    FROM user_sequences
    ORDER BY 1;

  -- Cursor de DML nas tabelas
  CURSOR cur_grants_dml_table IS
    SELECT 'GRANT INSERT, UPDATE, DELETE ON ' || v_owner || '.' || table_name || ' TO R' || v_owner || '_DML'
    FROM user_tables
    ORDER BY 1;
  
BEGIN
  dbms_output.put_line('');
  dbms_output.put_line('Inicio do script...');
  
  /* GRANTS DRL NAS TABELAS */
  dbms_output.put_line('Inicio - DRL em tabelas');
  OPEN cur_grants_drl_table;
  
  LOOP
    FETCH cur_grants_drl_table INTO v_comando;
    EXIT WHEN cur_grants_drl_table%NOTFOUND;
    EXECUTE IMMEDIATE v_comando;
    dbms_output.put_line('Comando = ''' || v_comando || ''' executado com sucesso.');
    dbms_output.put_line('');
  END LOOP;
  
  CLOSE cur_grants_drl_table;
  dbms_output.put_line('Fim - DRL em tabelas.'); 
  dbms_output.put_line('');
  dbms_output.put_line('');
  
  
  
  /* GRANTS DML NAS TABELAS  */
  dbms_output.put_line('Inicio - DML em tabelas');
  OPEN cur_grants_dml_table;
  
  LOOP
    FETCH cur_grants_dml_table INTO v_comando;
    EXIT WHEN cur_grants_dml_table%NOTFOUND;
    EXECUTE IMMEDIATE v_comando;
    dbms_output.put_line('Comando = ''' || v_comando || ''' executado com sucesso.');
    dbms_output.put_line('');
  END LOOP;
  
  CLOSE cur_grants_dml_table;
  dbms_output.put_line('Fim - DML em tabelas');
  dbms_output.put_line('');
  dbms_output.put_line('');
  
  
  
  /* GRANTS DRL NAS SEQUENCES */
  dbms_output.put_line('Inicio - DRL em sequences');
  OPEN cur_grants_drl_sequence;
  
  LOOP
    FETCH cur_grants_drl_sequence INTO v_comando;
    EXIT WHEN cur_grants_drl_sequence%NOTFOUND;
    EXECUTE IMMEDIATE v_comando;
    dbms_output.put_line('Comando = ''' || v_comando || ''' executado com sucesso.');
    dbms_output.put_line('');
  END LOOP;
  
  CLOSE cur_grants_drl_sequence;
  dbms_output.put_line('Fim - DRL em sequences');
  dbms_output.put_line('');
  dbms_output.put_line('');
  
  dbms_output.put_line('Fim do script');
END;