---------------------------------
---   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;
/