I am maintaining a client/server application written in Delphi 7 using an Oracle 9i database (we are using Direct Oracle Access components to do the Delphi to Oracle connection) and I would like to get some menu control design suggestions. The current method is very convoluted and I will try to explain it as clearly as I can.
The easy stuff:
- All the users are assigned to one or more Oracle Roles.
- The menu items (main menus and sub-menus) in the Delphi application are restricted based on the user's Oracle Role(s) per customer requirments.
The convoluted stuff:
- The original designer created a table (MENU_ROLE) which looks like this:
SQL> desc menu_role Name Null? Type ----------------------------------------- -------- -------------------- ROLE_NAME NOT NULL VARCHAR2(30) MNPROJECTS VARCHAR2(1) MNPROJECTDATA VARCHAR2(1) MNMILESTONETRACKING VARCHAR2(1) MNASSOCIATIONS VARCHAR2(1) MNCONTRACTS VARCHAR2(1) MNCUSTOMERDATA VARCHAR2(1) MNCEILINGRESERVATION VARCHAR2(1) MNCEILINGDATA VARCHAR2(1) MNCEILINGREQUEST VARCHAR2(1) MNBASICCONTRACT VARCHAR2(1) MNEDITCONTRACT VARCHAR2(1) MNEDITCDRL1 VARCHAR2(1) MNEDITCONMOD VARCHAR2(1) MNCONTRACTAWARD VARCHAR2(1) MNCONTRACTCLOSEOUT VARCHAR2(1)This goes on the same for another 70 some odd menu items. The values in the MNxxx columns are either 'X' or null. The ROLE_NAME column is the Oracle Role.
- When the user logs into the application a list of roles is retrieved in the Delphi equivalent to a cursor.
- For each role row that is returned, a Delphi function is called passing in the role. Within this function the following query is run:
select * from MENU_ROLE where ROLE_NAME = passed in role
- This returns one row with 70+ columns and for each column the code sets the appropriate menu item to true if the value in the column is not null. So we have 70 lines of code like this:
MNxxx.Enabled = not FieldIsNull(MNxxxColumnName)
- This process is repeated for each role returned above. So if the user has three roles the code goes through this gyration 210 times.
Every time I add a menu item I need to add a column to the MENU_ROLE table, set the value of the column appropriately for each role (there are 25 possible roles -- I know -- too many but that's a whole other problem), and add the line to the Delphi code. If there are any menu changes or deletions I have to modify or remove the column and associated Delphi code.
Hopefully the above was not too confusing. Can anyone suggest a better design for limiting access to menus based on the user's Oracle Role when the front-end application is not written in Oracle?
Table: ROLE_MENU_ITEMS ROLE_NAME MENU_ITEM ---------- ----------- ROLE1 MENU1 ROLE1 MENU2 ROLE1 MENU3 ROLE2 MENU2 ROLE2 MENU4I can then store the user and their roles in another table as follows:
Table: APP_USER_ROLES USER_NAME ROLE_NAME ---------- ---------- MYUSER1 ROLE1 MYUSER1 ROLE2 MYUSER2 ROLE1To determine the menu items assigned to a user, the following query can be used:
SELECT DISTINCT menu_item FROM app_user_roles a, role_menu_items r WHERE a.role_name = r.role_name AND a.user_name = 'MYUSER1';You now have a list of menu items. Looping through the returned results can enable those menu items quickly and easily.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.