Locking down SQL Server
Published: 18 Jul 2002 14:46 BST
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.
Have your say instantly in the Tech Update forum.
Find out what's where in the new Tech Update with our Guided Tour.
Let the editors know what you think in the Mailroom.
Previous
Did you find this article useful?
96 out of 173 people found this useful
Digg
Slashdot
Del.ici.ous
Stumble
Reddit
