Home / server / Questions / 32142
Accepted
SQLRockstar
Asked: 2009-06-27 04:34:33 +0800 CST

default PUBLIC settings

  • 772

does anyone know of any security risks with the default PUBLIC settings for sql 2005/8?

i ran sp_helprotect and it listed out the PUBLIC grants, and now i have an auditor telling me that i have a security risk because PUBLIC has access to system information. before i start to present my case i wanted to see if anyone has any links or information they can provide that would detail how the default settings allow for a security risk.

for the record, the auditor mentioned that these setting allow for the ability to crack passwords because they can view the password hash. i have not been able to verify that the setting allow for such an ability to see that information through the sys catalog views.

thanks in advance

sql-server sql-server-2005 sql-server-2008 security sql

2 Answers

  1. Best Answer

    In 2000, the public role has access to a lot more than in 2005 onwards - this may be the source of the auditor's issues. User/schema separation should take care of most issues, but you should create a user with minimal permissions and see what it can see. You'll want to try:

    master.sys.databases (and anything else that could give away info on what is stored in the instance) master.dbo.syslogins (which contains the password hashes - sysxlogins on 2000)

    And you also want to change all XPs in master so that the public role doesn't have execute permission on any of them. There's an article and a piece of code that will do this here.

    Note that it's NOT recommended to change the public role itself in any way.

    Hope this helps!

    PS If you get back a NULL for the password in the syslogins table, it's because the login isn't using SQL authentication. Using Windows-only is a good way to remove this risk, but you still need to have a password for the (disabled) SA account.

    • 1

  2. Here is a BOL aritcle explaining how the public role affects metadata visibilty for all users: http://msdn.microsoft.com/en-us/library/ms187113(SQL.90).aspx

    There is some stuff that typically needs to be visble to everyone or things will break (sys.databases is one of the examples, even if it's not listed in this aricle - for historical reason everyone can retrieve the list of all databases on the server but you can of course revoke this permission). Things tend to get funny when you start revoking public access to some pieces of metadata.

    To give you an example: things will break big-time if you revoke public permission to select from sys.databases. The proper way to do it is to REVOKE VIEW ANY DATABASE from public - then sys.databases will be "filtered" and you will only see the databases for which you have permissions. But it does not give you any real security as anyone can still enumerate all databases by doing SELECT DB_NAME(X) and supplying arbitrary values for X (that's a known loophole).

    And in terms of passwords, in default configuration for SQL2005 and SQL 2008, through the public role you can only see two logins via sys.syslogins: yourself and sa. And you will not see the password hash for either one. So obtaining hashes for cracking this way is not an option. And cracking those hashes requires brute-force approach anyway so it only works for weak or dictionary passwords.

    My experience with auditors is that they tend to make a fuzz about small things like public permissions but miss the big thing. YMMV.

    HTH

    • 1