SQL Server – Desiging for security – Prelude

Amongst many IT professionals, security is seen as many things: painful, a dark art, an unnecessary hindrance.


I believe this is because it is only considered at the end of a development process, rather than at the beginning. This means that certain choices that would make implementing a well rounded security platform could potentially be missed, such as the choice of database schemas being used.

Too often, I see development being carried out under sysadmin or database owner level permissions, which end up making things “easy” for development. Permissions aren’t a problem and so everything just works. The problems that this can cause, however, are massive. All of a sudden, those permissions that allow objects to be created and dropped at will, or (and this is often a killer) cross database permissions to work, are no longer there, causing the application to stop working.


Worse still, I’ve seen accounts being used that run at domain admin level, which in turn are sysadmin within SQL and start calling xp_cmdshell to move files about using dynamic SQL.  Not only were there massive security holes into the system by such an implementation, but it also became a mammoth task assigning out all the permissions required for the business to continue due to the number of different file share locations that were being accessed by that process.

By thinking about the different levels of accounts that will be needed in our application early, we can start testing using those accounts as we develop meaning that these sorts of errors can be picked up early and catered for using security features such as self signed certificates.  Not only that, but we can start building up the granular permissions that each role will need, and even go so far as actually creating database roles to cover those permission sets.  We can also start grouping our tables, views and procedures into schemas in a manner that will allow easier management of permissions, as opposed to just using the stock dbo schema.

As with anything, I feel that if we properly design a solution, then it will be far more effective than a reactive, enforced solution implemented much later.  It’s also, definitely, much easier to implement a good security solution if it’s thought about in the beginning of development, rather than right at the very end!

Over the next few weeks, I’ll look at going through some of the ways that security can be implemented alongside the holes and pitfalls that can trip the unwary developer up.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s