By Jonathan Lurie and Timothy Stockstill
Rewriting applications because of database security gaps is costly and time consuming. One of the easiest ways to avoid rewrites is to make security a priority during the initial database design and to enforce security at each tier.
We’ll discuss some of the techniques for providing a solid security foundation for the data tier of an application that resides on Microsoft SQL Server 7 and 2000. While this is a broad topic, we’ll focus on basic security concepts as well as column- and row-level security.
Basic SQL Server security concepts
The security implementations in this article will rely on the basic internal security system provided by SQL Server. Before a user can be granted any level of permissions, a user account must be created on the SQL Server. The user account can be either a domain or local account (using sp_grantlogin), or if the server is operating in the Mixed Security Mode, it can be a built-in SQL Server login (using sp_addlogin). Once a login has been created on the server, the login must then be added as a valid user for any of the databases the user intends to access (using sp_grantdbaccess).
Now that the user has the ability to connect to the server (a login was created) and use one or more databases on the server (a user account was created for each database), the next step is to specify the level of access the user has inside each database. This is done with the GRANT, DENY, and REVOKE SQL DDL statements. The SQL GRANT statement is fairly well understood, so we won’t dive into it here, but the DENY and REVOKE statements are less well known, especially for new database developers, so we’ll focus on them.
The DENY statement
The DENY statement is the opposite of the GRANT statement. Granting a database user access to a table (or object) specifies that the user has the specific right to access the table. Denying a database user access to a table (or object) specifies that under no circumstances should the user have access to the object. Therefore, if a user is denied access to an object either through his or her own user account or through any role (previously known in SQL 6.5 as groups) he or she belongs to, then the user will not be given access to the object.
The REVOKE statement
The REVOKE statement simply indicates that a previously assigned security setting should be undone. For example, if a user had previous access to a table with the GRANT statement, the REVOKE statement would undo that GRANT (note that the user may still have access to the table through one of the roles he or she belongs to). On the other hand, if the user had been denied access to the table at the user level, the REVOKE statement would delete (or undo) that denial.
The SQL Server security system allows for the definition of roles. An interesting note about the SQL Server security system is that role access is hierarchical, meaning that a SQL Server role can contain other SQL Server roles. As a result, whenever a hierarchical structure is put in place, user access can quickly become very difficult to manage or to determine. The user will receive the union of all of the permissions that have been granted to the user or to any one of the roles to which the user belongs, with denials taking precedence over grants—i.e., one denial anywhere in the chain and the user has no access.
Column-level security is a relatively simple matter as all the components necessary are built into the internal SQL Server security system. Access to any column can be secured with the SQL Server GRANT, REVOKE, and DENY statements (programmatically through DMO, or through the Enterprise manager). The statement in Listing A provides SELECT permissions on the stor_id, ord_num, ord_date, qty, and title_id columns of the sales table in the pubs database to the user b_smith. The sales table in the pubs database has another column the payterms column, which is not included in Listing A. Therefore, the statement in Listing A effectively gives the user the ability to view any column except the payterms column. At the column level, SELECT and UPDATE permissions can be assigned to any user or role.
SQL Server does not provide an internal method of securing individual rows in the database. However, by using SQL Server views, it’s possible to provide row-level security without too much effort. The primary component in a row-level security system is the use of views. Creating a view that resides on top of the table and giving the user access to the view but not the underlying table can enforce an effective row-level security system. The statement in Listing B demonstrates the creation of a single view per user approach. This view limits the records b_smith can access to only those from store 6380 (presumably the store where b_smith works). The statement also uses the WITH CHECK OPTION view option. This option prevents a user of the view (b_smith, in this case) from inserting a record that he or she would not be able to retrieve later. (In this case, b_smith would not be able to insert a record with a stor_id of any value other than 6380.) One disadvantage of this approach is that it requires views to be created for each role or user.
More complicated views are used to provide row-level access to a table. These views generally provide a single view, as opposed to one per user, incorporating all of the row-level security mechanisms of all of the users in the database. This technique offers the advantage of easier administration since there are fewer views to keep track of, which subsequently translates into less code (our favorite advantage).
Database security is worth the extra effort
Providing a fully functional system for securing your database is a significant yet often overlooked step in developing a well-rounded application security system. It’s often tempting to enforce security only within the application itself, with a single password giving all users access to the database. This entails less work, but it often leaves too many open holes in the database for crackers to exploit. (It’s not difficult to connect to a SQL Server database using Access—a user doesn’t even have to know SQL.) It’s a little more work to provide a robust security system at the database level, but the consequences of not doing so far outweigh the costs.