---------------------------------
---   DBAHero                 ---
---   Author: Leonardo Lopes  ---
---   Date: 09/12/2023        ---
---   v1                      ---
---------------------------------

DECLARE

LV_OBJECT_OWNER VARCHAR2(100); --- schema owner of the objects
LV_APPLY_TO VARCHAR2(100); --- schema or role which receives the grant

BEGIN
  
  LV_OBJECT_OWNER := 'REPLACE_ME'
  LV_APPLY_TO := 'REPLACE_ME';
  
  FOR O IN (
    SELECT OBJECT_NAME, OBJECT_TYPE 
    FROM ALL_OBJECTS 
    WHERE OWNER = LV_OBJECT_OWNER
    AND OBJECT_TYPE IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')
    ) 
    LOOP
      IF O.OBJECT_TYPE IN ('TABLE','VIEW') THEN
        EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON ' || LV_OBJECT_OWNER || '.' || O.OBJECT_NAME || ' TO ' || LV_APPLY_TO;
      ELSIF O.OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
        EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || LV_OBJECT_OWNER || '.' || O.OBJECT_NAME || ' TO ' || LV_APPLY_TO;
      END IF;
    END LOOP;

END;
/