Q
Problem solve Get help with specific problems with your technologies, process and projects.

Design help for Oracle Roles when front-end application is not Oracle

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. 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?

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?

The best suggestion I have for you is to use two columns in your table, rather than 70. Let me give you a quick example. Let's assume I have two roles (ROLE1 and ROLE2) and these two roles were granted to MYUSER. Now each role is assigned various menu items as denoted in this table:
Table: ROLE_MENU_ITEMS
ROLE_NAME   MENU_ITEM
----------  -----------
ROLE1       MENU1
ROLE1       MENU2
ROLE1       MENU3
ROLE2       MENU2
ROLE2       MENU4
I 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     ROLE1
To 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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close