Oracle Designer – Script for verifying the privileges assigned to roles against the Module Table Usages

In my current project – with Oracle Designer 10g, WebForms, Headstart and CDM RuleFrame, Repository Object Browser, CVS, Oracle SCM and Jira for Incident Management as well as TransferWare for transferring objects from Development to Test and beyond – we are constantly working on improving the software engineering process. Part of this ongoing challenge is ensuring that (database) users are granted the required roles and – even more delicate – ensuring that the roles have been granted all the required database object privileges.

We have recorded all Modules and all Roles in Oracle Designer. We have granted access on modules to roles through Module Role Access Grants in Oracle Designer. We try to keep carefully track of all databaseobject privileges – grants on Tables and Views – to make sure that when a user can start a WebForm module from the Menu – because of the grant of that Module to a role owned by the user – he or she will actually have all necessary select, insert, update and delete privileges for that Form.

Since all information about the table and view usages by the modules is recorded in Oracle Designer itself, it should be possible to find out whether we have already created all Database Object Grants. I have taken some time and developed a PL/SQL procedure that we – and you – can use to find out whether the set of database object privileges available to a role – either directly or through nested roles – is sufficient for the modules that the Role has access to.

The procedure can be created under the Repository Owner’s Schema – that is easiest. When it is created, it can be used in the following way:

exec jr_context.set_workarea('NAME_WORKAREA')

set serveroutput on size 999999
exec check_role_privs 

The result of running the procedure in my current project looks like this:

Checking Role Database Object Privileges against Module Access Grants and Module CRUDs
Checking privileges for role AGS$ALG
---------------------------------------------
Checking privileges for role AGS$BHR
---------------------------------------------
For module AGS1100F role AGS$BHR needs extra access on VIEW AGS_V_ALL_USERS; apparently it lacks IUD
For module AGS1300F role AGS$BHR needs extra access on VIEW AGS_V_1300F_AUR; apparently it lacks IUD
Checking privileges for role AGS$BWGBHR
---------------------------------------------
For module AGS5400F role AGS$BWGBHR needs extra access on VIEW AGS_V_GEBRUIKERS; apparently it lacks U
Checking privileges for role AGS$BWGOND
---------------------------------------------
For module AGS5100F role AGS$BWGOND needs extra access on VIEW AGS_V_5108F_ELT; apparently it lacks Insert, Update, Delete
For module AGS5100F role AGS$BWGOND needs extra access on VIEW AGS_V_EENHEDEN; apparently it lacks IUD
For module AGS5100F role AGS$BWGOND needs extra access on VIEW AGS_V_GEBRUIKERS; apparently it lacks IUD
Because of the foreign key AGS_VGMG_VGBR_FK from AGS_V_GBR_MTG to AGS_V_GEBRUIKERS (Validate in CLIENT, Delete Rule C) that has a Delete Usage in module AGS5100F
Role AGS$BWGOND needs extra access on VIEW AGS_V_GBR_MTG; apparently it lacks Delete
For module AGS5300F role AGS$BWGOND needs extra access on VIEW AGS_V_ALL_USERS; apparently it lacks IUD
For module AGS5300F role AGS$BWGOND needs extra access on VIEW AGS_V_GEBRUIKERS; apparently it lacks IUD
Because of the foreign key AGS_VGMG_VGBR_FK from AGS_V_GBR_MTG to AGS_V_GEBRUIKERS (Validate in CLIENT, Delete Rule C) that has a Delete Usage in module AGS5300F
Role AGS$BWGOND needs extra access on VIEW AGS_V_GBR_MTG; apparently it lacks D
Checking privileges for role AGS$BWGRPN
---------------------------------------------
Checking privileges for role AGS$BWGTTN
---------------------------------------------
... 

The trigger for me to create this script by the way was the problems we ran into when we added a table that had foreign keys to some of the tables that already existed. When we re-generated one of the forms that used one of the tables that already existed, suddenly we could not compile the form anymore. It turned out that the new foreign key from the new table had Validate In set to Client (or Both) and the Delete Rule was set to Cascade. The result was that the Oracle Form Generator generated a delete statement for the new (child)table in the form, even though we did not actually make any changes in the module definition. To prevent this problem from occurring, the procedure check_role_privs take careful note of all foreign keys that reference the tables that are used in the modules that are granted to the roles…. 

Next Steps

We can further improve this procedure in following ways:

• Allow a specific role to be analyzed
• Allow a specifc module to be analyzed
• Allow all modules and/or roles in a specific folder (and all its subfolders) to be analyzed
• Have the script automatically fix all lacking privileges
• Wrap the procedure in a Table Function
• Adapt the script for non-Oracle 10g: currently it includes a NOCYCLE clause in the hierarchical query that traverses nested loops. This clause is only available with Oracle 10g. In pre-10g databases, we would have to replace this code with a non-hierarchical approach; just select the nested roles and after processing these and still lacking privileges, process each of the nested roles in turn – until all roles have been processed already or all required privileges are found

The script for the procedure Check_Role_Privs 

/* this procedure checks whether all roles in the current workarea have all the necessary database
   object grants for invoking the modules they have been granted access to. That means: every module
   that the role may invoke is verified; what tables and views are used by the module - and what is the 
   CRUD for each usage - and which database object privileges are therefore required for the role
*/   
create or replace 
procedure check_role_privs
is
  l_extra_privs varchar2(4);
  /* This function checks whether the indicated role p_rle_id has the proper access privileges
     on p_obj_id. These privileges are checked directly for the role and if necessary also
     through the role-network: all roles that are granted directly or indirectly to the role 
     p_rle_id can provide additional access privileges.
  */     
  function role_has_object_access
  ( p_rle_id in number -- which role are we interested in
  , p_obj_id in number -- what object should the role has access on
  , p_select in boolean
  , p_insert in boolean
  , p_update in boolean
  , p_delete in boolean
  ) return varchar2
  is
    l_privs varchar2(4):='NNNN';
    l_result varchar2(4);
  begin
    -- added the NOCYCLE clause to prevent ORA-01436: CONNECT BY loop in user data error */
    -- use the MAX to find the all Y flags if they exist
    -- dbms_output.put_line('Checking DOG for role '||to_char(p_rle_id)||' on object '||to_char(p_obj_id));
    for privs in ( select max(nvl(dog.select_flag,'A')) select_flag
                   ,      max(nvl(dog.insert_flag,'A')) insert_flag
                   ,      max(nvl(dog.update_flag,'A')) update_flag
                   ,      max(nvl(dog.delete_flag,'A')) delete_flag
                   from   ci_database_object_grants  dog
                   ,      ci_db_object_implementations doi
                   ,      ( select rgt.grantor_role_reference rle_id
                            from   ci_role_grant rgt
                            connect 
                            by     NOCYCLE prior rgt.grantor_role_reference = rgt.grantee_role_reference

                  start 
                            with   rgt.grantee_role_reference = p_rle_id
                            union
                            select p_rle_id rle_id
                            from   dual
                          ) rle
                   where  dog.ROLE_REFERENCE = rle.rle_id
                   and    nvl(dog.table_reference, dog.view_reference) = doi.id
                   and    nvl(doi.table_definition_reference, doi.view_definition_reference) = p_obj_id
                 ) loop
    l_privs:= privs.select_flag||privs.insert_flag||privs.update_flag||privs.delete_flag;
    --dbms_output.put_line('In privs '||l_privs);
   end loop; -- privs
   -- if there are no object privileges on p_obj_id granted to p_rle_id or one of its nested roles, then l_privs is still equal to 'NNNN' 
   if p_select and substr(l_privs,1,1) ='N' -- no select even though that is required
   then
     l_result:= l_result||'S';
   end if;
   if p_insert and substr(l_privs,2,1) ='N' -- no insert even though that is required
   then
     l_result:= l_result||'I';
   end if;
   if p_update and substr(l_privs,3,1) ='N' -- no update even though that is required
   then
     l_result:= l_result||'U';
   end if;
   if p_delete and substr(l_privs,4,1) ='N' -- no delete while that is required
   then
     l_result:= l_result||'D';
   end if;
    return l_result;
  end role_has_object_access;

begin
  dbms_output.put_line('Checking Role Database Object Privileges against Module Access Grants and Module CRUDs');
  for rle in ( select rle.id   rle_id
               ,      rle.name rle_name
               ,      mde.name mde_name
               ,      mde.id   mde_id
               ,      row_number() over (partition by rle.id order by mde.name) rn
               from   ci_roles rle
               ,      CI_ROLE_MODULE_ACCESSES rms
               ,      ci_general_modules mde
               where  rms.role_reference = rle.id
               and    rms.general_module_reference = mde.id
               and rle.name <>'DEVELOPER'
               order
               by     rle.name       
               ,      mde.name
             ) loop
     if rle.rn = 1
     then
       dbms_output.put_line('Checking privileges for role '||rle.rle_name);
       dbms_output.put_line('---------------------------------------------');
     end if;
       
     -- OK, checking role rle.rle_id against module rle.mde_id
     -- let;s find out about the table and view usages of this module
     -- for each table or view usage, verify the CRUD
     for crud in ( select max(mco.insert_flag) insert_flag
                   ,      max(mco.update_flag) update_flag
                   ,      max(mco.delete_flag) delete_flag
                   ,      max(mco.select_flag) select_flag
                   ,      mti.table_reference
                   ,      rel.name table_name
                   ,      rel.table_type 
                   from   ci_module_detail_table_usages mti
                   ,      ci_module_component_inclusions mcn
                   ,      ci_module_components mco
                   ,      ci_relation_definitions rel
                   where  mcn.general_module_reference = rle.mde_id
                   and    mcn.module_component_reference = mco.id
                   and    mti.module_unit_reference = mco.id
                   and    rel.id = mti.table_reference
                   group
                   by     mti.table_reference
                   ,      rel.name
                   ,      rel.table_type
                  ) loop
       l_extra_privs:= role_has_object_access
                       ( p_rle_id => rle.rle_id
                       , p_obj_id => crud.table_reference
                       , p_select => crud.select_flag ='Y'
                       , p_insert => crud.insert_flag ='Y'
                       , p_update => crud.update_flag ='Y'
                       , p_delete => crud.delete_flag ='Y'
                       );
       if length(l_extra_privs) > 0
       then
         dbms_output.put_line('For module '||rle.mde_name||' role '||rle.rle_name||' needs extra access on '||crud.table_type||' '||crud.table_name||'; apparently it lacks '||l_extra_privs);
       end if;                  
       -- now when there is a Delete Usage, we need to look further: if there are Foreign Keys defined that reference the Table on which there is a delete usage
       -- and the Validate In (internally called IMPLEMENTATION_LEVEL) property of that Foreign Key is set to Client (internal value CLIENT) or Both (internal value BOTH)
       -- , the Form Generator will generate either a Select (when Delete Rule is restricted, internal value X )
       -- or a Delete (when Delete Rule is cascade, internal value C). For Default of Nullified (values D and N) there will be an update usage on the referencing (child) table.
       -- So in these situations, the Role will need additional Select, Update or Delete privileges on the Child tables
       if crud.delete_flag = 'Y'
       then
         for fk in ( select fk.name fk_name
                     ,      fk.FK_CASCADE_DELETE delete_rule
                     ,      fk.implementation_level
                     ,      fk.table_reference
                     ,      rel.name table_name
                     ,      rel.table_type table_type
                     from   ci_foreign_key_constraints fk
                     ,      ci_relation_definitions rel
                     where  fk.foreign_table_reference = crud.table_reference
                     and    fk.implementation_level in ('CLIENT','BOTH')
                     and    rel.id = fk.table_reference
                   ) loop
         l_extra_privs:= role_has_object_access
                         ( p_rle_id => rle.rle_id
                         , p_obj_id => fk.table_reference
                         , p_select => fk.delete_rule = 'X'
                         , p_insert => false
                         , p_update => fk.delete_rule in ( 'D','N')
                         , p_delete => fk.delete_rule = 'C'
                         );
         if length(l_extra_privs) > 0
         then
           dbms_output.put_line('Because of the foreign key '||fk.fk_name||' from '||fk.table_name||' to '||crud.table_name||' (Validate in '||fk.implementation_level||', Delete Rule '||fk.delete_rule||')'
           ||' that has a Delete Usage in module '||rle.mde_name);     
           dbms_output.put_line('Role '||rle.rle_name||' needs extra access on '||fk.table_type||' '||fk.table_name
           ||'; apparently it lacks '||l_extra_privs);
         end if;                  
                   
         end loop; -- fk                     
       end if; -- delete_flag='Y'
     end loop; -- crud
  end loop; -- rle
end check_role_privs;
/