Column-Level Application Security

How to restrict access when you need to

If you're a Visual Basic (VB) programmer on SQL Server, you can control application security in different ways. For example, you can examine the contents of SQL Server tables to determine a user's security permissions, then use that information to prevent users from entering a record edit dialog when they don't have update permission to the underlying SQL Server table.

But such global program flow isn't useful in a large application program. To provide application control for a personnel database application, for example, you can use SQL Server's column-level security features, which let you grant users SELECT and UPDATE privileges to specific table columns. One data-entry clerk can enter changes to employees' addresses, and a personnel manager with higher security can enter employees' hourly pay rates for check computations. Users can retrieve and update data from only the columns they're authorized to change. SQL Server column-level control gives you good program control within applications, if you can retrieve and digest the information hidden within SQL Server's internal system tables. Column-level security is a terrific tool for restricting access to specific columns within the database table. (This article addresses column-level security in SQL Server 6.5. For information on column-level security in SQL Server 7.0, see the sidebar on page 42, "Permissions in SQL Server 7.0.")

Internal Affairs

The SQL Server sysusers, sysobjects, and sysprotects tables contain information you need to implement application control. Only sysprotects contains specific object security information for the tables and columns you want to exert control over, but the sysusers and sysobjects tables contain information you need to properly select and digest information from the sysprotects table. (For information about how these tables work in SQL Server, see "Using SQL Server 6.5 for Application Control," April 1999) The sysprotects table has six fields that contain information about users' privileges to access and update data within a SQL Server database. Table 1, page 38, shows these fields.

By using the OBJECT_ID() function to properly specify the ID of the database table you want to examine security on, and the user ID (UID) of the user whose security status you want to check, you can retrieve all the columns from the sysprotects table that contain security information for that user and table. Then, you can process these columns of data into a digestible form that the application program uses to turn on and off various command buttons within the application.

You can use the columns field (which Table 1 defines) in the sysprotects table to control the column-level security features of the database table in question. With column-level security, you can maintain a simple table structure for your application, yet limit each user's access to specific fields within that table.

Consider the example in Screen 1. In SQL Enterprise Manager (which you open by selecting Object, then Permissions, from the menu bar), I edited the permissions for the PERSONNEL table, which contains 10 fields. I want to let user mdeignan select all fields except payrate and update all fields except employeeid, payrate, startdate, and termdate.

When you select column-level security, you affect the contents of the columns column in the sysprotects table by changing the value that appears in this column. Also, note that the INSERT and DELETE permissions don't have column-level security attributes that you can set. Consider that when you issue a DELETE SQL statement, you can't delete specific columns (that would be an UPDATE operation). Similarly, when performing an INSERT, SQL Server is allocating an entire row (all columns) of data. Thus, the columns field of the sysprotects table will contain intermediate data when the action field is set to 193 (SELECT permission) or 197 (UPDATE permission).

Table 2 shows the three states the columns field can be in. The columns field contains a bit mask of values, one bit for each column in the table. Bit 0 is set on when you aren't using column-level security. When you are using column-level security, bit 0 is off. The presence of another bit set on means you have permission to SELECT or UPDATE designated database columns. You can use Transact-SQL's (T-SQL's) implicit conversion feature to convert the contents of the columns field into an integer. Then, you can use an AND statement to compare the integer to the bit number of the column. If the result is 0, the bit is not set on (is 0). If the result is not 0, the bit is set on and the value will equal the AND statement bit mask.

Manipulating the columns bit mask can be tricky. Byte 0 contains column-permission indicators for columns 1 through 7, byte 1 contains permissions for columns 8 through 15, byte 3 for columns 16 through 23, etc. This layout is important when a program attempts to perform mathematical manipulations on the column contents because the layout affects the CONVERT function. Feeding the contents of the column into a CONVERT(int, column) function will yield an incorrect numerical value. For example, if the value is 0x0001, meaning that column 8 has its bit set on, CONVERT(int) will convert the value into an integer 1 value—but the correct value is 256. So you need to reverse the byte order in the column from 0x0001 to 0x0100 to perform the bitwise operation correctly.

Now that you know how to use and manipulate the columns data, you need to determine which bit in the columns column to test. The syscolumns table has the answer. The syscolumns table contains detailed information on every column in the database. This information goes beyond a simple column name and position. It includes field length, data type, and, most important for this discussion, column number. Table 3 shows the fields contained in the syscolumns system table.

Although application developers can use the contents of the syscolumns table for various purposes, for implementing application security, only three columns are important: id, colid, and name. To test the contents of the column bit mask to see whether you have UPDATE or SELECT permission to a field, you need to find out which column that named field occupies. A control in a Microsoft Access or VB application does not contain this information; it merely contains information on the field name within the recordset. Thus, in this application, you need to use the table and column name to manually check permissions.

For example, suppose you want to see whether a user has update permission to the Address field in the PERSONNEL table. Before you can interrogate the column bitmask, you need to determine which bit to test. syscolumns gives you that information.

Selections

Now that you know how the columns column in the sysprotects table relates to the syscolumns table, you can use this information to determine column-level permissions. For example, if you examine the contents of the sysprotects table in the sample PERSONNEL table for only the SELECT and UPDATE permission settings, and specify the following SQL statements, the results will show that user mdeignan has GRANT (protecttype 205) permission to the fields in Table 4.

SELECT action, protecttype, columns FROM sysprotects WHERE ID = OBJECT_ID("PERSONNEL") AND UID = USER_ID("MDEIGNAN")

Interpreting this data is challenging. Each bit represents valid permission to retrieve (SELECT) or change (UPDATE) the data in the PERSONNEL table. To determine whether you can update the Address field, you must find out which bit you have to examine: Execute the following SQL statement:

SELECT COLID, NAME FROM syscolumns WHERE ID = OBJECT_ID('PERSONNEL') 

The results in Table 5 show you that in order to see whether you have access to update the Address field, you need to test bit 4. If this bit is set (1), you can update the field. If the bit is off (0), you can't update the field. To test bit 4, you can convert the contents of the columns column into an integer value, then use an AND statement with 0x1000. In terms of bitwise operations, after you have reversed the data in the column so the CONVERT function will work, you expect to see the results in Figure 1.

To conduct the bit test, you can execute the SELECT statement that Listing 1 shows. The query results show that the user has permission to update that field. If you run the same query, substituting EmployeeID for Address, you'll see the results Permission 0, meaning that the user doesn't have permission to update that field. You need to code a test in your VB routine to set the Enabled property of that control to false to prevent the user from trying to update the field.

Complications

This application control has limitations. Specifically, it works only for tables with 30 or fewer columns because of SQL Server's implicit data-type conversions in the SELECT statements that retrieve the columns field from the sysprotects table. When performing a bitwise AND function, SQL Server converts the data in the columns field to an integer value, which is in turn bitwise ANDed with the integer value returned from the POWER() function. If you try to use a POWER(2, x), and if X>30 you'll have an arithmetic overflow, because 2^31 exceeds the maximum value of an integer data type. In addition, the implicit integer conversion process of the columns field will return negative numbers. So the examples in this article will work only if you have fewer than 31 columns in your table (or test only columns with column IDs less than 31).

You can code a SQL Server stored procedure to isolate the specific byte in the columns field of the sysprotects table, and you can test the specific bit to return an appropriate indicator to the calling application. For code to circumvent the aforementioned problem, refer to this article on the SQL Server Magazine Web site at www.sqlmag.com. This routine will return an integer value with bit 0 set on (if the user has SELECT permission to the column) and bit 3 set on (if the user has UPDATE permission to the column). You can then use bitwise operators within VB to test the return value. To access this routine from VB, you can use the routine that Listing 2 shows. To make the control visible or invisible depending on SELECT permissions, and to enable and disable data from being edited depending on UPDATE permissions, you can execute the code that Listing 3 shows.

Many Methods

The method I've presented in this article is only one you can use to implement column-level security control of your application programs. My approach can be a starting point for you in using SQL Server's system tables to interactively customize the way your application programs execute.