danger in granting roles in model and tempdb

Question

danger in granting roles in model and tempdb

pixye.sb

SSCommitted

Points: 1520
More actions
January 21, 2009 at 6:48 am

#196309

I have a problem with security.
We have a cluster on which reside all of our suppliers databases. Each of them has a user dbowner of its database. A supplier would like to create a new database for a new application and has the following needs:
The sslogin shall be mapped to following roles in model and tempdb systemdabases:
db_databasereader
db_databasewriter
db_ddladmin
else the application could not run properly.
Is it dangerous for the global instance and for the other databases? Could there be any problems if they mistake something?
Thanks in advance for your suggestions,
sb
😉

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Why would it need permissions in tempDB? Temp table usage doesn't require any permissions in tempDB, so unless there're doing something like creating permanent tables in tempDB (which the permissions on model suggest they are), there's no point

Permissions on model means that they will have those permissions, by default, on all databases created on that instance (including tempDB on every start)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pixye.sb SSCommitted Points: 1520 More actions · Answer 2

you mean that a user having the role db_datawriter on msdb has by default the same privilege on all databases in that instance?

(I'm not so expert, so sorry if I ask obvious questions...)

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

I would really question needing permissions in model and tempDB. As Gail pointed out, you would only need permissions in TempDB if you were creating permanent tables there - which is not typically a good design decision.

Needing permissions in model suggests that their application will be creating new databases automatically. This is not completely uncommon, but in these cases, it is usually best to have the application run on it's own instance of SQL server because their design puts other databases in the instance in danger. It would also suggest that they have at least one login that has server-level permissions allowing database creation - again dangerous with other applications using the instance.

I would be a bit nervous of using a software vendor that has made these requests.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

pixye.sb (1/21/2009)
you mean that a user having the role db_datawriter on msdb has by default the same privilege on all databases in that instance?

No. A user with the db_datawriter on model will have the db_datawritter permission on any new databases created afterwards

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pixye.sb SSCommitted Points: 1520 More actions · Answer 5

I completely agree, there is the risk of compromise all other databases, which are very critical.

So many thanks for your suggestions, I think we will convince the supplier to make its application work in a different way.

sb