September 17, 2003 at 11:20 am
Will there be any problems if I revoke user access to the Master and MSDB databases?
Background. My server is set up for a third-party application. It created the databases and unfortuately they use a very bad practice. They set up access using PUBLIC ROLE. So, the builtin dbo (which MUST remain) requires the use of PUBLIC ROLE for everything it does. That means that every user has access to everything unless I specifically DENY/REVOKE the permission.
They really should have created a ROLE for their application and given the permissions to their role and not the PUBLIC ROLE. But they didn't. And they also gave PUBLIC ROLE select permissions on the MASTER and MSDB databases.
I don't have a test-bed/development server, so I can't test revoking permissions. So, I'm asking for help here. In a normal SQL Server install, does PUBLIC Role have any 'builtin' permissions to the MASTER and MSDB databases?
(BTW-due to my constantly 'bugging' the vendor about this poor security issue, they are changing it with the next update).
-SQLBill
September 17, 2003 at 11:55 am
Yes, there are default permissions (to public) on objects in master and msdb. For example, any user (public) can execute sp_password or any user can select * from sysfiles (among other tables).
My first thought would be to create a second group for your users that aren't dbo and assign the deny permissions to them- but then I got to thinking. An explicit deny will override an inheritted select - so this is just asking for trouble (in relation to denying rights on objects in Master and Msdb).
I don't have an all encompassing answer for you - I would recommend against revoking or denying permissions to objects in master and msdb unless you know that the app or any calls/processes that the app does won't require use of those objects.
-Ken
September 17, 2003 at 11:58 am
Msdb isn't usually an issue. We remove guest here.
master is another story. there are things that are needed. you can default the users to antoher db and remove access from master, but then test to be sure all functions still work.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 17, 2003 at 12:31 pm
Thanks for the responses. I think I'm going to have to scrounge for a server and build it as a test bed. Then I'll be able to install SQL Server without this application and compare the permissions on both.
At least the vendor is finally seeing the light on this issue.
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply