Using SQL Server 6.5 for Application Control

Control your program's flow via SQL Server security permissions

If you develop Visual Basic (VB) applications that modify data in SQL Server tables, you can design application controls that prevent users from seeing command buttons they aren't authorized to use. Implementing application controls is more professional and user-friendly than building applications that let users try to delete records they can't, then send an error message. Here's an overview of new methods that streamline the control process and take advantage of SQL Server's security features.

The integrated security in SQL Server gives you flexible control of your application. You can use information about a user's permissions on various objects within SQL Server to customize the application's operation. You can turn on and off various command buttons, restrict access to specific columns, and control the application's flow.

To implement application controls, you need to know how SQL Server maintains security information. Each object in SQL Server has a unique object identifier. For the purposes of this discussion about security permissions, objects can include tables, views, and stored procedures. In addition, each user has a unique user ID number. SQL Server uses this user ID number and the object numbers to maintain a security information table. You use data from three SQL Server system tables—sysusers, sysobjects, and sysprotects—to get permission information about any object within the SQL Server database. A fourth SQL Server table, syscolumns, contains column information about SQL Server tables and is important when you implement column-level security, a topic that's beyond the scope of this article.

The sysusers table contains a list of SQL Server user names and group names, and a corresponding ID number for each. This table is important because SQL Server lets you establish security permissions at three levels. First, you can give explicit security permissions to an individual user. Next, you can grant specific security permissions to a group, which affects all users in that group. Finally, you can grant specific security permissions to the special system group public, a catch-all group that lets you provide systemwide access to objects. Each entity—the individual user, the group the user belongs to, and the system public group—has a unique user ID. You need to retrieve these UIDs to properly determine someone's security permissions.

The sysobjects system table contains information about each object within the SQL Server database. This table is important to security permission only because each object stores the user ID of the person who created the object. (The table also contains each object's unique identifier, although you don't need this information to determine security permissions because the OBJECT_ID system function performs the same task.) Each object's owner has full access to the object regardless of specific security permissions defined elsewhere.

Finally, the sysprotects table contains permission data for each object and each user. When you grant or revoke a user's permissions, SQL Server stores pertinent information in this table, which is the most important one you'll use to retrieve security information from the database. Table 1 shows the fields sysprotects contains. If you grant user mdeignan insert permission to the personnel table, and the public group has select permission, the sysprotects table contains two entries: an entry for the public group, specifying that the group has select permission, and an entry for user mdeignan, specifying he has insert permission. Names don't appear in the table. Instead, the ID column contains the object ID for the personnel table, and the user ID (UID) field contains the user ID for the public group and user mdeignan. You can use this information in any development environment to control how your program operates.

Applying Controls

You can develop code within your application that determines whether a user has specific permission to an object, and that turns on a command button based on that permission. For example, if a user has permission to insert records into the database, you can enable the Add New Record command button within your application.

However, retrieving information each time you want to check permission is time-consuming. In addition, you need to code a routine within your application to retrieve the user ID and group ID from the sysusers table. Then you need to retrieve the user ID of the public group from the sysusers table. Finally, you combine the information from those sources to retrieve permission data from the sysprotects table. Listing 1, page 46, contains the VB code you can use to determine whether a user has delete permission for the personnel table.

The code in Listing 1 checks for a revoke protection type and sets the status of that protection to false. This check is necessary because although the system public group or the user's group has delete permission to the table, the individual user or group might be specifically excluded from updating the table.

In a sense, SQL Server permissions are additive. If a user doesn't have permission to an object, then SQL Server checks whether the user's group has permission to the object. If no user or group permission exists, the default permission granted to the system's public group applies. If you specify access permissions for the public group, but you want to prevent people or groups from gaining access, you need to specifically revoke those permissions from an individual or group of users. SQL Server recognizes that a revoke condition exists and stores a record in the sysprotects table to mark this condition. The final select command from the sysprotects table in the code in Listing 1 checks for revoke conditions.

You can develop a generic VB routine that will return a Boolean value to show whether a user has specific permission to an object. But this approach incurs overhead at the application level because you need to call the routine each time you want to retrieve information about the user's permissions.

You can develop a generic routine that sends a bitmap of access permissions in integer values to the application, which tests the values to determine whether to turn command buttons on or off. To streamline the control process, you can write a generic routine as a stored procedure, then call the stored procedure from the application. This way, SQL Server does the processing, and you can leverage system functions to make the stored procedure run faster. The stored procedure in Listing 2, page 47, takes a table name as a passed parameter, and returns an integer value containing a bitmap of access permissions for the table. To call this routine from VB, you can construct a function subroutine, as Listing 3 shows.

The operation theory is simple: If the user calling the function has select access, bit 0 is set on. Bit 1 determines insert permission, bit 2 update permission, and bit 3 delete permission. SQL Server retrieves this information for the user, user's group, and system public group from the sysprotects table, based on the object ID of a table. Also, you can perform a function similar to the one Listing 4 shows.

By testing individual bits in the returned integer value, you can control program flow. For example, Listing 4 lets you test the insert, update, and delete permissions to disable (or make invisible) certain command buttons that execute routines or allow access to other forms within the application. You can prevent users from trying to perform database operations they don't have SQL Server permissions for and, thus, prevent them from seeing frustrating error messages.

You Have Control

With a basic understanding of how SQL Server stores permission information within the sysprotects table, you can construct nearly any type of application control. By declaring global variables and populating them when you launch your program, you can control the application's user interface and program flow.