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')

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
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
l_privs varchar2(4):='NNNN';
l_result varchar2(4);
-- 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
by NOCYCLE prior rgt.grantor_role_reference = rgt.grantee_role_reference
with rgt.grantee_role_reference = p_rle_id
select p_rle_id rle_id
from dual
) rle
where dog.ROLE_REFERENCE = rle.rle_id
and nvl(dog.table_reference, dog.view_reference) =
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
l_result:= l_result||'S';
end if;
if p_insert and substr(l_privs,2,1) ='N' -- no insert even though that is required
l_result:= l_result||'I';
end if;
if p_update and substr(l_privs,3,1) ='N' -- no update even though that is required
l_result:= l_result||'U';
end if;
if p_delete and substr(l_privs,4,1) ='N' -- no delete while that is required
l_result:= l_result||'D';
end if;
return l_result;
end role_has_object_access;

dbms_output.put_line('Checking Role Database Object Privileges against Module Access Grants and Module CRUDs');
for rle in ( select rle_id
, rle_name
, mde_name
, mde_id
, row_number() over (partition by order by rn
from ci_roles rle
, ci_general_modules mde
where rms.role_reference =
and rms.general_module_reference =
) loop
if rle.rn = 1
dbms_output.put_line('Checking privileges for role '||rle.rle_name);
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
, 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 =
and mti.module_unit_reference =
and = mti.table_reference
by mti.table_reference
, 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
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'
for fk in ( select fk_name
, fk.FK_CASCADE_DELETE delete_rule
, fk.implementation_level
, fk.table_reference
, 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 = 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
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;
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?