Denying access to both master and msdb databases
-
Hello everyone,
This might sound like a dumb question but I just want to get everyone's opinion on this. When I initially setup SQL Server I noticed that both the master and the msdb database grant public access to all tables, views and stored procedures.
Is this a good idea ? I really don't think users should have access to the msdb database as this will aloow them to run dts packages and create dts packages. Most of my DTS packages run as Administrator. If users have this right then they can have all sorts of fun.
The master database has all the extended stored procedures such as xp_cmdshell, which would give users access to the allserver's drives and such.
So what I am asking is should I deny access to the public role on both the master and the msdb databases after I setup the server or would this cause a lot of harm ? Is it safe to leave things the way they are ?
Comments would be appreciated.
Regards
-
I would not worry too much about the master DB, since privileged procedures are already secured by checking explicitly for user access within the proc. So not much harm can be done by having execute access to public (atleast I have not come across any incidents) except these:
USE master
GO
REVOKE EXEC ON xp_execresultset FROM PUBLIC
REVOKE EXEC ON xp_printstatements FROM PUBLIC
REVOKE EXEC ON xp_displayparamstmt FROM PUBLIC
msdb on the other side is an open door, lock it down. I am dealing with few test / development DTS packages, jobs added by folks since every valid user in the server has rights to perform these actions. I would recommend the following to fix:
o Remove the guest account from msdb.
o Add explicitly users or applications that require access to DTS packages as users to the msdb database. DTS Packages and SQLAgent jobs should be added only by the DBA / Sysadmin.
o Revoke execute permissions on the following procedures:
USE msdb
GO
REVOKE EXEC ON sp_add_dtspackage FROM PUBLIC
REVOKE EXEC ON sp_add_job FROM PUBLIC
REVOKE EXEC ON sp_add_jobstep FROM PUBLIC
REVOKE EXEC ON sp_add_jobserver FROM PUBLIC
o These changes still permit the developers to view and execute 'their' jobs and packages.
o It is recommended that sensitive DTS packages be secured with owner and user passwords.
Regards.
-
I've written about this quite extensively in my practical for the GSEC:
http://www.giac.org/certified_professionals/practicals/gsec/3360.php
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply