October 10, 2008 at 11:03 am
Hi there
I'm just wondering if anyone might have any thoughts on this. We recently had external auditors audit one of our SQL Servers (2005) where I work and they came back with a number of vulnerabilities according to the audit software they used which is a product called AppDetective.
One of the issues raised was permissions granted to the public role. In the details, they listed over 3500 instances where the public role had been granted Execute or Select permissions on sytem objects in the master database. These permissions were granted by default during the installation of SQL Server, so I wouldn't have imagined they would be a vulnerability. Below is a sample of what came back as a threat:
Database=master, Object Name=sp_MSalreadyhavegeneration, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_MSwritemergeperfcounter, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=TABLE_PRIVILEGES, Permission=SELECT, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=INFORMATION_SCHEMA
Database=master, Object Name=sp_replsetsyncstatus, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_replshowcmds, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_publishdb, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_addqueued_artinfo, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_replcounters, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_MSget_subscription_dts_info, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_password, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_MSstopdistribution_agent, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_replmonitorrefreshjob, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_MSenumpartialchangesdirect, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_MSupdate_subscriber_info, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_MSdrop_distribution_agent, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_bindsession, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Database=master, Object Name=sp_MSallocate_new_identity_range, Permission=EXECUTE, Granted By=dbo, Class=OBJECT_OR_COLUMN, Schema Name=sys
Does anyone have an idea if these do pose a risk? My boss would like me to address any of the issues raised by the report, including this one, but I'm reluctant as I'm not sure what impact it would have.
Regards
Steven
October 10, 2008 at 12:52 pm
We had an identical situation when I worked for a SOX audited company. We did end up removing those permissions with no ill effects HOWEVER, I would test that out in a non-critical environment first and make sure that you script out the permissions prior to removing them so that if you have to restore them you can do it quickly.
With that being said, I don't know that we really found any holes that these opened up. I haven't looked through your list yet to see if there was anything glaring either. It really came down to the point where I was tired of having to put in statements every time we were audited to get past these items.
....ah, the good ol' days of audit. Sure DON'T miss 'em.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 10, 2008 at 2:42 pm
Thanks David. I figured removing them wouldn't cause any issues, but still I reckon leaving them there wouldn't be a risk either. Me thinks my boss will side with the auditors tho, so I'll create said scripts to revoke & regrant if necessary!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply