none
Permissions for Public Role

    Question

  • Hi,

    This is my first post in MSDN forums and I'd really be happy if someone here could help me answer the questions that's been troubling me for 2 weeks.

    In a SQL Server 2005 box, as part of security audit check, we were told to remove all the default select permission available on system tables to public role. So I managed to get hold of a script somewhere on the internet which removed the Select permission on Public from ALL system tables.

    e.g: revoke Select on sys.configurations from public

    Now users are complaining about connectivity issues. Some of the problems of are

    1. ODBC connections cannot be created (corrected by grant select on sys.charsets to public)
    2. Users cannot login (
    corrected by grant select on sys.configurations to public)
    3. Database list cannot be opened even though their ID has DBO permission on it. (corrected by grant select on sys.databases to public)

    I presume we will be having more problems in the future when replication, linked servers etc. are set up.

    Is there some way we can determine what is the ideal permission that can be set for public. Can someone recommend a solution to this so that I can speak to my security team to not disturb the default permissions? Is this really a security issue? Any help is highly appreciated. Thank you.

    Regards,
    Jayakrishna
    Thursday, November 06, 2008 3:29 AM

Answers

  • As a best practice for SQL Server 2005, you should not tinker with the default permissions assigned to the public roles. If you think there is a specific problem with any of those permissions, let Microsoft know. If someone advises you to remove them, ask them for their reasons. These permissions only let you see metadata that is public - catalog security in SQL Server 2005 prevents you from seeing metadata for objects that you do not have specific permissions on - so there's no reason to remove these permissions.

     

     Emanuel Peixoto wrote:

    Public role is only used for this account.

     

    It is not true that public roles only apply to the guest user.

     

    Also, guest is by default disabled, so you don't have to worry about it unless you took steps to enable it.

    Thursday, November 06, 2008 10:52 PM

All replies

  • Hi Jayakrishna, welcome to MSDN.

     

    I think you only need to remove guest user from database. Public role is only used for this account.I recomend you to rollback to default permission, and then, just remove guest user account.This will turn your database safe and only users with explicit permissions will have access to objects on you database.

     

     

     

     

     

     

    Thursday, November 06, 2008 9:01 PM
  • As a best practice for SQL Server 2005, you should not tinker with the default permissions assigned to the public roles. If you think there is a specific problem with any of those permissions, let Microsoft know. If someone advises you to remove them, ask them for their reasons. These permissions only let you see metadata that is public - catalog security in SQL Server 2005 prevents you from seeing metadata for objects that you do not have specific permissions on - so there's no reason to remove these permissions.

     

     Emanuel Peixoto wrote:

    Public role is only used for this account.

     

    It is not true that public roles only apply to the guest user.

     

    Also, guest is by default disabled, so you don't have to worry about it unless you took steps to enable it.

    Thursday, November 06, 2008 10:52 PM
  • Thanks a lot for all the replies. Really appreciate your effort to provide such valuable info.

    My company does not have any support with Microsoft at the moment, so I'm not sure how to contact Microsoft for help. What other options do I have?


    Regards,
    Jay
    Monday, November 10, 2008 11:03 AM
  • By posting here, you just contacted Microsoft for help. For other contact information, have a look at the sticky post at the top of this forum.

    Tuesday, November 11, 2008 8:04 PM
  • Hi I have a question about this matter. I have a login and a user both mapped to the AdventureWorks DB. This user doesn't have any privileges on any tables nor views. The only privilege he has is the execute privilege on a [dbo].[sp_xyz]. This user can query and execute DML statements through the stored procedure, which doesn't seem right at all. I think this user is accesing through the public role. My public role hasn't got any privileges on anything, even if I denied the permissions on tables or the SP, I can go through with the user. If I DENY the control server privilege to the public role,  this user can't access the tables anymore. Am I missing any particular server configuration?

    Any help is highly appreciated. Thanks in advance!!!!


    Renato2099
    Friday, January 30, 2009 8:45 PM
  • Hi Jayakrishna and Moderator,

      I am having the same issue while trying to audit SQL SERVER using SECURITY EXPRESSION  according to the DISA rules. Does anybody know about any concrete documentation from Microsoft that talks about default permissions granted to public role and what to do and not to do with them? Please reply me back ASAP if you have one.
    Thanks,
    sudip
    Friday, May 29, 2009 6:32 PM
  • You can do the following query and see what permissions have been granted to public role :

    select * from sys.database_permissions where grantee_principal_id= (select principal_id from sys.server_principals where name='public')


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, May 29, 2009 9:33 PM
  • I posted the following in another forum:

    Hi,
    When creating a new database I thought the PUBLIC role was default without any permission. For some reason I checked this and now I see the PUBLIC role automatically has SELECT rights in this database. When I add a new user, which is always a member of PUBLIC this user has SELECT rights on every usertable, can't do anything about it. I can manually change that, but I seem to recall this used to NOT be the case.
    Can any1 confirm PUBLIC indeed defaults to SELECT-rights? Is there a way to change this behaveour, besides writing my own logic for this?

    So, what I see in a newly created database a newly added user with no other permissions then PUBLIC(default) can read any usertable in that new database. Nothing much else, but still, this is not a desirable situation. Now I understand it's not a good idea to tinker with this setup...
    What can 1 do to prevent a user from reading through the PUBLIC role?

    Greetz, FreeHansje
    Friday, June 12, 2009 5:31 AM
  • No, PUBLIC do not by default have any permissions on user-created objects. Perhaps somebody tinkered with your model database?
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by FreeHansje Monday, June 15, 2009 9:28 AM
    Saturday, June 13, 2009 7:25 AM
  • Right...Never thought of that. I'll have to check, tnx.

    Greetz, FreeHansje
    Monday, June 15, 2009 9:27 AM
  • I am having the same problem with the users seeing the sys and information tables by default and I tracked this problem to the public role.  This role has select rights on all system views.  No one changed any system parameters so I have to assume that this is intentional but very annoying to the users when they link to the tables.  I checked several other SQL Servers and they are all setup the same.
    Wednesday, June 24, 2009 6:17 PM
  • Being able to select from a catalog view is not the same as viewing all catalog information. Since SQL Server 2005, catalog security restricts access to catalog entries to those principals that have some permissions on the respective objects.

    See for example: http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc

    Here's a relevant excerpt:

    "The information in the system metadata views is secured on a per-row basis. In order to be able to see system metadata for an object, a user must have some permission on the object. For example, to see metadata about the dbo.authors table, SELECT permission on the table is sufficient. This prohibits browsing the system catalog by users who do not have appropriate object access. Discovery is often the first level of prevention. There are two exceptions to this rule: sys.databases and sys.schemas are public-readable. These metadata views may be secured with the DENY verb if required."

    Hope this helps


    This post is provided "AS IS" with no warranties, and confers no rights.
    Thursday, June 25, 2009 12:33 AM
  • Hi all,

    I'm back again with more doubts.

    I agree with Laurentiu but at the same time Public has a lot of execute permissions on many stored procedures and some extended procedures too in master. This may not be a good option.

    I'm back to my original question again.. What is the optimum level of permission we can allow Public group in all DBs including master and model.

    Regards,
    Jayakrishna
    Wednesday, February 10, 2010 7:46 PM
  • Hi Jay,

     

    By default public role can only access only master , msdb and tempdb and not MODEL database.

    The optimal way of setting the public role depends on what kind of secure environment you work with , i am just pointing out couple of things we do when we remediate our SQL servers

     

    revoke view server state from public

    revoke view any database from public

    revoke execute on sp_helpsort from public

    revoke execute on sp_helptext from public

    -- and lot more of this sort


    Thanks, Leks
    Wednesday, February 10, 2010 8:22 PM
  • Laurentiu already answered that. The supported is the default. MS has cut down the permissions to a minimum, so that they at all can exist inside SQL Server. If you believe there is some problematic assigned permission, you need to be more concrete and tell us about it so we (or perhaps more specific MS) can listen and reply.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, February 11, 2010 7:00 AM
  • Hello All - I am working on SQL Server 2005 security hardening project with DISA's SQL Server 2005 security checklist as the reference...you can find this document @ http://iase.disa.mil/stigs/checklist/db_srr_checklist_sql_server_v8r1-2.pdf, to meet some the requirements in this document it is required to REVOKE SELECT permissions on sys tables from the PUBLIC role...But from this thread I understand that is not the recommended way....

    These requirement and checks are detailed in the document on page 340(DM1749) and page 359 ( DM6196)...[My apologies for telling you to read this document...but if I try to ask this question it would be same as some of the questions above and I know the answer already :) ]

    I want to understand if these requirement can be met or not? or if I am missing some thing in uderstanding the requirement...
     
    Thanks

    Cheriyan

    Thursday, February 11, 2010 8:10 AM
  • I see. Hopefully MS has somebody who has been working with this compliance and can comment on that.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, February 11, 2010 8:22 AM
  • Any update on this? We are also using the DISA checklist and have findings for the same checks that Cheriyan mention.
    Saturday, February 20, 2010 5:44 AM
  • Has there been any progress on this?  This problem really affects a lot of administrators!
    dprice
    Friday, April 23, 2010 4:15 PM
  • I have my application up and running after removing all default permissions from PUBLIC role to meet DISA requirements!!

    As expected, after removing the PUBLIC role permissions, my application started to throw permission related errors in resource db ...But I had a workaround for this by creating a database user in Master db and assigning those permissions required to this user from the public role...

    The application account that connects to the database server is mapped to the new user in the master db too...With this approach my application has no dependency on the public role now...  

    The permissions that is removed from PUBLIC role and required by the application can deffer from application to application depending upon what the application developer has put in their code...

    Thanks

    Cheriyan

     

     

     

     

     

     

    Sunday, April 25, 2010 7:39 AM