April 6, 2005 at 9:23 am
I’m working on tightening security on our SQL Server and was wondering if anyone knows why the PUBLIC role has SELECT permissions on most of the ‘sys’ tables in the database. I’m assuming this is some kind of default when the database is created, because it appears that all of our databases have explicitly granted permissions to PUBLIC on the ‘sys’ (& dtproperties) tables.
NOW, can anyone tell me if revoking these permissions will cause problems? In general, we are not relying on the PUBLIC role to assign permissions to users, and definitely are moving towards locking down the PUBLIC role.
Thanks.
April 6, 2005 at 9:40 am
I wrote about this for my GSEC practical. You can find it in the SANS Reading Room:
SQL Server 2000: Permissions on System Tables Granted to Logins Due to the Public Role
K. Brian Kelley
@kbriankelley
April 7, 2005 at 7:02 am
Thanks, this looks like the information I'm looking for and the ownership chain information may be helpful, too!
April 7, 2005 at 7:40 am
I have successfully removed public rights from most system tables and procs , however, and it's a big however, your applications may stop working, especially 3rd party apps < grin >
Setting a dsn scans the sysdatabases table for example - support people can get unhappy if they can't see this list although you can set up a dsn without access to this table.
Many 3rd party apps do very wierd things at connection, usually the more respected and established the worse it is.
I have an app ( 3rd party established large ) that reads from the table and index structures on every login - it builds an entire schema on the client workstation.
My understanding is that once upon a time ( dbase ) you could only search on an indexed column ( field ) so it was very important for the client to know this information - seems we can never loose the legacy.
I can say that home produced software usually doesn't need this level of access - I suggest the extensive use of profiler to establish what needs what.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 7, 2005 at 7:56 am
More good information....
From what I've read so far, I'm guessing my biggest headache will be 3rd party software, which on my server is mostly Accounting related and I also support these packages. So I guess that means I'm just creating headaches for myself!
As far as DSNs, anyone who tries to set up a connection to my server without knowing the database name will just have to contact my department.
Profiler is a good idea to see what tables may be accessed.
April 7, 2005 at 8:10 am
Biggest headache may be support from MS... remember, altering the permissions will result in a non-supported state. Whatever you remove, remember to have scripted the mechanisms to reapply the permissions.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply