0723-0725

Previous Table of Contents Next

Page 723

The application itself communicates with application-specific objects in the database through public synonyms. Stored procedures and functions operating on views are used exclusively for transaction processing, and views are used to retrieve all result sets. This method of communicating with the database provides the highest degree of abstraction and security and simplifies the process of developing the client application.

Table-Driven Application Security

You can use information stored in Oracle tables to drive application security. This solution is particularly useful when security restrictions are likely to change and flexibility is required. As mentioned in the previous section, you can use a table in Oracle to determine the default application role of the current user. The role can then be used as the basis for determining which menu options, forms, and controls are available to the user .

First, you must construct a table to store the APPLICATION role of each user. This can be a simple two-column table made up of the user ID and APPLICATION role. Each user should have only one role. If a particular user should have access to more than one role, you should create a new role and grant the privileges of the required roles. For example, if a user should have the privileges of the roles oe_user, oe_management, and oe_admin, a fourth role should be created, as follows :

 CREATE ROLE oe_superuser; GRANT oe_user, oe_management, oe_admin TO oe_superuser; GRANT CONNECT, oe_superuser TO scotty IDENTIFIED BY tiger; 

The user's role should be determined by the application immediately after the connection is established so that it can be used to enable, disable, or hide menu options as needed. The code that will alter the main window's menu should be placed in the window's constructor so that all changes are made before the window is instantiated . This will prevent the hidden options from being momentarily visible, before the menu is repainted. One possible table definition for controlling menu behavior appears in Listing 29.2.

Listing 29.2. One possible way to store application security information related to menus .

 CREATE TABLE oe_menu_privileges (      app_role       VARCHAR2(20)     ,menu_item_id   VARCHAR2(10)     ,visible        NUMBER(1)     NOT NULL     ,enabled        NUMBER(1)     NOT NULL     ,CONSTRAINT menu_priv_pk PRIMARY KEY (app_role, menu_item_id) ); 

Defining the menu item identifier as a numeric value might be preferable to a character datatype, depending on the tool you used to develop the client application. Many popular Windows development tools provide the Tag property as the only possible way to identify a

Page 724

particular control at runtime (besides the actual text of the menu item). This property is typically a string datatype and should be stored as such in the database. Be careful to prevent trailing spaces from being stored in this column. Trailing spaces are easily overlooked, and might cause comparison problems.

The visible and enabled columns in Listing 29.2 should contain the numeric representations of the Boolean values TRUE and FALSE so that they can be used to set the corresponding properties directly. For example, a Delphi application might use a method like the following one to directly enable or disable a menu option from a TTable object:

 mnuAdmin.Enabled := tblMenuSecurity.FieldByName("ENABLED").AsBoolean; 
 

One difficulty in dynamically altering menu options based on table information is in determining which menu option is referenced in the table. Depending on the tool being used, a menu item can be identified by an integer ID or by a string value assigned to the Tag property. Regardless of the means by which a menu option is identified, the application must be able to iterate through the menu options to find a match for a menu ID read from the database. In some cases, the only available means of accomplishing this is to provide a switch statement with a separate case for each possible menu item identifier. Consider this when you design menu security. If only a few items will be disabled or hidden for any given role, the number of items that must be checked against values read from the database will be minimized. This, in turn , will make the code required to accomplish these tasks smaller and easier to maintain. Listing 29.3 presents a sample implementation of these concepts in Visual Basic.

Listing 29.3. This Visual Basic subroutine uses a control array to alter a form's menu at runtime.

 Sub SetMenuOptions(dsMenuOptions() As Dynaset)     Dim i As Integer     While Not dsMenuOptions(0).EOF         For i = 0 To MAX_MENU_OPTIONS             If (mnuTop(i).Tag =                 dsMenuOptions(0).Fields("MENU_OPTION")) Then                 mnuTop(i).Enabled =                      dsMenuOptions(0).Fields("ENABLED")                 mnuTop(i).Visible =                      dsMenuOptions(0).Fields("VISIBLE")                 Exit For             End If         Next i         dsMenuOptions(0).MoveNext     Wend End Sub 

Page 725

Note that Visual Basic's implementation of the control array provides a generic way to match a menu's identifier with values read from the database. This approach has its limitations, however. A menu control array can contain options only at the same level. Also, when controls are part of an array in Visual Basic, they share the same event code. Each event receives the index of the array to which the event currently applies as a parameter. This requires additional logic in event handlers for control arrays.

Many development tools do not provide control arrays as an option, so the code to match a menu item with a database value becomes more application-specific. The problem inherent to this method of using tables to control menu options is that the hard-coded menu identifiers must exactly match the values stored for them in the database. A change to either the identifier within the client application or the value of the identifier in the table will cause this means of enforcing application security to fail. In most cases, if a menu option can possibly be disabled by the application security mechanism, it should be disabled by default. This is based on the assumption that if there are problems in properly matching values from the database, erring on the side of increased security is usually better.

Maintaining application security for menu options can be simplified by the design of the menus. Options that can potentially be disabled or hidden should be top-level menu items, and where groups of options can be disabled, they should be grouped together under the same top-level menu item wherever possible. Limiting the number of items that will need to be stored in the database and checked at runtime will improve performance and limit the possible points of failure.

Using the previously described order-entry subsystem as an example, assume that only users with the role oe_admin will have access to update and insert records into lookup tables and to add new users to the system. These two operations can be logically grouped into a top-level menu category, Admin. Using this design, the application need only set the state for the top-level menu item. The Admin menu option should probably be made invisible (rather than disabled) for users who do not have access to it, because the options it contains will not be available to those users under any circumstances. Figures 29.2 and 29.3 show examples of what the main application window might look like to oe_admin and non-oe_admin users, respectively.

In some cases, it might be necessary to enable access to a subset of options in a drop-down menu. For example, a second role, oe_manager, might have privileges to add a new user, but not to modify lookup tables. For this user, the application's main menu can appear as in Figure 29.4.

Whether you make menu options invisible or disabled is a matter of design preference. In most cases, it makes more sense to completely hide an option that is unavailable to the current user. Simply disabling a menu option implies that there are circumstances under which it will be enabled. However, when the menu option is part of a drop-down, making it invisible can leave only a single option, which is inconsistent with the standard uses of drop-down menus. Regardless of the way you enforce application security for menus, you should apply it consistently throughout the application.

Previous Table of Contents Next