0730-0732

Previous Table of Contents Next

Page 730

Listing 29.5. continued

 While Not dsMenuOptions(0).EOF         iControlID = dsControlSecurity(0).Fields("control_id")         bVal = dsControlSecurity(0).Fields("read_only")         Select Case iControlID             Case Val(txtPrice.Tag)                 If (bVal = False) Then                     lblPrice.ForeColor = COLOR_BLACK                     iRet = SendMessage(txtPrice.hWnd,                         EM_SETREADONLY, False, NILL)                 End If             Case Val(txtDateShipped.Tag)                 If (bVal = False) Then                     lblDateShipped.ForeColor = COLOR_BLACK                     iRet = SendMessage(txtDateShipped.hWnd,                         EM_SETREADONLY, False, NILL)                 End If         dsControlSecurity(0).MoveNext     Wend End Sub 

In the example, security restrictions apply to only two fields; therefore, only two fields are checked. Note that Listing 29.5 assumes that the fields were set to read-only by default in the constructor for the window. Where security restrictions apply, the default behavior of windows and controls should be to assume that the current user does not have privileges, so that if there is any problem with the data (other than valid, but inaccurate values), the application will err on the side of increased, rather than reduced, security.

The example in Listing 29.5 points out one of the powerful uses of application security, as opposed to database security. The mechanism by which the default price is changed would be very difficult to implement in the database. For example, if the table that stores order details defines the ID of the item ordered as a foreign key to a product table, the price can never be changed. On the other hand, if price is simply defined as a column in the table that stores order details, there is no way to enforce the default prices. In cases such as these, using application security is the only way to enforce the rule. Separate procedures or functions exist to perform inserts and updates for each role; however, in that case, the client application would still be enforcing security by determining which procedure to call based on the role of the user. This is just one example of how you can use application security to enforce rules that would be difficult, if not impossible , to enforce through database security alone.

Another way in which application security is used is to filter result sets being returned from the database. For example, an order-entry system might enable salespersons to view and edit only their own orders. Filtering is best accomplished through the use of views. A view to create a list of a specific salesperson's accounts can be as simple as the following statement:

 CREATE OR REPLACE VIEW saleperson_orders AS      SELECT * FROM orders WHERE salesperson = user; 

Page 731

In most cases, the same filter will not be applied to all users. Create a separate view to apply different filters. Managers using the sample order-entry system might use a view that applies no filter, whereas the shipping department might access the system through a view similar to the following one:

 CREATE OR REPLACE VIEW shipping_orders AS      SELECT * FROM orders WHERE status = `OPEN'; 

The application will need to determine which view to use for each particular role. As with other means of enforcing application security, this process can be table-driven. In order to use table-driven filtering, the application must have some way to assign a unique identifier to each result set for which a filter is to be applied. Again, potential problems exist in tying this information to the database. Duplicate IDs, or mismatches between IDs used in the application and IDs stored in the database, will result in database errors or incorrect result sets being returned.

Once unique identifiers have been assigned to the result sets, you can use a simple table consisting of a role name, a result-set identifier, and a view name to apply a filter to a particular application role. To implement filtering based on information in the database, an application must retrieve the data used to apply the filter before retrieving the result set. Using views as the filter mechanism keeps the size of the security table to a minimum, which will help limit the negative impact on performance caused by the additional read required. Using views also simplifies the process of building dynamic SQL in the application. You can construct the views in such a way that no additional information is required by the client application. In this case, the only thing dynamic about the SQL is the name of the view. The SQL in the client application would look like the following:

 SELECT * FROM view_name 

In the preceding SQL statement, the view_name is the value read from the database for the particular role and result-set identifier. Simply concatenating two strings is an easy task in any programming language. Using views to apply filters makes this task of application security the easiest to implement.

Application and Performance Considerations

If application security is not implemented properly, the result will be a less intuitive interface that requires more error handling. For example, if users who do not have the INSERT privilege for a particular table are allowed to enter data into a form that inserts values into the table, the users will not know they do not have access until they try to save. At that point, the application must deal with the resulting database error and display some message to the user. Users will be frustrated by these efforts, and in many cases will interpret the resulting message as a bug. In this respect, application security should be used to hide the fact that database security exists. Generally , users should not see menu options, forms, and controls that they can never use. Hiding inaccessible menu options and controls results in a less cluttered and more intuitive interface.

Page 732

However, as the previous section illustrates, the task of enforcing application security can be somewhat complicated, particularly when you use the database to control it. If the rules governing application security are relatively static, it is usually preferable to enforce them without using the database. Although this type of hard-coding is generally viewed as unsavory, it can be implemented in a way that is much cleaner than using the database to drive application security. The methods for altering menus and controls can be completely encapsulated in the windows to which they apply. The application need only retrieve the role of the current user from the database. Using values stored in the database to enforce application security, in some respects, amounts to an even less acceptable means of hard-coding. The identifiers used for menus , forms, and controls in the application must exactly match the identifiers being stored in the database, which introduces otherwise unnecessary dependencies. Also, the additional database reads required by this method will have some negative impact on performance. The degree to which performance is affected depends on the network and hardware environments and the size of the records and tables being used to drive application security.

In addition to the likelihood of changes in security restrictions, consider the number of users and their locations when you determine how to enforce application security. In general, unless security will be changed frequently and there are many users at remote sites, the improved performance and encapsulation of client-side enforcement will outweigh the benefits of table-driven application security. Even if it is known that security restrictions will change frequently, if there are very few users at a single site, coding security into the client application might be preferable because it would not be difficult to release and install a new version. Also, even if there are a large number of users at remote sites, if security restrictions are expected to remain static, client-side enforcement might be preferable for performance reasons. This is particularly true if there are a large number of restrictions, which will increase the amount of data that must be stored and read to enforce them, as well as increase the likelihood of errors in the data.

If application security must be table-driven, the design of the application interface and security tables should aim to minimize the negative impact on performance. On the client side, you do this by designing menus and forms in a way that minimizes the number of rules that must be applied. On the server side, design the tables to minimize the size of each record. Applying to both, the identifiers used for forms, windows, and controls should be as small as possible, and the values stored in the database should be of the same type as what is used in the application. This will minimize the number of conversions that are required as well as simplify the code and reduce the possibility of errors. In most cases, you can use a single integer value to represent the desired state of a control.

The client application should use appropriate defaults, and data should be stored only for those cases in which the default behavior must be overridden. For example, if the defaults apply to a specific role in all cases, that role will have no records in tables used to drive menu, window, and control-level security. Check the security tables as windows are constructed and, if possible, buffer the data locally so that it is read from the database only once. In general, the design of the application and the required tables should try to minimize the amount of data required to enforce application security, and try to minimize the negative impact on performance.

Previous Table of Contents Next