Procedure Aplicar Grants Read-Only (PL/SQL)

Olá, nesse artigo gostaria de compartilhar com vocês uma procedure em PL/SQL, para aplicação de grants (somente leitura) de um schema para outro schema ou role:

Considerações:

O script abaixo deve ser validado num ambiente não produtivo ¹⁾, antes de ser aplicado em produção.

1 - Segue abaixo a procedure PL/SQL:

prc_apply_grants_ro.sql

---------------------------------
---   DBAHero                 ---
---   Author: Leonardo Lopes  ---
---   Date: 09/12/2023        ---
---   v1                      ---
---------------------------------
 
CREATE OR REPLACE PROCEDURE APPLY_GRANTS_RO(PV_OWNER_OBJECT VARCHAR2, PV_APPLY_TO VARCHAR2)
 
IS
--- PV_OWNER_OBJECT schema owner of the objects
--- PV_APPLY_TO schema or role which receives the grant
 
CURSOR C_OBJECTS
is
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE 
FROM ALL_OBJECTS
WHERE OWNER = PV_OWNER_OBJECT
AND OBJECT_TYPE IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE');
 
BEGIN
 
  FOR O IN C_OBJECTS LOOP
    IF O.OBJECT_TYPE IN ('TABLE','VIEW') THEN
      EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON ' || PV_OWNER_OBJECT || '.' || O.OBJECT_NAME || ' TO ' || PV_APPLY_TO;
    ELSIF O.OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
      EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || PV_OWNER_OBJECT || '.' || O.OBJECT_NAME || ' TO ' || PV_APPLY_TO;
    END IF;
  END LOOP;
 
END;
/

• PV_OBJECT_OWNER ⇒ O valor dessa variável deve conter o nome do schema owner ²⁾ dos objetos.

• PV_APPLY_TO ⇒ O valor dessa variável deve conter o nome do schema ou role que receberá os grants ³⁾ nos objetos.

Exemplo de como executar a procedure:

EXECUTE APPLY_GRANTS_RO('DONO_DO_OBJETO','USER_QUE_RECEBE_ACESSO');

EXECUTE APPLY_GRANTS_RO('DONO_DO_OBJETO','ROLE_QUE_RECEBE_ACESSO');

- Até a próxima.

Procedure Aplicar Grants Read-Only (PL/SQL)