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.