|
Application security limits database access from the client side of the system. Database security limits access to specific database objects, whereas application security limits access to specific interface objects. In a broader sense, application
security also includes the use of any application-specific database objects that are created to enhance security.
There are several reasons why you should use application security in addition to database security. These reasons are discussed in the following section. This chapter also covers the use of application-specific database objects and table-driven user
interfaces, as well as application and performance considerations relating to the use of application security.
In general, application security is used to enhance and supplement database security. However, it would be dangerous to rely only on application security because there is no guarantee that users will access the database only through a single
application.
Application security can enhance database security by further limiting access to database objects and by providing an additional layer of abstraction (hiding the details of available database objects). If you limit access to application-specific stored
procedures and functions and create application-specific roles, you can hide objects based on the specific user or the role of the user accessing the system. On the same basis, the application can limit access of specific columns or make specific columns
read-only by using different views for different roles or users.
In a typical database application, the interface enables the user to communicate with the database by using menus and forms related to business processes and objects rather than tables and columns. This is a convenient layer that prevents users from
having to know the names of the tables and columns being accessed.
In many cases, information in the database is used to control the behavior of the interface, based on the role of the user. System tables contain security information used by the application to drive what capabilities are available to the interface.
Menu options and forms to which the user has privileges to access are made visible, while others are hidden.
For example, if an application has an administration menu containing options specific to database administration functions, the entire menu is hidden from users who do not have the administrator role. Any forms relating to these
specific functions are inaccessible as well. This method of using application security is obviously preferable to providing a homogenous interface that enables users to try to perform operations on tables to which they have no privileges. If the
application allowed the user to access a database object that they did not have privileges to, the resulting errors would have to be trapped and displayed to the user, which can result in misunderstanding and frustration on the users' part. Using the
database to drive application security is also preferable to hard-coding rules into the client application. Table-driven application security makes it easy to update a user's privileges without having to reprogram the application.
Another reason for using application security relates to databases that are accessed by multiple applications. In this case, a single user might have multiple roles, of which only one applies to the specific application. Use an Oracle table when there
are different application roles that could apply. For example, an order entry system might have three different roles: one for order entry, one for management reporting, and one for system administration. A billing system and a accounts receivable system
might share common tables and be used by many of the same users. These users may have slightly different role definitions for each application. A simple Oracle table containing a user ID and a corresponding role can be used to determine the correct role to
be used for the application at runtime. The privileges of the appropriate role can then be used to limit the user's view of the database to the scope that was intended for the particular application.
The distinction between application security and database security is sometimes blurred, as is the case when you use application-specific database objects. As illustrated by the previous example, different applications often access a common database.
When you create objects to be used only by a specific application, application-level security is common. Although the objects exist in the database and database security is used, if the object exists only to service a specific application, it is
"owned" by the application. The argument can be made that rights granted to the stored object fall under the category of application security.
In the definition of overall systems security, the "base" objects, (clusters, tables, indexes, and sequences), and rights granted to these objects are categorized as database security issues. Views, procedures, and functions are considered
application-specific objects, and rights granted to them are categorized as application security issues.
Typically, in large database environments the primary DBA will not be responsible for creating and maintaining all application-specific objects. One possible way to handle security for a large database accessed by multiple applications is to grant
rights to sequences and views of the tables to lead developers or administrators who serve as DBAs for the individual applications. To do this, use the WITH GRANT OPTION clause. Listing 21.1 follows the example of the database shared by order entry and
billing/accounts receivable applications. It demonstrates how the WITH GRANT OPTION might be used on a subset of the database objects.
Listing 21.1. This script grants limited rights to the common database objects.
/* SEQUENCE */
CREATE PUBLIC SYNONYM account_no FOR account_no;
CREATE OR REPLACE VIEW accounts AS
SELECT * FROM account;
CREATE PUBLIC SYNONYM accounts FOR accounts;
/* SEQUENCE */
CREATE PUBLIC SYNONYM order_no FOR order_no;
CREATE OR REPLACE VIEW orders AS
SELECT * FROM order_on_acct;
CREATE PUBLIC SYNONYM orders FOR orders;
/* BillingAR System DBA & rights */
GRANT CONNECT
,RESOURCE
,CREATE USER
,CREATE ROLE
,CREATE PUBLIC SYNONYM
TO bardba IDENTIFIED BY billing;
GRANT SELECT ON account_no TO bardba
WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE ON accounts TO bardba
WITH GRANT OPTION;
GRANT SELECT ON orders TO bardba
WITH GRANT OPTION;
/* Order Entry System DBA & rights */
GRANT CONNECT
,RESOURCE
,CREATE USER
,CREATE ROLE
,CREATE PUBLIC SYNONYM
TO oedba IDENTIFIED BY entry;
GRANT SELECT ON order_no TO oedba
WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE ON orders TO oedba
WITH GRANT OPTION;
GRANT SELECT ON accounts TO oedba
WITH GRANT OPTION;
According to the previously described model of security, the script in Listing 21.1 is where database security ends and application security begins; enforcement responsibilities are delegated to the administrators of the specific applications. Note that
public synonyms were created to hide the system ID of the DBA, and that limited rights were granted to each application "super-user." The bardba and oedba users will create application-specific objects, roles, and users, and grant rights based on
the individual application—within the database security restrictions enforced by the DBA by the limited rights granted to them.
Note that the bardba user received read-only access to the orders view, and that the oedba user received read-only access to the accounts view, while neither user received the delete privilege to either view. This will limit the objects they can create,
as well as the rights that they can grant to additional roles and users. For example, the bardba user will not be able to create a procedure to insert records into orders, or grant the insert privilege on orders to other roles and users.
Application-specific objects include procedures, functions, packages, and views.
Stored procedures and functions are typically used to insert, update, and delete records. In many cases, these subprograms will operate only on views, and not on the underlying tables themselves. This makes it easier for administrators to change the
underlying structures as needed, without affecting the applications that access them. Views are also used to present separate tables as one logical business object or to limit access to specific columns.
You should use procedures and functions to perform all transactions for several reasons. One reason to use database subroutines is that they can be used to enforce integrity. For example, if a sequence is used to generate unique primary key values for a
particular table, encapsulating the insert into a procedure or function can ensure that the sequence is always used. When user IDs or timestamps are being stored, the values for the user IDs and timestamps can be supplied from within the stored procedure
or function as well. This ensures the integrity of these values, simplifies transaction processing for the client-side application, and helps reduce the amount of data being passed between the client and the server.
Using procedures and functions can also enhance database security. By granting only EXECUTE privileges on subprograms, the views and tables on which the subprograms operate remain unavailable. This prevents users from accessing them through SQL*Plus or
one of the many desktop database or reporting tools that might enable them to modify the subprograms.
There are additional advantages to using packages. An Oracle package encapsulates a group of variables, constants, cursors, and subprograms into a single logical unit. This can greatly simplify the process of granting privileges, and improve overall
performance. When EXECUTE is granted on a package, the user receives the execute privilege for each subprogram contained in the package specification. The entire package is loaded into memory when a packaged object is first referenced by the application,
which reduces I/O and improves performance for subsequent calls to subprograms within the package.
You should always use views to present result sets to the application. As mentioned previously, using views can help insulate the application from structural changes to the underlying tables and limit access to specific columns. Views can also simplify
embedded SQL in the application by completely eliminating the need for joins. If all joins are handled by the views, the application can treat the result set as if it were a single table. This can also simplify the process of granting privileges. For
example, if a view is created that joins seven tables, the user need only have the SELECT privilege to the view, not the underlying seven tables. The user will then be able to access the view. If the join were accomplished within SQL embedded in the
application, the user would need the SELECT privilege for each of the seven tables. Through column aliasing, views can also present result sets in terms of business lingo rather than column names, which are often very different.
Ideally, application-specific stored procedures and functions are used for all transactions and operate only on views, whereas all result sets are retrieved through application-specific views. Such a configuration greatly enhances overall security and
can completely insulate the client application from changes to the structure of the underlying tables. The procedures, functions, and views can be used to present a consistent database interface, regardless of the underlying structures on which they
operate. By granting only EXECUTE on subprograms and SELECT on views, the users cannot perform any transactions or SELECTS from outside the application-specific objects to which these privileges are granted. Exclusive use of procedures, functions, and
views places a layer of abstraction between the users and the database, hiding the actual implementations of the tables. This is becoming an increasingly important security consideration as users become more sophisticated and generic reporting tools become
more powerful.
Public synonyms are a method of making a database object available to all users of the database. You can be use public synonyms to hide the ID of the owner of application objects and prevent an application from having to specify a schema. Granting
privileges to the public synonyms rather than the objects themselves also provides an additional layer of abstraction. The diagram in Figure 21.1 provides a visual representation of the model of application security that has been presented in this section.
Figure 21.1. This diagram represents the role of database objects in application security.
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.
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 shown following:
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 21.2.
Listing 21.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 data type, 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 particular control at runtime (besides the actual text of the menu item). This property is typically a string data type 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 21.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 tabled 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 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 21.3 presents a sample implementation of these concepts in Visual Basic.
Listing 21.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
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. However, this approach has its limitations. 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 to 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 these users under any circumstances. Figures 21.2 and 21.3 show examples of what the main application window might look like to oe_admin and non-oe_admin users, respectively.
Figure 21.2. This main window has all top-level menus visible and enabled.
Figure 21.3. In this main window, the top-level menu item Admin is completely hidden from a user who does not have the oe_admin role.
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 21.4.
Figure 21.4. In this main window, the menu option Look-Ups is disabled for a user who has the oe_manager role.
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.
You can apply similar methods and principles to enforce application security for windows and specific controls. In some cases, disabling or hiding a menu option prevents access to a particular form. Under these circumstances, no additional security
should be required to prevent a user from accessing the form. However, it is more common for a particular form to be read-only for a specific application role or group of roles. In some cases, specific controls must be made read-only or disabled based on
the role of the user.
As with menu options, you can design tables to drive application security for access to forms and specific controls. Listing 21.4 demonstrates one possible implementation of data-driven window and control-based application security.
Listing 21.4. This DDL script creates tables that can be used to dynamically alter the states of windows and controls at runtime.
CREATE TABLE oe_window_privileges (
app_role VARCHAR2(20)
,window_id VARCHAR2(10)
,read_only NUMBER(1) NOT NULL
,CONSTRAINT window_priv_pk PRIMARY KEY (app_role, window_id)
);
CREATE TABLE oe_control_privileges (
app_role VARCHAR2(20)
,window_id VARCHAR2(10)
,control_id VARCHAR2(10)
,visible NUMBER(1) NOT NULL
,read_only NUMBER(1) NOT NULL
,CONSTRAINT cntrl_priv_pk PRIMARY KEY
(app_role, window_id, control_id)
);
The same potential problems that apply to data-driven menu security apply to data-driven window and control security. First, there must be a method of determining the application role for a specific user. If application security is being applied to menu
options, the same application role should apply to window and control-based security for a specific user. The user's role would then need to be read only once and stored in a global variable to be used whenever security restrictions must be checked. Within
the table being used to determine which windows and controls can be accessed for a particular role, there must be a way to uniquely identify a window as well as individual controls within a window. Again, the problem with this approach is that the
identifiers must exactly match those being used by the client application. As is the case with menu options, many development tools have only a Tag property available to use as this identifier. Any mismatch between identifiers in the application and the
identifiers being stored in the table will result in a breakdown of application security.
Code used to retrieve security information from the database and alter the states for windows and controls should be placed in the appropriate constructors. Depending on the development tool, this can be a potential problem because objects that need to
be referenced might not be instantiated at the time the window is constructed. For example, in C or C++, the constructors for a window's controls are typically called from within the constructor of the window itself. The application should retrieve values
from the database before calling the constructors for any controls that might be affected by application security. The controls themselves can then be disabled or hidden as needed.
In MFC applications, for example, the Create member function is used to position and set the style for most interface objects. An application can set the style constants dynamically at runtime by calling Create with style constants read from the
database. For example, in Windows 3.1, the ES_READONLY style constant can be passed to the Create member function of an edit control to make it read-only, and any control object that inherits from CWnd can use the WS_DISABLED style constant to disable a
control. If the objects are constructed as part of a dialog resource, messages can be sent that will have the same effect. For example, EM_SETREADONLY can be sent to an edit box to make it read-only at any time after it is constructed.
In some cases, an application will need to hide controls based on the current user. In this case, the objects themselves should simply not be constructed, if possible. Note that the oe_control_privileges table in Listing 21.4 contains the columns
visible and read_only. The read_only column should be redefined for C and C++ applications to accept style constants instead of the numeric representations of the boolean values TRUE and FALSE. If a control will not be visible, no values need be supplied
for the style constant.
The order in which objects are constructed is not as much of a concern with most Windows GUI design tools, such as PowerBuilder and Visual Basic, unless controls are being placed dynamically at runtime. In Visual Basic, for example, all controls that
were placed on a form at design time can be referenced in the load event (constructor) of a form. Unfortunately, Visual Basic controls do not provide all of the flexibility of the analogous MFC objects. The Visual Basic text box, which is roughly
equivalent to the MFC CEdit class, does not provide a read-only property. However, the Windows API can be used to set a Visual Basic text box read-only at runtime, using the SendMessage function and the hWnd property of the text box.
The development tool being used will have an impact on the structure of the tables being used to drive application security. Tables such as those in Listing 21.4 will fit most situations, with minor modifications. Because the application must interpret
the values in the tables based on the columns in which the values appear, the actual implementation is not important as long as it is applied consistently. For example, the oe_control_privileges table does not have an enabled column. However, the
application will set the control as enabled rather than read-only based on the data in the control. As mentioned previously, if the application is being developed using C or C++, it might be preferable to replace the read_only column with a style column,
used to store a style constant, or a combination of style constants to be applied to the control.
Regardless of the development tool or data structures you use to drive the interface, follow the same basic steps to enforce application security for windows and controls. First, in the constructor of a window, security information pertaining to the
window is read from the database. This information must then be interpreted by the application through a process that maps values from the database to controls and properties. Finally, the properties of controls must be set based on this information. The
entire process can become more complicated when a particular window serves more than one purpose. For example, in many cases the same form that is used to add a record is used to edit a record.
Typically, different rules or security restrictions will apply to adding a record versus editing an existing record. In these cases, the tables provided as an example in Listing 21.4 will not suffice. One possible solution is to define a constant to be
used by the application to determine whether the window is being used to add a record or to edit one. You could add an additional column to the tables in Listing 21.4 to differentiate these modes. This column would also have to be part of the primary key.
The application will now be required to prepare a different select statement based on whether the window is in add mode or edit mode.
Listing 21.5, which applies to the Order Item form shown in Figure 21.5, provides a simple example of how these concepts can be applied to application security using Visual Basic. The example is based on an order detail entry form that is used to add
records by all salespeople. The example assumes that the date shipped field is updated by another process (such as the shipping department filling the order and creating a packing slip). It also assumes that only a manager can override the default price
read from the database, and can do so only after the item has been added to the order. Although this simple example might seem a bit contrived, these types of rules are sometimes enforced to provide an additional audit trail for unusual transactions.
Listing 21.5. An example of enforcing application security for windows and controls in Visual Basic.
Sub SetSecurityStates(dsControlSecurity() As Dynaset)
Dim iControlID As Integer
Dim bVal As Integer
Dim iRet As Integer
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
Figure 21.5. This window uses application security to make the price and date shipped for a particular role read-only.
In the example, security restrictions apply to only two fields; therefore, only two fields are checked. Note that the Listing 21.5 assumes that the fields were set 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 21.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 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 only enable salespersons to view and edit 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;
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 a simple task in any programming language. Using views to apply filters makes
this task of application security the easiest to implement.
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 that 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 will result in a less cluttered and more intuitive interface.
However, as the previous section illustrated, 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 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 and 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 only be stored 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.
Application security is not a substitute for database security, but it can be used to enhance database security and enforce rules that cannot be enforced through integrity constraints. Using application-specific stored procedures, functions, and views
will enhance security and performance, while simplifying the process of developing the client application. Enforcing application security through the application will result in a more intuitive and user-friendly interface.
| 
