| Downloads |
|---|
| 5392.zip |
You can customize security within your application by using SQL Server's system tables to control application security. The procedures I've outlined in this article and in "Using SQL Server 6.5 for Application Control" (April 1999) are designed for SQL Server 6.5, but they work with SQL Server 7.0. However, SQL Server 7.0 has security flexibility that makes testing for permissions easier. Let's examine the differences between SQL Server 6.5 and 7.0 in testing for permission and how to code SQL Server stored procedures and Visual Basic (VB) programs to take advantage of the new features in SQL Server 7.0.
Better Mouse Traps
SQL Server 7.0 introduced a new system function, PERMISSIONS(), which lets you test for the current user's access level to various tables and columns within SQL Server. This function lets you avoid much of the stored procedure code that SQL Server 6.5 required. Previously, you had to interpret all the different permissions data in sysprotects—not only individual user settings, but permission settings for their groups, and specific REVOKE permissions. The PERMISSIONS() function does all of this work for you, saving a great deal of time and stored procedure code. The PERMISSIONS() function has the following format:
PERMISSIONS(OBJECT_ID('object_name'), \[column name\])
If you are not testing column-level security, you can omit the column name parameters. The return value is a bit mask containing permission data. For table-level security, the permissions returned are those in Table A. Table B shows the bit mask SQL Server returns for column-level security when you specify a column name in the PERMISSIONS function.
Retrieving and using these bit masks is easy and useful. The PERMISSIONS function saves time because you don't need to individually test a user's, user's group, and public sysprotect entries. For example, to test a user's permissions to the PERSONNEL table in SQL Server 7.0, you can execute the following SQL query:
SELECT PERMISSIONS(OBJECT_ID('PERSONNEL'))
12291
You will get a single permissions value in return, which you can then programmatically evaluate within your VB program to determine whether the user has permission to a particular object.
In this example, the return value is 12291 because the user has SELECT and UPDATE permissions to all the table columns. Thus, the results of the PERMISSIONS() function will show the following bits set on: bit 0 (decimal value 1); bit 1 (decimal value 2); bit 12 (decimal value 4096); bit 13 (decimal value 8192).
When you're not using column-level security permissions, the lower two bits will be set on to show that the user has permission to SELECT and UPDATE, respectively, all columns in the table. However, if you are using column-level security, the same query will yield 12288 to show only bits 12 and 13 are set on, whereas bits 0 and 1 are off. The results tell you that the user can select some of the table columns, but not all.
Listing 1 shows a generic query as a SQL Server stored procedure that you can use to generate the permissions value for testing from VB. Note that the stored procedure in Listing A is significantly less code than the SQL Server 6.5 version (see the main article). Next, you need a VB program to retrieve the value. Listing B shows the VB subroutine to do that. After you execute the routines in Listings 1 and 2, you can use the function that Listing C shows to retrieve permissions for the Personnel table. If the subroutine shows that a user has INSERT permission, you can turn on a command button.
An important additional step you need to take in VB is to check for the presence of column-level security if you are checking for either SELECT or UPDATE permissions. If the table permissions are returned with bits 12 or 13 set on and bits 0 or 1 set off, you have column-level security. Thus, your application program needs to retrieve and analyze those column-specific permissions. To do so, you can use the subroutine that Listing D shows.
With a few major modifications to the sp_GetColumnPerms stored procedure, you can use the PERMISSIONS T-SQL function to get this information. Listing E shows the stored procedure to retrieve column-specific permissions. This stored procedure is easier to understand and is more streamlined than previous VB and SQL Server stored procedure code. The procedure includes more code for parameter validation than for data retrieval. Again, to access the data from VB, you need a VB subroutine to call the stored procedure. Listing F shows this subroutine. Listing G shows how to digest the results of the stored procedure within a VB program.
You can use the listings in this article to take advantage of SQL Server 7.0's PERMISSIONS feature to interrogate table and column permissions. However, for information about downward compatibility with SQL Server 6.5, refer to the main article and the April article on application control.