This is part 4 of a series. If you have not read part 3, you can find it here.
In this months installment, we will add security to the existing code and add new unit tests to prove that security.
Database Security: Its Not Just For Administrators
Too many applications are built assuming that security needs to be handled at the web tier or middle tier only, ignoring details like object permissions in the database. The other myth is that database security is just a matter of administration.
I am proposing a set of design security standards here that I hope you will scrutinize closely. If you believe they could be better, speak up!
Security Logins, GRANTs
For a web application like SqlCredit, users will not reach the database with their Windows Authentication credentials. The user will log in at the web tier, and their application credentials will accompany their session. When the application accesses the database, it will use Windows Authentication (a.k.a. integrated security). The login used by the application to access the database should have limited privileges. There are many good articles that detail those specific privileges, so I wont go into that detail here. For in-depth coverage, see Marcin Polichts two series on SQL Server security, covering both SQL Server 2000 and 2005.
It is a best practice to remove the public role from production servers, so there are no specific denies regarding the public role.
Grant Privileges to Stored Procedures and Functions, Not Tables
The security we are setting up here is for the primary users of the application: the on-line customers. We are not handling reporting capabilities or back-office work (at least for now).
Because the application will be accessing the database through our published APIs (stored procedures and functions), those are the only objects that need to have GRANT statements. There is no need for direct access to the tables by the OLTP application login.
I know that privileges are not granted automatically, but I still like to do explicit DENYs to tables and views. I have gotten so much negative feedback from our implementation teams that I have finally been convinced not to DENY SELECT on tables and views. In a perfect world, I would do that, but I wont for this code. If you control both the design and the implementation, then you may decide to lock down even SELECT.
An example of a DENY statement is here:
DENY DELETE, INSERT, REFERENCES, UPDATE ON dbo.Account TO SqlCreditAppSqlRole;
An example of a GRANT statement is here:
GRANT EXEC ON dbo.CreateAccount TO SqlCreditAppSqlRole;
You probably already understand that I can create an INSERT procedure that inserts records into the Account table, GRANT EXEC on that stored procedure, and the INSERTs will work through the stored procedure but will not work directly (ad-hoc).
An important consideration here is to balance the number of distinct groups/roles you create against the complexity of managing those credentials. For an application like SqlCredit, I would recommend setting up distinct groups/roles for the following functions:
- OLTP access
The user that can update a single card should not be the same user that can purge a months worth of old purchase data.
Feedback: What do you think about this design? I have created a new thread in the forum to discuss security, and I would appreciate any feedback. Certainly, there is more to a complete implementations security plan than this. Besides encrypting certain personal data (which I will add in a later installment), what do you think is missing from the application design?
Remember that at this point we are still supporting both SQL Server 2000 and 2005, so we cannot yet use any 2005-specific security features. However, please do comment on changes you would expect once we move this series to 2005-only security.
Windows Authentication Group and User
Before running the scripts, the Windows Authentication group needs to be set up. Since I am doing this development and writing on a laptop that is not connected to anything, I will create the group and user as local accounts. Here are the steps to set up the Windows Auth group and user:
- Set up SqlCreditAppWinAuthGp
- Set up SqlCreditWinAuthUser
- Add user SqlCreditWinAuthUser to group SqlCreditAppWinAuthGp
All the rest of the set-up is done by the scripts.
Testing Object Access Using TSqlTest
Setting up object security is not an easy task, but to be confident that it works correctly, it must be tested. (Of course, to be continually confident requires continuous integration and automated testing.)
TSqlTest allows us to create a separate script to be run as the SqlCreditWinAuthUser Windows login to verify that
- This login cannot directly affect any tables (INSERT, UPDATE, DELETE).
- This login can execute the appropriate stored procedures.
The attached test script (UnitTests\TestSecurityForAppUser.sql) is called by a batch file (Tests_LoginAsSqlCreditWinAuthUser.bat) while logged in as the application user (SqlCreditWinAuthUser).
When the script tries to INSERT directly into the Account table, error 229 is raised.
Msg 229, Level 14, State 5, Line 89 INSERT permission denied on object 'Account', database 'SqlCredit', schema 'dbo'.
Because this is the exact error expected, the test passes. Any other error number will cause the test to fail. The script then tests that the application user can execute the appropriate stored procedures.
There are now two different batch files that must be run as two different users to complete the full test suite. This requires a bit more scripting but provides confidence that the security is working properly.
For Next Time
Next time, we will fill out much more of the application: cards, vendors, and purchases.
References and Further Reading
- SQL Server 2005 Part 1 - Security (Authentication) at Database Journal
- SQL Server 2005 Security - Part 2 Authorization at Database Journal
- SQL Server 2000 Security - Part 7 - Statement and Object Permissions at Database Journal
- SQL Server 2000 Security - Part 14 - Conclusion - Pre- and Post- Installation Recommendations at Database Journal
- SQL Server Stored Procedures Administration at Database Journal
- A special thanks to Kevin Christensen at Corillian for his help with the login scripts.
Download the scripts for this article.SqlCredit - Developing a Complete SQL Server OLTP Database Project
- SQL Server 2008's Change Data Capture - Tracking the Moving Parts
- Performance Testing - SQL Server 2008 versus SQL Server 2005
- Exploring SQL Server's Index INCLUDEs
- Exploring SQL 2005's Ranking Functions - NTILE() and ROW_NUMBER()
- SqlCredit - Part 19: Exploring SQL 2005's Ranking Functions - RANK() and DENSE_RANK()
- SqlCredit, Part 18: Exploring the Performance of SQL 2005's OUTPUT Clause
- SqlCredit - Part 17: Exploring SQL 2005's OUTPUT Clause
- SqlCredit - Part 16: The Cost of Bloat
- SqlCredit - Part 15: The Cost of Distribution
- SqlCredit - Part 14: The Cost of Translation
- SqlCredit - Part 13: More on Indexed Persisted Computed Columns
- SqlCredit - Part 12: Exploring Indexed Persisted Computed Columns
- SqlCredit - Part 11: Change Tracking Using History Records
- SqlCredit - Part 10: MAC Performance and Updating SqlCredit
- SqlCredit - Part 9: Message Authentication Codes
- SqlCredit Part 8: Comparing Encrypt/DecryptByCert and Encrypt/DecryptByKey
- SqlCredit Part 7: Performance Impact of EncryptByCert and DecryptByCert
- SqlCredit Part 6: Exploring EncryptByCert and DecryptByCert
- SqlCredit - Part 5: Adding Card, Vendor, and Purchase, Plus Much Refactoring
- SqlCredit - Part 4: Schema and Procedure Security
- SqlCredit - Part 3: Schema/Proc Updates and Automated Unit Testing
- SqlCredit - Part 2: Creating the Database, Tables, CRUD Procedures
- SqlCredit - Developing a Complete SQL Server OLTP Database Project