User Access Control
In a client server application there are many ways to handle user access, and multiple levels at which it can be implemented:
-
User Interface At this level we are concerned with two things. First the "Log In" which is the mechanism by which a user establishes their credentials and their right to access the application. Second we may need to implement differential access based upon a user's log in credentials. The actual implementation of both of these must be handled in the presentation tier, although the validation of the user input will have to be handled by the Middle Tier and will normally involve the database too
-
Middle Tier At this level the issue is to ensure that any request for data, or to save information to the database, is coming from a valid and authorized source. While less critical (and often omitted entirely) in LAN/Intranet applications, in any application that is exposed to the internet (whether directly or via a web service) this "Request Authentication" is crucial to maintaining application and data security and integrity. This will usually involve the database too
-
Database Back end databases (like SQL Server) implement their own multi-layered security model based on user login and define database "roles" that define the actions a user may perform. It typically allows the creation of, and assignment of users to, customized application roles that can be used to set up detailed rules for users
However, when reduced to basics, all of these come down to the same issue; do we recognize this user and are they allowed to do whatever it is that they are asking?
As noted, back end databases implement complex, layered, security models but while such layered security is useful in some specialized circumstances it is unlikely that, in a Client/Server application it will be used to handle individual users directly. The reason is simply that the management and maintenance of a large population of users on an individual basis is time consuming (and expensive). The question that should, about now, be forming in your mind is how to validate that a user is allowed access to the database if that user does not have a login into the database.
Impersonation
This relies on the creation of two separate databases, the first contains only the tables needed to map user identities to connection information and the second is the application database itself (see Figure 1). The idea here is that the login screen in the presentation tier is directly connected to this database (using a trusted connection so that there is no need to set up individual users in SQL Server). If the user identity is validated, the necessary connection parameters for that user are returned.
Figure 1: User Impersonation model
This information can be as simple as the name of a DSN to use, an explicit connection string or the information for a standard 'user' assigned to a specific role within the database. That information must then be stored directly in the presentation tier and sent as part of each request to the Middle Tier. This, in turn, uses the specified information to connect to the application database and execute the request. This model has a couple of significant benefits:
-
The user has no direct login, and never has access to the information used to connect them to the application database
-
By mapping end users to 'standard' database users full use can be made of the features offered by SQL Server's security model since the middle tier "impersonates" the user on each request.
The main drawback is, of course, that the middle tier has either to keep connecting and disconnecting ( a relatively slow process), or maintain multiple connections to the database. A secondary concern is that the connection information has to be stored in the presentation tier and that, by itself, can be a security risk in some scenarios. However this model is certainly viable and is often used for in-house applications.
Authentication
In this approach the only user that has access to the database is the middle tier component (or, more accurately, the Data Access Layer of the Middle Tier) itself. When a user logs in, the middle tier uses its own connection to the database to validate the user information and generates an 'authentication ID' or 'token' that is returned to the Presentation Tier (Figure 2). Any further requests from this user include the assigned id and, providing it is recognized as valid (they are often time limited), no further authentication is needed. This is very widely used model and offers several benefits:
-
The middle tier is not constantly connecting/disconnecting, nor does it have to manage a connection pool. This also keeps the number of connections to the database to a minimum
-
The middle tier can be responsible for assigning, and tracking, the authentication IDs. This hides all details of the implementation from the Presentation Tier which has only a single item of data to manage – the assigned authentication ID, usually stored in a "cookie"
-
Access security and control can be handled in code in the middle tier, which can even use locally held data to avoid making round trips to the server
Figure 2: User Authentication Model
The main drawback to this model is that is more complex than the simple impersonation approach outlined above and it places more responsibility on the middle tier. This model is widely used for applications that are exposed over the internet, either directly, or as a web service.
Error Handling
As noted earlier one of the major differences between a Client/Server application and a traditional Desktop application is in the way in which errors must be handled. We can safely assume that the initial detection and generation of an error will be handled by the application responsible for it, whether that is the database, the Middle tier or the User Interface. So the issue really comes down to how to handle, and record, errors in this multi-tier environment.
The first issue to be decided is whether errors should be logged, and if so, where? The obvious place to log errors would be in the database but, of course, that becomes problematic if the error is such that the database is not available (i.e. a connection issue, or a network problem or even a server crash). One of the benefits of using a data aware middle tier (like Visual FoxPro) is that it does have a local database engine and can easily create local error logs as local files. But what if it the problem is that the middle tier cannot be reached, or has crashed?
In that scenario we are left with a User Interface which cannot connect to anything, or do anything other than simply tell the user that it cannot connect and, possibly broadcast some sort of notification – maybe by E-Mail (assuming that the client machine still has access to E-Mail of course). In other words if the primary connection to the middle tier cannot be established, that error has to be handled directly by the client.
In all other cases we really do want the middle tier to be the responsible component. Whether the error is within the middle tier itself, the database or the presentation tier all errors should be handled here. The mechanism for logging the error can be anything that is convenient although my personal preference is to use a local DBF file simply because it is easy to extract the information into any form that may be needed.
Whenever an error is detected, a call is first made to the middle tier's "Log Error" method which handles the task of logging the error. Subsequent action depends upon the nature and location of the error but typically it involves the transmission of a more user-friendly message to the Presentation Tier for display.
Transaction Management
By default back end servers generally implement "Implicit Transactions with Auto-Commit" which means that each SQL Statement executed by a connection is considered a separate transaction. In other words, each INSERT, UPDATE or DELETE command is treated as a separate transaction and either succeeds (and is committed) or fails (and is rolled back).
NOTE: Other commands may require, and initiate, transactions, but for the purpose of this discussion we will concern ourselves only with the commands that actually modify data. Data Definition Language statements are rarely, if ever, used in a Client Server application environment.
Whether it is ever necessary to change this behavior will, of course, depend on the requirements of the application. As long as there is never any need to wrap multiple update commands in a single transaction the default setting will work just fine. However, when inserting records into a more complex relational model - where Parent-Child relationships must be maintained may require a departure from the basic one command = one transaction approach.
To handle more complex situations, the auto-commit on the connection is disabled so that transactions are only initiated by the server if a one is not already open, or when an explict "BEGIN TRANSACTION" command is issued. Open transactions are not closed by the server, and an explicit COMMIT or ROLLBACK command must be initiated by the application in order to close the transaction(s) that it has opened. Clearly this requires more complex programming, and moves the responsibility for transaction management from the database to the application. Hopwever, in situations where handling multi-table updates as a single unit of work there is no alternative.
Different servers implement transactions in slightly different ways and if you find that you need to use anything other than "Implicit with Auto-Commit" you will need to determine what options are available in the server that you are using and how to best utilize the functionality provided.