February 4, 2016 at 12:59 pm
Today we found out a new developer was backing-up a database manually. This is a function the DBAs like to control so we informed the developer of our policy. No big deal. We realized the developer was a member of db_owner for the database so we understood why he had permission to execute the command. What has confused us is how the developer was able to backup the database to the database server, when the developer has no direct permissions to the backup drive. I then found this statement in BOL
"Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions."
The above statement makes me believe drive permissions get inherited from SQL Server for all backups. If that is true we are bothered by Microsoft's security choice. That means on a database server with 100 databases, if an individual has db_owner in one database, he can then backup that database to any drive (data, log, tempdb). Since we have databases larger than 1TB, that database could be backed up by a db_owner to a tempdb drive, causing the drive to fill and SQL to crash.
It's dev we're talking about in our case, so it's not a huge deal. And maybe this is nothing knew to most people, but something we've never encountered in our own experience. But why did Microsoft not follow their own security model of least privilege? Heck, with Windows 2012 I can be a local admin, but I still have to perform many functions by specifying Run As Administrator.
Can someone explain how and why this can happen?
Thanks
February 4, 2016 at 1:07 pm
DBADave (2/4/2016)
The above statement makes me believe drive permissions get inherited from SQL Server for all backups.
Not inherited from. SQL runs the backup operation to disk, using its service account. If SQL does not have permission to a folder, it can't write backups to that folder. All operations affecting the DB files behave that way iirc. The dev still has no rights to the folders or files himself.
That means on a database server with 100 databases, if an individual has db_owner in one database, he can then backup that database to any drive (data, log, tempdb).
And he can create a new file in that DB on any drive SQL has rights to and set it to 16TB if he so chooses, he can grow the existing files to max size.
That's why db_owner is considered a high-privileged account.
But why did Microsoft not follow their own security model of least privilege?
It's not a violation of least privilege. Least Privilege says that a user should have the minimum permissions necessary to do their job and no more, for eg a DBA should have only access to the DB instances they administer and nothing else. If SQL didn't work this way, any DBA would have to have to have full control to various folders on the server in order to do basic operations. That is a violation of least privilege.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2016 at 2:42 pm
Thanks. That makes sense to me as far as how he/she has the ability to place a backup on any drive, but I'm not sure I understand why this type of design was necessary. I don't believe a db_owner should be permitted to access any drive the SQL instance can access. Permissions should be limited to the drives that makeup the current database. I created the database on a specific set of drives for a reason. That said, I don't have the ability to designate a specific drive for backups so without such functionality I understand why it works the way it does.
I admit I may be missing something, and honestly I didn't give much thought to how drive permissions are handled for SQL backups prior to today since we don't give developers db_owner permission beyond DEV instances. The reason why I say this is a violation of least privilege is because SQL Server is defaulting drive access for backups to all drives it can access (via the SQL Agent Service). It's not allowing drives to be restricted. Someone doesn't need to have access to a tempdb drive to store a backup.
I do appreciate the explanation, and it got me to think a bit more, although I'm not sure if that's a good thing 🙂
Thanks
February 4, 2016 at 4:00 pm
the issue is, DB_Owner is an elevated privilege the developer does not require 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 5, 2016 at 1:56 am
DBADave (2/4/2016)
It's not allowing drives to be restricted. Someone doesn't need to have access to a tempdb drive to store a backup.
How does SQL know that's a TempDB drive? Sure, TempDB's there, but that doesn't mean that the folder also doesn't legitimately get used for other things, like ad-hoc backups.
What you're proposing (limiting folder usage to a defined purpose) would ensure that people cannot create a database or database files outside of the folder specified as the defined database location (hope you don't want to add hekaton or filestream in a different location), cannot take backups to locations other than the defined backup location (would be fun when the backup drive is full), etc.
db_owner is a high-privileged role. It's essentially sysadmin within a single database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2016 at 2:51 am
DBADave (2/4/2016)
Permissions should be limited to the drives that makeup the current database.
The only way to ensure that users with the rights to create databses or create backups stay within a predefined set of drives is to give the SQL Server service account no access to other drives.
But that limits the permissions for anything and anyone on that instance. So if you have two database stored on different drives, you cannot restrict this as long as they are in the same instance.
February 5, 2016 at 10:54 am
Gail, I think you misunderstood my statement, or most likely I did not communicate it clearly. 😀 Either way I’m not proposing folder limitations, rather tighter drive permissions. True SQL Server doesn’t know it’s a tempdb drive, but technically it does know the drive where tempdb currently resides. What I’m saying is a db_owner does not have to automatically have the keys to all drives. It is a database owner, not a database creator, and not a local admin. I just feel the internal code should have been more selective. This could have been accomplished programmatically within the engine. For example, we have the ability during installation to assign a data and log drive. That information is maintained within SQL Server (technically the registry). A similar approach could have been followed to give an administrator the ability to restrict the usage of drives. Just my opinion, nothing more.
What I’m proposing does not prevent the creation of a database. I’m referring to db_owner restrictions only, not restrictions on creating a database.
And yes if a backup drive suddenly fills you may need to temporarily use another drive. That function is typically controlled by someone with higher than dbo permission to a single database. And I stress “typically” as I know there are exceptions. However, in the typical case it becomes a concern of the administrator, who is usually a sysadmin. Not much different than a sysadmin having to restart an instance because tempdb filled and the space couldn’t be reclaimed. A member of db_owner uses tempdb and his/her process may have filled it, but he can’t restart the instance. Anyhow, like I said this was just something that came to our attention this week, and only pertains to a development server. I appreciate the feedback.
Perry I agree. Unfortunately that was us taking the easy way out several years ago, and never revisiting best practice to reduce developer permissions. Point taken 🙂
February 5, 2016 at 2:08 pm
DBADave (2/5/2016)
Either way I’m not proposing folder limitations, rather tighter drive permissions.
It would have to be permissions at a folder level. Because NTFS permissions are folder/file based with inheritance (and Windows doesn't let people play in the root of the drives any longer), but more because with mount points F:\DBs, F:\Temp and F:\Backups might be three different logical or even physical drives.
There's lots of complications with what you suggest.
If you limit a DB to only files in G:\SpecialDB2, can a sysadmin add a file to that DB and put it on the T drive? If so, can the db_owner of that database grow or shrink that file?
What happens if a DB has files on F and H drive, and the sysadmin adds a folder restriction to that DB to only allow files on F drive? Does the db go offline? Does it carry on running, but the files on H drive can not grow?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2016 at 1:23 am
DBADave (2/5/2016)
It is a database owner, not a database creator, and not a local admin.
Tis the same thing, db_owner provides the power to backup and restore the database, restore implies create database.
As Gail said, Db_owner is basically sysadmin at the database level, so avoid granting it.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 6, 2016 at 7:37 pm
Perry Whittle (2/6/2016)
DBADave (2/5/2016)
It is a database owner, not a database creator, and not a local admin.Tis the same thing, db_owner provides the power to backup and restore the database, restore implies create database.
As Gail said, Db_owner is basically sysadmin at the database level, so avoid granting it.
I believe the db_owner role can even drop the database. They can't recreate it, but they have permission to drop it.
I admit I never thought of it that way, but I like the way Gail put it - sysadmin at a database level. I think that description fits very well.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply