I'm advising a friend who manages a SQL 2k5 box that has several users who have dbo access to multiple databases. The problem is:
- These users haven't had their passwords changed for some months,
- These users put their IDs into applications and the applications run as DBO.
So - aside from the obvious dbo rights to add/update/delete tables and procs, what dangers can I cite for a malicious user having dbo to a SQL 2005 database?
I'd like to provide specific scenarios that pose harm to the database and other users. Could a dbo change file allocations on the server? Could a DBO affect other resources not directly connected to that database?
As a clarification, this was SQL Server 2005, and by default xp_cmdShell wasn't authorized for DBO users. I'm still wondering if there are risks beyond the obvious CRUD. What can someone do with DBO?
yes. dbo has rights to do whatever it wants on the database. even run xp_cmdshell. and once you can run xp_cmdshell you can do pretty much anything on the system.
this is all possible provided dbo has sysadmin rights which by default it has.
I realize that this is a 5 year old post but it was never answered correctly and there's some really bad information that's been posted.
First, let's see what Books Online has to say about the "DBO" role (priv). The emphasis is mine.
Members of the db_owner fixed database role can perform all
configuration and maintenance activities on the database.
To summarize, that means that a person with "DBO" privs can do just about anything they want to any database where they have that priv. As of 2005, that also means they can drop the database.
Also notice that it doesn't say a bloody thing about being able to control the server. As far back as I can remember, you needed either "SA" or (more recently) "Control Server" privs to execute xp_CmdShell. DBO does not come with either priv so it cannot run xp_CmdShell or a great many other things that begin with "xp_" or even "sp_".
Shifting gears and as it seems the OP is already a bit aware of, DBO privs are considered to be "elevated privs". I would advise that you never grant the DBO priv to an application (or other front end) and I would only grant it to people that are supposed to be responsible administrators of the database. Those people need to be chosen as wisely as those people you would choose to be DBAs.
To answer the other questions concerning CRUD... in most cases where CRUD is performed by an ORM, the most elevated privs needed are db_DataReader and db_DataWriter. To be honest, I prefer to not even allow logins to have those privs. I know every front end developer in the world is going to scream at me but the truth is that even CRUD should be done by stored procedure to prevent an attacker from getting in with db_DataWriter which allows someone to delete from tables. So far as I'm concerned, apps (and other front ends) should have only unmodified PUBLIC privs and the privs to execute certain stored procedures... period.
