December 23, 2003 at 1:30 pm
The public role seems to have default permissions to some potentially sensitive tables and procedures. I'm sure it doesn't require all of these permissions, but I'm not sure which ones to revoke.
Has any developed a standard recommendation for what permissions public should actually have?
December 23, 2003 at 2:10 pm
From Chip Andrews' (SQLSecurity.com) lockdown script:
USE msdb
REVOKE execute on sp_add_job to public
REVOKE execute on sp_add_jobstep to public
REVOKE execute on sp_add_jobserver to public
REVOKE execute on sp_start_job to public
GO
--
--Tighten permissions on web tasks table to keep malicious users from creating or altering tasks.
USE msdb
REVOKE update on mswebtasks to public
REVOKE insert on mswebtasks to public
GO
--
--Tighten permissions on DTS package connection table so that malicious users cannot affect DTS packages.
USE msdb
REVOKE select on RTblDBMProps to public
REVOKE update on RTblDBMProps to public
REVOKE insert on RTblDBMProps to public
REVOKE delete on RTblDBMProps to public
GO
--
--Tighten permissions on extended procedures that require heavy use but should not be allowed public access.
USE master
REVOKE execute on sp_runwebtask to public
REVOKE execute on sp_readwebtask to public
REVOKE execute on sp_MSSetServerProperties to public
REVOKE execute on sp_MScopyscriptfile to public
REVOKE execute on sp_MSsetalertinfo to public
REVOKE execute on xp_regread to public
REVOKE execute on xp_instance_regread to public
--Jonathan
--Jonathan
December 23, 2003 at 2:21 pm
http://www.giac.org/practical/GSEC/KBrian_Kelley_GSEC.pdf
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 23, 2003 at 2:55 pm
Thanks, bkelley. That is a very well thought out and written white paper. However, it confirms what I already believed; the waters here are fairly murky. It leaves me feeling that I ought to do something, but I'm not sure what.
I'm presently consulting for a company that has a couple of dozen SQL Servers. Many of them haven't been maintained well, and there's little documentation on what each one is actually doing. We're trying to fix them up. Since I don't know what might break when I tighten security, I was hoping to raise the bar a little higher while minimizing fallout.
Thanks also to AdiCohn for your perspective and to Jonathan for your script. I'd like more of the same. What are others doing? Anything? Have others run Jonathan's script or similar scripts in your enviornments?
December 23, 2003 at 3:04 pm
If you are consulting, bring 'em up to SP3/3a and verify you can turn cross-database ownership chaining off. One of the probs with adjusting the public permissions is it leaves the SQL Server in a non-supported state. Yukon deals with this a whole lot better.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply