0 Replies Latest reply on Nov 19, 2005 12:46 AM by 453869

    Menu Control Design Suggestions

    453869
      I am maintaining a client/server application written in Delphi 7 using an Oracle 91 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 ----
      1) All the users are assigned to one or more Oracle Roles.
      2) 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 ----
      3) 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.
      4) When the user logs into the application a list of their roles is retrieved in the delphi equivlant to a cursor.
      5) 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
      6) 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)
      7) This process (#5 & #6) is repeated for each role returned in #4 above. So if the user has 3 roles the code goes through this gyration 210 times.
      8) 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 thats a whole other problem hehe), 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.

      Thank you
      Richard Anderson