Procedure for Giving Grants for Objects Which are Missing Grants

--  Purpose: Loop through all tables in the schema(s) specified and check if the corresponding role has been granted select.

--  This Script Needs to run As SYS

--                 If the corresponding role is missing the select privilege this script will grant select to the role.

--      We need to Create Temp Table Which is having Two Columns Which is Schema name and                   Role Name .

--               

--           Schema - Role relationship is defined in a table named "schema_roles"

--    

--             CREATE TABLE SCHEMA_ROLES

--             (

--               "SCHEMA"  VARCHAR2(50 BYTE),

--               ROLE      VARCHAR2(50 BYTE)

--             )

--  

CREATE OR replace PROCEDURE Pr_fix_missing_grants

AS

  CURSOR C1 is select * from schema_roles;

  grant_rec C1%rowtype;

  

  TYPE REF_CURSOR IS REF CURSOR;

  C2                 REF_CURSOR;

  

  V_SQL              VARCHAR2(2000);

  V_SQL2             VARCHAR2(2000);

  V_OWNER            VARCHAR2(50);

  V_TABLE            VARCHAR2(50);

  

  V_TMP_CNT          NUMBER:=0;

  

begin

DBMS_OUTPUT.ENABLE(1000000); 

--Open cursor C1 to loop through list of schemas stored in schema_roles.

OPEN C1;

 LOOP

 FETCH C1 INTO grant_rec;

 EXIT WHEN C1%NOTFOUND;

 

   --Validate the user exists:

   select count(username) into V_TMP_CNT from dba_users where username = grant_rec.schema;

   if (V_TMP_CNT) > 0 then

   

     --Validate the role exists

     select count(role) into V_TMP_CNT from dba_roles where role = grant_rec.role;

     if (V_TMP_CNT) > 0 then

     

       --Build select statement to retrieve tables missing the SELECT privilege on the specified role.

       --dbms_output.put_line(grant_rec.schema ||' '||grant_rec.role);

       V_SQL := 'select owner, table_name from dba_tables where owner =''' || grant_rec.schema 

                 || ''' MINUS

                select owner, table_name from dba_tab_privs where privilege = ''SELECT'' and GRANTEE =''' || grant_rec.role || '''';

       --dbms_output.put_line(V_SQL);

       --Open cursor C2 to loop through tables that need to have select granted to the role.

       OPEN C2 FOR V_SQL;

       LOOP

         begin

         

           FETCH C2 into v_owner, v_table;

           EXIT WHEN C2%NOTFOUND; 

             V_SQL2 := 'grant select on '||V_OWNER ||'.'||V_TABLE||' to '||grant_rec.role;             

             --DBMS_OUTPUT.PUT_LINE(V_SQL2);

             execute immediate V_SQL2;

             DBMS_OUTPUT.PUT_LINE('Granted select on '||V_OWNER ||'.'||V_TABLE||' to '||grant_rec.role);

            

             --Exception handling in case something goes wrong making the grant.

exception 

             when others then

             DBMS_OUTPUT.PUT_LINE('ERROR PROCESSING ' || V_SQL2);

             dbms_output.put_line('!!!-ERROR-!!!' ||SQLERRM);

              

         end;

       END LOOP;

       CLOSE C2;

     ELSE     

       dbms_output.put_line('*****Role '||grant_rec.role||' does not exist!*****');

     END IF;     

   ELSE   

     dbms_output.put_line('*****Schema '||grant_rec.schema||' does not exist!*****');

   END IF;

 END LOOP;

END;

/