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