The secure operation of a database server requires a cooperative effort among IT professionals responsible for...
the secure operation of the network and servers, and those developing applications that interact with the database server.
In the last Web Security Tip, we examined some of the issues surrounding administration of a SQL Server database. Today we'll look at some of the specific security issues that impact the application development process. As a security professional, it's important that you understand the basics of secure coding and educate your organization's developers to ensure that they comply with these policies. A single failure along these lines could completely undermine the security of your entire database server.
- Use database views instead of tables. Developers should create applications that interact with views (basically, predefined queries) rather than interact directly with the underlying table. This allows greater control over access to information, both at the row and column level.
- Make use of stored procedures. Developers should store their SQL code on the server and make it available to applications through the use of stored procedures. This limits the range of actions applications may perform on the database and allows for easy, centralized updates if security requirements change in the future.
- Don't embed SQL commands in application code. This goes hand-in-hand with the previous step. Developers should never include SQL commands in their applications. This creates a significant vulnerability if malicious users are able to later modify the application.
- Don't let developers have administrative power over users. Security professionals have long practiced the idea of separation of powers. It's a good idea to ensure that developers (who often control table structures, stored procedures and the like) are not able to create and/or modify user permissions. This prevents them from succumbing to the temptation of loosening access controls to make a program work "just while we're testing it." I've seen all too many cases where those "temporary" solutions have remained in place for years. Requiring developers to approach administrators for permission changes limits the likelihood of unnecessary change requests.
- Apply the principle of least privilege. In our last tip, we discussed the importance of only granting users the minimum set of permissions necessary to complete their jobs. This is also true for the administrative accounts used to execute application code. Ensure that these accounts have only the specific permissions they need to execute authorized functions.
These basic tips will help you get started down the road toward ensuring the security of your database. Encourage the developers in your organization to review these principles and think "Security First!" when writing code.
- Learn how to prevent SQL injections in this Web Security Tip, also by Mike Chapple.
- Here are more helpful tips for implementing database security and integrity.
- Read this tip and learn about SQL Server's inherent security mechanisms.
About the author
Mike Chapple, CISSP, currently serves as Chief Information Officer of the Brand Institute, a Miami-based marketing consultancy. He previously worked as an information security researcher for the U.S. National Security Agency. His publishing credits include the TICSA Training Guide from Que Publishing, the CISSP Study Guide from Sybex and the upcoming SANS GSEC Prep Guide from John Wiley. He's also the About.com Guide to Databases.