UNIX ORACLE SQL SERVER WINDOWS 技术: SqlCredit – Developing a Complete SQL Server OLTP Database Project Part IV

Database Security: It’s 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 won’t go into that detail here. For in-depth coverage, see Marcin Policht’s 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 won’t 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

Reporting

Purging

The user that can update a single card should not be the same user that can purge a month’s 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 implementation’s 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.