March 28, 2005 at 6:51 am
I work for a government agency and we are often subjected to security scans to find vulnerabilities in our systems. Recently we have been asked to remove permissions to the Public group in SQL for all objects in all DBs. We have already revoked permissions to Public on various objects and SPs to tighten security but I have heard that it is no wise to do a blanket revoke on permissions for the Public role as it is used for SQL internal operations and is often used by third-party and many internal applications.
The thought of removing all permissions to the Public role makes us nervous. We could replace the Public role with another user-defined role with the same permissions but that wouldn't necessarily make our security stronger. My question is this, does anyone know of any Microsoft article that warns against tampering with Public permissions? We need documentation from a Microsoft source to justify leaving our Public permissions alone. I haven't been able to find an article yet that speaks specifically about this. Thanks for any help or explanation you can offer.
Here's what we currently do on all servers:
use msdb
go
REVOKE EXECUTE ON sp_add_job FROM PUBLIC
REVOKE EXECUTE ON sp_add_jobstep FROM PUBLIC
REVOKE EXECUTE ON sp_add_jobserver FROM PUBLIC
REVOKE EXECUTE ON sp_start_job FROM PUBLIC
REVOKE ALL ON mswebtasks FROM public
REVOKE EXECUTE ON sp_get_sqlagent_properties FROM public
REVOKE EXECUTE ON sp_enum_dtspackages FROM PUBLIC
REVOKE EXECUTE ON sp_get_dtspackage FROM PUBLIC
REVOKE EXECUTE ON sp_get_sqlagent_properties from public
use master
go
REVOKE EXECUTE ON xp_execresultset FROM PUBLIC
REVOKE EXECUTE ON xp_printstatements FROM PUBLIC
REVOKE EXECUTE ON xp_displayparamstmt FROM PUBLIC
REVOKE EXECUTE ON xp_regread FROM public
REVOKE EXECUTE ON sp_runwebtask FROM public
REVOKE EXECUTE ON sp_readwebtask FROM public
REVOKE EXECUTE ON xp_readwebtask FROM public
REVOKE EXECUTE ON xp_regread FROM public
REVOKE EXECUTE ON xp_instance_regread FROM public
REVOKE EXECUTE ON xp_grantlogin FROM public
REVOKE EXECUTE ON sp_grantlogin FROM public
March 29, 2005 at 9:56 am
Here's an article from the well-respected Andy Warren that proposes a security management solution that includes removing all permissions from the PUBLIC role:
http://www.sqlservercentral.com/columnists/awarren/sqlpermissionspublicrole.asp
March 29, 2005 at 10:10 am
Short answer, no. Microsoft made minimal changes to earn a C2 compliance standard. Public was largely unaffected.
SQL Server 2000 C2 Administrator's and User's Security Guide
If you want more details, my GSEC practical looked at this issue...
SQL Server 2000: Permissions on System Tables Granted to Logins Due to the Public Role
K. Brian Kelley
@kbriankelley
March 31, 2005 at 9:50 am
Many thanks for your post. I will check out the links you provided.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply