The weakest link in database security is the same as for most all IT security: people.
Because the weakest link is always people, we have adopted a principle called The Principle of Least Privilege to determine how we should assign security. If you’ve never heard of it, it’s a basic concept with some easy to follow rules:
- Give enough rights to do the job.
- Don’t give any more.
- Don’t give any less.
#2 is particularly relevant. It’s easy to violate this, especially when SQL Server is concerned. If you’ve ever heard, “Just give the application db_datareader,” you’ve heard a violation of this principle. The same is true with db_datawriter. Why is this a violation? Let’s ask a few basic questions.
- Does the application (report / user / etc.) need access to all tables in the database?
- Does the application (report / user / etc.) need access to all view in the database?
- Are you sure the application (report / user /etc.) needs access to the catalog views (the system objects) for the database?
Often times, the risk is understated: “Well, so-and-so is completely safe. He would never do anything wrong with the data.” Yes, that particular person may be safe. Then again, life changes may alter that person’s morals and ethics. It’s amazing how much major events like a divorce, like medical bills, like gambling debts, like a new drug habit change those things. But let’s say the person is completely safe. Are you safe? No.
The problem isn’t the person. It’s the person’s user account. If someone were able to run under the context of the user account, he or she can do whatever the account can do. Give the account too many privileges and you’ve got a problem. If someone is willing to compromise an account, they are likely going to have few qualms about swiping important data.
See, it’s relatively hard to directly attack a database server and in a trusted environment. It’s much easier to slip a trojan in to an email or mail a link which leads to the malware being installed on the user’s computer. And then, BAM!, the attacker is in as that user and the attacker now has the user’s access to the database. If you’ve given too much access, that’s bad.
Therefore, given the weakest link in database security, the best practice is to follow The Principle of Least Privilege. Don’t leave it until the end and try to retrofit. You’ll likely be forced to rush and that’s when people cut corners with db_datareader and db_datawriter. Instead, get it attacked at the start of a project / development effort. Get it right from the beginning.
