Saturday, 25 April 2015

Give Grants For Objects Which are Missing Grants on Objects & Which are Created Last 24 hrs Create

CREATE OR replace PROCEDURE snanabala.Pr_fix_missing_grants
AS
  CURSOR cu_missing_grants_identity IS
    SELECT 'grant select on '
           || owner
           ||'.'
           || object_name
           || ' to IDENTITY_READ_ROLE' AS script
    FROM   dba_objects
    WHERE  object_type IN ( 'TABLE', 'VIEW', 'SEQUENCE' )
           AND owner IN ( 'IDENTITY' )
           AND Trunc(created) = Trunc(SYSDATE);
  CURSOR cu_missing_grants_siw IS
    SELECT 'grant select on '
           || owner
           ||'.'
           || object_name
           || ' to SIW_READ_ROLE' AS script
    FROM   dba_objects
    WHERE  object_type IN ( 'TABLE', 'VIEW', 'SEQUENCE' )
           AND owner IN ( 'SIW' )
           AND Trunc(created) = Trunc(SYSDATE);
  CURSOR cu_missing_grants_edw IS
    SELECT 'grant select on '
           || owner
           ||'.'
           || object_name
           || ' to EDW_READ_ROLE' AS script
    FROM   dba_objects
    WHERE  object_type IN ( 'TABLE', 'VIEW', 'SEQUENCE' )
           AND owner IN ( 'EDW_READ_ROLE' )
           AND Trunc(created) = Trunc(SYSDATE);
BEGIN
    FOR l_missing_gr_identity IN cu_missing_grants_identity LOOP
        dbms_output.Put_line (l_missing_gr_identity.script);
   EXECUTE IMMEDIATE l_missing_gr_identity.script;
    END LOOP;

    FOR l_missing_gr_siw IN cu_missing_grants_siw LOOP
        dbms_output.Put_line (l_missing_gr_siw.script);
    EXECUTE IMMEDIATE l_missing_gr_siw.script;
    END LOOP;

    FOR l_missing_gr_edw IN cu_missing_grants_edw LOOP
        dbms_output.Put_line (l_missing_gr_edw.script);
    EXECUTE IMMEDIATE l_missing_gr_edw.script;
    END LOOP;
END;
/

No comments:

Post a Comment