Top 13 Mistakes And Missteps In SQL Server – #7 db_owner: There is More to it than What You Think
The db_owner Database Role Has a Secret
The right solution is not frequently the easiest to implement. Avoiding hyperbole as much as possible I’ll simply say that database security is a very fine example of that adage.
It is by far easier to
… grant all users in a database membership in the db_owner database role.
… grant all users sysadmin server role membership on your SQL Server instance.
… just give everyone the password to the sa privileged login on your SQL Servers
But if you do the last two be sure to factor in the overhead of keeping your resume updated.
Why didn’t I single out use of the db_owner database role? Because it’s a great tool to help you lose your job as a Database Administrator yet it seems to be a fine way to keep your job when you’re employed by the majority of the independent software vendors (ISVs) from which every company I’ve worked for has purchased software solutions. Hyperbole? Nope. I swore that off at the beginning of this article. It’s so bad that I can tell you what the specifications around security are probably going to be for a new solution before I even start to review documentation. Why is db_owner role membership so bad? Look at the definition Microsoft provides on Books Online: Members of the db_owner fixed database role can perform all configuration and maintenance activties on the database, and drop the database. Before I begin to itemize what configuration and maintenance activities means; read that statement again and tell me what stands out.
DROP. THE. DATABASE. Perhaps you didn’t hear me. I said…
DROP
THE
DATABASE
Why in your right mind, as a developer would you allow all the users in your database to have the ability to drop said database?
Why as the Manager over the development team would you ever advocate such a position?
Why as someone tasked with QA would you ever let something like that make it through all the levels and iterations of testing and out the door to your customers?
Why, when alerted to this by customers with a vigilant Database Administration Team would you still utter the phrase “You can’t change the security settings for our product without invalidating your SLA” (or something to that effect?) Because you’re being lazy, because your mis-informed, or because you simply don’t care because “the application prevents the users from doing that.”
The Application Prevents Users from “Doing That”.
That’s great! As a DBA I’m so glad that you’re aware that granting db_owner rights give end users more rights than they need and that your application is coded in such a manner that users can’t do things like drop the database. Hacking is hard. Surely none of your customers have users that are technically savvy enough to say, go to Microsoft.com and download a free application that allows them to connect to a SQL Server database. But that’s exactly what they can do. Heck, DBAs pull their hair out all the time finding staff with installs of SQL Server Management Studio throughout their environment. If a user has db_owner, sysadmin, or any access beyond what the bare minimums of what they need to do their job then they have the capability of using that access for good or evil. Both options possibly without any intent. DBAs and Auditors care nothing about intent by the way. We freak out about access. (Not Access with a capital A, that just throws us into a fit of painful belly laughs.)
What Else Can db_owners do?
Here is a listing of all the rights afforded to that role:
- Manage all access – adding and revoking rights to objects as they see fit.
- Create objects (tables, user-defined functions, stored procedures, views and so forth.
- Drop object (see above and think happy thoughts about your users being able to drop any of those objects as they see fit.
- Run backups (good)
- Run backups incorrectly and kill your backup chain (not so good)
- Restore a database backup
Does your average user need to do this things? Of course not but they can even if your application doesn’t afford them the functionality to do so. All it takes is a search engine, a few minutes, and rights. You can control only one of those.
What is the Correct Approach?
The correct approach is called Least Privilege. Least privilege means that you only grant the rights required to the user to do their job. This means granting only SELECT rights to those objects that the user needs to query. It means granting rights to INSERT, UPDATE or DELETE on objects that the user will only need to perform those tasks against. If you find the need to provide these rights to many users then set up a custom database role and grant the rights to the role. Afterwards all you’ll need to do is run sp_addrolemember and assign any logins that need that collection of rights to that role. Sure, there is more work up front to set up the role, but after that the overhead and effort to provide proper security is the same number of keystrokes and mouse clicks as it takes to grant a user rights to the db_owner role.
If you don’t want to take the effort to do it the right way then do it the almost right way. You can at least remove the ability to drop databases, prevent a user from altering security and running backups or restores as well as prevent dropping objects by granting the db_datareader, db_datawriter, and execution on the dbo schema (or whatever schema the user needs access to for stored procedures.) This will allow the user to read and alter all data as well as execute any stored procedure. It’s a sledgehammer approach to driving a screw but it beats using the Acme nuclear-powered jackhammer also known as db_owner.
Next up…