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

Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

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')<br /><br />set serveroutput on size 999999<br /><p>exec check_role_privs&nbsp;</p>

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<br />Checking privileges for role AGS$ALG<br />---------------------------------------------<br />Checking privileges for role AGS$BHR<br />---------------------------------------------<br />For module AGS1100F role AGS$BHR needs extra access on VIEW AGS_V_ALL_USERS; apparently it lacks IUD<br />For module AGS1300F role AGS$BHR needs extra access on VIEW AGS_V_1300F_AUR; apparently it lacks IUD<br />Checking privileges for role AGS$BWGBHR<br />---------------------------------------------<br />For module AGS5400F role AGS$BWGBHR needs extra access on VIEW AGS_V_GEBRUIKERS; apparently it lacks U<br />Checking privileges for role AGS$BWGOND<br />---------------------------------------------<br />For module AGS5100F role AGS$BWGOND needs extra access on VIEW AGS_V_5108F_ELT; apparently it lacks Insert, Update, Delete<br />For module AGS5100F role AGS$BWGOND needs extra access on VIEW AGS_V_EENHEDEN; apparently it lacks IUD<br />For module AGS5100F role AGS$BWGOND needs extra access on VIEW AGS_V_GEBRUIKERS; apparently it lacks IUD<br />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<br />Role AGS$BWGOND needs extra access on VIEW AGS_V_GBR_MTG; apparently it lacks Delete<br />For module AGS5300F role AGS$BWGOND needs extra access on VIEW AGS_V_ALL_USERS; apparently it lacks IUD<br />For module AGS5300F role AGS$BWGOND needs extra access on VIEW AGS_V_GEBRUIKERS; apparently it lacks IUD<br />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<br />Role AGS$BWGOND needs extra access on VIEW AGS_V_GBR_MTG; apparently it lacks D<br /><p>Checking privileges for role AGS$BWGRPN<br />---------------------------------------------<br />Checking privileges for role AGS$BWGTTN<br />---------------------------------------------<br /></p><p>...&nbsp;</p>

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<br />   object grants for invoking the modules they have been granted access to. That means: every module<br />   that the role may invoke is verified; what tables and views are used by the module - and what is the <br />   CRUD for each usage - and which database object privileges are therefore required for the role<br />*/   <br />create or replace <br />procedure check_role_privs<br />is<br />  l_extra_privs varchar2(4);<br />  /* This function checks whether the indicated role p_rle_id has the proper access privileges<br />     on p_obj_id. These privileges are checked directly for the role and if necessary also<br />     through the role-network: all roles that are granted directly or indirectly to the role <br />     p_rle_id can provide additional access privileges.<br />  */     <br />  function role_has_object_access<br />  ( p_rle_id in number -- which role are we interested in<br />  , p_obj_id in number -- what object should the role has access on<br />  , p_select in boolean<br />  , p_insert in boolean<br />  , p_update in boolean<br />  , p_delete in boolean<br />  ) return varchar2<br />  is<br />    l_privs varchar2(4):='NNNN';<br />    l_result varchar2(4);<br />  begin<br />    -- added the NOCYCLE clause to prevent ORA-01436: CONNECT BY loop in user data error */<br />    -- use the MAX to find the all Y flags if they exist<br />    -- dbms_output.put_line('Checking DOG for role '||to_char(p_rle_id)||' on object '||to_char(p_obj_id));<br />    for privs in ( select max(nvl(dog.select_flag,'A')) select_flag<br />                   ,      max(nvl(dog.insert_flag,'A')) insert_flag<br />                   ,      max(nvl(dog.update_flag,'A')) update_flag<br />                   ,      max(nvl(dog.delete_flag,'A')) delete_flag<br />                   from   ci_database_object_grants  dog<br />                   ,      ci_db_object_implementations doi<br />                   ,      ( select rgt.grantor_role_reference rle_id<br />                            from   ci_role_grant rgt<br />                            connect <br />                            by     NOCYCLE prior rgt.grantor_role_reference = rgt.grantee_role_reference<br />
                  start <br
/>                            with   rgt.grantee_role_reference = p_rle_id<br />                            union<br />                            select p_rle_id rle_id<br />                            from   dual<br />                          ) rle<br />                   where  dog.ROLE_REFERENCE = rle.rle_id<br />                   and    nvl(dog.table_reference, dog.view_reference) =<br />                   and    nvl(doi.table_definition_reference, doi.view_definition_reference) = p_obj_id<br />                 ) loop<br />    l_privs:= privs.select_flag||privs.insert_flag||privs.update_flag||privs.delete_flag;<br />    --dbms_output.put_line('In privs '||l_privs);<br />   end loop; -- privs<br />   -- 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' <br />   if p_select and substr(l_privs,1,1) ='N' -- no select even though that is required<br />   then<br />     l_result:= l_result||'S';<br />   end if;<br />   if p_insert and substr(l_privs,2,1) ='N' -- no insert even though that is required<br />   then<br />     l_result:= l_result||'I';<br />   end if;<br />   if p_update and substr(l_privs,3,1) ='N' -- no update even though that is required<br />   then<br />     l_result:= l_result||'U';<br />   end if;<br />   if p_delete and substr(l_privs,4,1) ='N' -- no delete while that is required<br />   then<br />     l_result:= l_result||'D';<br />   end if;<br />    return l_result;<br />  end role_has_object_access;<br /><br />begin<br />  dbms_output.put_line('Checking Role Database Object Privileges against Module Access Grants and Module CRUDs');<br />  for rle in ( select   rle_id<br />               , rle_name<br />               , mde_name<br />               ,   mde_id<br />               ,      row_number() over (partition by order by rn<br />               from   ci_roles rle<br />               ,      CI_ROLE_MODULE_ACCESSES rms<br />               ,      ci_general_modules mde<br />               where  rms.role_reference =<br />               and    rms.general_module_reference =<br />               and &lt;&gt;'DEVELOPER'<br />               order<br />               by       <br />               ,<br />             ) loop<br />     if rle.rn = 1<br />     then<br />       dbms_output.put_line('Checking privileges for role '||rle.rle_name);<br />       dbms_output.put_line('---------------------------------------------');<br />     end if;<br />       <br />     -- OK, checking role rle.rle_id against module rle.mde_id<br />     -- let;s find out about the table and view usages of this module<br />     -- for each table or view usage, verify the CRUD<br />     for crud in ( select max(mco.insert_flag) insert_flag<br />                   ,      max(mco.update_flag) update_flag<br />                   ,      max(mco.delete_flag) delete_flag<br />                   ,      max(mco.select_flag) select_flag<br />                   ,      mti.table_reference<br />                   , table_name<br />                   ,      rel.table_type <br />                   from   ci_module_detail_table_usages mti<br />                   ,      ci_module_component_inclusions mcn<br />                   ,      ci_module_components mco<br />                   ,      ci_relation_definitions rel<br />                   where  mcn.general_module_reference = rle.mde_id<br />                   and    mcn.module_component_reference =<br />                   and    mti.module_unit_reference =<br />                   and = mti.table_reference<br />                   group<br />                   by     mti.table_reference<br />                   ,<br />                   ,      rel.table_type<br />                  ) loop<br />       l_extra_privs:= role_has_object_access<br />                       ( p_rle_id =&gt; rle.rle_id<br />                       , p_obj_id =&gt; crud.table_reference<br />                       , p_select =&gt; crud.select_flag ='Y'<br />                       , p_insert =&gt; crud.insert_flag ='Y'<br />                       , p_update =&gt; crud.update_flag ='Y'<br />                       , p_delete =&gt; crud.delete_flag ='Y'<br />                       );<br />       if length(l_extra_privs) &gt; 0<br />       then<br />         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);<br />       end if;                  <br />       -- 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<br />       -- 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)<br />       -- , the Form Generator will generate either a Select (when Delete Rule is restricted, internal value X )<br />       -- 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.<br />       -- So in these situations, the Role will need additional Select, Update or Delete privileges on the Child tables<br />       if crud.delete_flag = 'Y'<br />       then<br />         for fk in ( select fk_name<br />                     ,      fk.FK_CASCADE_DELETE delete_rule<br />                     ,      fk.implementation_level<br />                     ,      fk.table_reference<br />                     , table_name<br />                     ,      rel.table_type table_type<br />                     from   ci_foreign_key_constraints fk<br />                     ,      ci_relation_definitions rel<br />                     where  fk.foreign_table_reference = crud.table_reference<br />                     and    fk.implementation_level in ('CLIENT','BOTH')<br />                     and = fk.table_reference<br />                   ) loop<br />         l_extra_privs:= role_has_object_access<br />                         ( p_rle_id =&gt; rle.rle_id<br />                         , p_obj_id =&gt; fk.table_reference<br />                         , p_select =&gt; fk.delete_rule = 'X'<br />                         , p_insert =&gt; false<br />                         , p_update =&gt; fk.delete_rule in ( 'D','N')<br />                         , p_delete =&gt; fk.delete_rule = 'C'<br />                         );<br />         if length(l_extra_privs) &gt; 0<br />         then<br />           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||')'<br />           ||' that has a Delete Usage in module '||rle.mde_name);     <br />           dbms_output.put_line('Role '||rle.rle_name||' needs extra access on '||fk.table_type||' '||fk.table_name<br />           ||'; apparently it lacks '||l_extra_privs);<br />         end if;                  <br />                   <br />         end loop; -- fk                     <br />       end if; -- delete_flag='Y'<br />     end loop; -- crud<br />  end loop; -- rle<br />end check_role_privs;<br />/
Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PL/SQL), Service Oriented Architecture, BPM, ADF, JavaScript, Java in various shapes and forms and many other things. Author of the Oracle Press books: Oracle SOA Suite 11g Handbook and Oracle SOA Suite 12c Handbook. Frequent presenter on conferences such as JavaOne and Oracle OpenWorld. Presenter for Oracle University Celebrity specials.


  1. Hi Lucas,

    I indeed meant that the relevant user was removed as a Repository user (not a database user, yet). When this was done obviously all sorts of privileges were dropped at the same time. Next my colleague (really!) realised that the user account she just dropped was “important” and she reinstated the user as a Repository, to no avail.

    The reinstated user account is now available in the SDW_USERS table. When the ODWA is used to grant and revoke privileges, a difference can be detected in that some roles can grant more privileges than others. The reason for this is not clear.

    I am still trying to make a match between the RAU on the one side and ODWA on the other. Stupid or what? In the RAU I am still wondering why there is no way of modifying properties. When you position the cursor on any USER the properties button comes alive. Not with the ROLES. So where are you supposed to modify the ROLES’ properties?

    So seeking consolation in the ODWA for some reason (my colleague persists it is because this one user was deleted) certain roles just lack a number of options, like ‘Grant role’, ‘Edit role properties’ and ‘Reconcile role’. Where or how can you add these privileges to the relevant roles?

    Thanks, Lucas!


  2. Victor,

    I am not entirely sure what your situation is. When you say ‘deleted a user’do you mean removed that user from the group of Repository Users? You cannot delete (drop) the Repository owner and continue to use the Repository – as the entire repository owner’s database schema along with all Designer’s database objects would have vanished. So the owner must still exist as a database user.

    Is the situation such that the database user who owns the repository is not currently a user in the repository? Which sounds strange but could be the case. That would means that the SDW_USERS table does not have a record where USERNAME=.

    Please give me a little more information.


  3. Hello Lucas,

    At the moment I am struggling with a problem where a key user within the Repository environment (Designer deleted another user who apparently installed the Repository in the very beginning. In the environment there is no account called something like REPOS_OWNER or REPOS_MANAGER. It appears that the owner in this Repository is D6I_OWNER, but somehow this account does not have sufficient privileges to grant rights to roles.

    Do we need to run scripts to grant privileges to the D6I_OWNER in order to reinstate the original situation? The roles administration is done through the ODWA.

    Hope you can quote some keywords that I can use.


    Victor Bax

  4. Darn. I had not thought of that. You know what: you bring that functionality in the script and I will cheer you on. Deal??

    You are unfortunately right of course. Another ‘weak spot’ is the fact that the script does not check for modules that were granted through nested roles – so even if a role may look OK, it is possible that it has been granted modules through nested roles that require database object privileges that are not also granted through these nested roles. However, if you check all roles, this discrepancy will at least be reported.

  5. A very useful script (I work on the same project 🙂 ), but not perfect: what happens if someone has “All” rights on a object, but not specific “Select”, “Update”, “Insert” or “Delete” rigths?