Balancing Security Concerns.


July 12, 2018 by Kenneth Fisher

I have a presentation I do every now again on security basics that I’m actually quite proud of. One of the sections at the end is on best practices, where I mention a few important security best practices broken down as follows:

  • Least Maintenance
  • Least Least Surface Area
  • Least Privilages

Probably my favorite part of the whole thing is the first and last best practice I mention.

  • Don’t make permissions more granular than you have to. (Don’t grant permissions at a table level if granting them at a Schema or even better the database level will work)
  • Grant permissions at the lowest level possible. (Don’t grant permissions at a database level if granting it at a Schema or even better an Object level will work.)

Then I sit and wait a few seconds (depending on how much time I have left) and watch it slowly dawn on people what I’ve said. How does that work? How can you have to completely opposing best practices?

Because security is a balance. On the one hand, you have to protect the data. That’s probably our most important task. On the other hand, our users have to be able to do their jobs. So we have to grant them sufficient permissions to do so. And on the other hand, we have to be able to do our jobs. If we are spending all our time dealing with security we aren’t going to be doing anything else. (Yes, that’s three hands, so sue me.) Maintainability is a must.

In other words it depends.

  • If you have hundreds of servers and thousands of databases with active development on many of them, you probably can’t manage security at an individual object level.
  • If you have a database with highly secure data, say some tables have payroll information or HIPAA related data. You need to be more careful here and might need to grant permissions at an individual object level.

Really, in the end, it’s going to come down to your situation. You need to make your security as tight as possible, while still allowing your users/developers/customers etc to still do their job, and still manage security over time. Of course, the more you know about security the easier this gets but you still need to make sure you pay attention.

2 thoughts on “Balancing Security Concerns.

  1. Phil says:

    I have a related quandary at the moment in regards to granting View Server State in production to developers so that they can run things like sp_whoisactive and access server dmvs.

    On the one hand it would be great for them to see issues as they are happening i.e. tsql related to blocking. On the other hand they will be able to view lots of other stuff as well.

    So I would be curious to hear your views on this.

Leave a Reply to Phil Cancel reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: