Developer Product Briefs

Secure Your Data With SQL Server

Learn how defining levels of database access, for both nonsensitive and sensitive data, can help protect you from system failure.

• By Alex Papadimoulis
• 05/01/2006

Secure Your Data With SQL Server
Learn how defining levels of database access, for both nonsensitive and sensitive data, can help protect you from system failure.
by Alex Papadimoulis

May 1, 2006

In most installations, SQL Server acts as the bottom layer in a standard application stack (user interface, middle tier, and database). Users rarely know what type of database they're using, let alone access and use it directly. For this reason, database security configuration is minimal, and often lax. Sometimes only an easy-to-guess username and password sit between the database and the middle tier.

At first, a lack of security doesn't seem like a problem. If your database is behind a firewall and your users and developers are trusted, you'd think no one would maliciously delete your database. But it takes only one disgruntled (and dedicated) employee to render the system nearly useless by making slow, eroding changes to your business data. And in addition to malicious changes, you have to worry about accidental changes as well. Fortunately, you can take some simple security measures to greatly prevent accidental and malicious damage.

An application database should define at least three different levels of access:

• User access executes only a set of known queries defined within your application.
• Developer access provides read-only access to nonsensitive data for production support purposes.
• Administrator access offers full access to all database objects and data.

The first step in defining these access levels is setting up your security principals (for example, entities that utilize database resources). SQL Server offers a number of mechanisms for database access; their appropriateness depends on your application environment:

• Integrated, User: Windows logons are granted access on an individual basis.
• Integrated, Group: A Windows group is granted access, and only current members of the group are granted access.
• SQL User, Individual: For every user accessing your database, a SQL Server logon is created and granted appropriate access.
• SQL User, Single: A single SQL Server logon is created and granted access.

Once you've set up your principals, you must enforce each access level. The easiest access level to establish is the administrator level. Simply add the principal to the db_owner role with this command:

USE <database_name>
EXEC sp_addrolemember 'db_owner', '<principal_name>'

As long as you don't have sensitive data in your database, the developer access level is just as easy to set up: Simply add the principal to the db_datareader role. Sensitive data, such as bank account numbers, should be assessed on a case-by-case basis. You can secure it with one of these techniques:

• Table-level security prevents SELECT access to a specific table with the DENY statement.
• Column-level security is similar to table-level security; it uses the DENY statement to restrict SELECT access on a column.
• Encryption: Data can be encrypted at the application level or, in SQL Server 2005, with built-in cryptography functions.

Implementing the user access level requires a slightly different approach: stored procedures. In stored procedures, the user principal requires an EXECUTE privilege for each procedure run by the application. However, if your application has already been developed with inline SQL, you have only one alternative to giving full access. You must grant SELECT, INSERT, UPDATE, and DELETE privileges on tables and columns based on the queries run by the application.

About the Author
Alex Papadimoulis lives in Berea, Ohio. The principal member of Inedo, LLC, he uses his 10 years of IT experience to bring custom software solutions to small- and mid-sized businesses and to help other software development organizations utilize best practices in their products. On the Internet, Alex can usually be found answering questions in various newsgroups and posting some rather interesting real-life examples of how not to program on his Web site TheDailyWTF.com. You can contact Alex directly via email at [email protected].