locked
Security issue? RRS feed

  • Question

  • SQL server 10.0.5826 (2008)

    I was testing a login - Domain\UsernameA. UsernameA is a test account on the domain with no domain group associations besides domain users. So UsernameA couldn't have inherited the privileges I'm describing below from any domain groups.

    I granted UsernameA to a user database (db1) with db_datareader privileges.

    I login with SQL Server Management Studio as UsernameA; I was able to traverse to System Databases, Master, System Tables and view a handful of tables and a bunch of views and SPs.

    The same goes for MSDB database

    Is that not a security issue? If so, how do I disable that?


    • Edited by Ami2013 Thursday, March 20, 2014 2:30 PM
    Thursday, March 20, 2014 2:28 PM

Answers

  • What should be granted, denied or revoked depends on what you want to achieve. That is not to clear to me in your case.

    So should VIEW ANY DATABASE be revoked from public in production systems?

    That depends a whole lot. On many production systems, only users who have sysadmin permissions have access to the server through SSMS anyway. (Because normal users connect through a middle tier, and have no direct network access to the SQL Server machine.)

    If plain users can have access through SSMS, revoking VIEW ANY DATABASE may make sense, if the database names may disclose sensitive information. That could be the case at a hosting service, for instance. However, if a user has access to a couple of databases without being the owner of them, it's unpractical when he can't see the database in the dropdown in SSMS or in Object Explorer.

    1) UsernameA can still see the system tables.

    And? The systems tables are documented in Books Online. No secrets there.

    Should the guest user be disabled on MASTER in production systems?

    Absolutely not! (And as far as I know it is not possible.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, March 20, 2014 10:27 PM

All replies

  • Its by design. The system database by default the guest user is enabled. Any login with db_datareader role can select the system objects except model database.

    But you can't view or explore any other database.

    -Prashanth


    Thursday, March 20, 2014 3:22 PM
  • I tested this on SQL Server 2012 and cannot reproduce the issue... User won't see even the database but you can select on ....In terms of system databases  you can "see" only master db without a content

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 20, 2014 3:23 PM
  • Sorry it could not see the database because I by mistake denied "view any database" permission to the user..

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 20, 2014 3:26 PM
  • So should VIEW ANY DATABASE be revoked from public in production systems?

    @Uri,

    I executed REVOKE VIEW ANY DATABASE FROM [Domain\UsernameA]

    login as UsernameA and I can still traverse to the tables.

    • Edited by Ami2013 Thursday, March 20, 2014 3:47 PM
    Thursday, March 20, 2014 3:35 PM
  • Should the guest user be disabled on MASTER in production systems? The guest user is disabled on user database (db1).
    • Edited by Ami2013 Thursday, March 20, 2014 3:37 PM
    Thursday, March 20, 2014 3:37 PM
  • But do not grant the user  any permissions , I mean db_owner database role or something?

    DENY VIEW ANY DATABASE FROM [Domain\UsernameA]


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 20, 2014 3:58 PM
  • DENY VIEW ANY DATABASE FROM [Domain\UsernameA]  gave me an error.

    I did a REVOKE VIEW ANY DATABASE FROM [Domain\UsernameA]  and removed db_datareader on db1 from the username as well.

    1) UsernameA can still see the system tables.

    2) UsernameA cannot select / view / traverse on the tree at all - unfortunately, totally defeats the goal.

    Thursday, March 20, 2014 5:18 PM
  • What should be granted, denied or revoked depends on what you want to achieve. That is not to clear to me in your case.

    So should VIEW ANY DATABASE be revoked from public in production systems?

    That depends a whole lot. On many production systems, only users who have sysadmin permissions have access to the server through SSMS anyway. (Because normal users connect through a middle tier, and have no direct network access to the SQL Server machine.)

    If plain users can have access through SSMS, revoking VIEW ANY DATABASE may make sense, if the database names may disclose sensitive information. That could be the case at a hosting service, for instance. However, if a user has access to a couple of databases without being the owner of them, it's unpractical when he can't see the database in the dropdown in SSMS or in Object Explorer.

    1) UsernameA can still see the system tables.

    And? The systems tables are documented in Books Online. No secrets there.

    Should the guest user be disabled on MASTER in production systems?

    Absolutely not! (And as far as I know it is not possible.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, March 20, 2014 10:27 PM