Balancing Security Concerns.2
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.
Category: Security, SQLServerPedia Syndication | Tags: security
2 thoughts on “Balancing Security Concerns.”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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.
You do have the option of using a stored procedure with impersonation to do the actual work. i.e. For example you could change sp_whoisactive to use impersonation (add EXECUTE AS ) and then give them access to the SP rather than actually granting VIEW SERVER STATE. You’ll have to put it in a DB with TRUSTWORTHY turned on (https://sqlstudies.com/2016/08/01/using-impersonation-to-allow-a-task-without-granting-the-required-permission/) so I would recommend creating a separate database for this.