none
What's so special about public?

    Question

  • Could someone recommend a book or other resource to help me understand this message:

     

        Cannot use the special principal 'public'.

     

    BOL doesn’t index the term “special principal” or mention any restrictions on how public can be used.   

     

    I understand the literal message: 'public' cannot be used in the way I tried.  I'd like to know the properties of 'public' that make it "special".  I want to understand how specialness interacts with the security model, so that I can predict when public can and cannot be used. 

     

    2008R2 is rife with things like this.  Another example: as db_securityadmin, I cannot grant my own account permissions.  But I can create a role, put myself in it, and grant permissions to that role.  

     

    The actual command was:

    1> sp_addrolemember db_datareader, 'public'
    2> go
    Msg 15405, Level 11, State 1, Server [name], Procedure sp_addrolemember, Line 84
    Cannot use the special principal 'public'.

    How would you grant everyone read access without enumerating the usernames and/or roles? 

     

    --jkl

    Monday, April 25, 2011 6:36 PM

Answers

  • The docs call it a role, as you do, even if it's listed under "Principals (Database Engine)".  sp_addrolemember rejected 'public' as a "special principal", a term I do not find described in BOL. 
    I can grant things to public.  I can put roles in roles, even if I'm advised not to.  Why can't I add 'public' to a role?  And how else might I put everyone in db_datareader, without enumerating every user?   
    <...snip...>

    A database role (e.g. public) is one type of Principal in SQL Server.
    You can't add public to a role because all other users (aka DB level principals) are already part of public. It seems odd, but it really isn't. I'll explain more later.
    In your specific case, DB_Datareader has the effective permissions SELECT on the database and VIEW ANY DB at the server level. If you want to provide that to public, you just need to grant the same permissions manually to public. For example
    USE mydatabase
    GO
    GRANT SELECT TO public
    GO
    
    The info about Public is scattered around BOL; perhaps it may benefit from some tweaks. Please log a suggestion on connect.microsoft.com/sql so the owners will have it on their radar. For now, you can treat public as
    • a special role that can't have other principals (roles or users) assigned to it since they are all already part of public
    • the default permissions bucket for all users in the DB
    • can't be deleted/dropped
    • the base or ground which all other principals sit on top of
    Think of public as the superset, the "whole" if you will. Everything else are subsets built on top of public, such as db_datareader. As such, trying to add Public to DB_DataReader or any other role is kinda like trying to add canine to golden retriever. I.E. adding the superset to one of its subsets.
    You can't add the superset to one of its subsets. You don't need to add the subset to its superset; it's already a part of the superset. You can, however, redefine the boundaries and definitions of both (e.g. change permissions for public). Permissions are attributes that enable specific actions to the whole or to a subset. For instance, subset Foo that has attribute Bar so it can perform some action(s). You can also do all kinds of interesting things between subsets though it's generally not a good idea from a security standpoint.
    Btw, if you felt being pointed to BOL as condescending, we don't mean to be. It's done primarily because:
    1. There may be a lot of content to cover so if it already exists and we don't want to put up a really long post
    2. We want to know if the content is serving its intended purpose; if not, we want to know what to fix

     


    No great genius has ever existed without some touch of madness. - Aristotle
    • Marked as answer by jklowden Wednesday, April 27, 2011 5:49 PM
    Tuesday, April 26, 2011 10:48 PM

All replies

  • Public is a special server role that serves as the default permissions bucket if no other permission has been granted on a securable. You don't have to add any other principal to public since all logins are already members.

    BOL documents server roles in detail. Some specific sections to get you started:

    Fixed server roles

    Fixed server roles permissions

    Fixed database roles

    Fixed database roles permissions

     


    No great genius has ever existed without some touch of madness. - Aristotle
    Monday, April 25, 2011 10:46 PM
  • http://msdn.microsoft.com/en-us/library/ms181127.aspx

    The docs call it a role, as you do, even if it's listed under "Principals (Database Engine)".  sp_addrolemember rejected 'public' as a "special principal", a term I do not find described in BOL. 

    I can grant things to public.  I can put roles in roles, even if I'm advised not to.  Why can't I add 'public' to a role?  And how else might I put everyone in db_datareader, without enumerating every user?   

    I can't, it seems.  I got close, though, which I'll post here for posterity:

    1> grant select on schema::dbo to public
    2> go
    1>
    

    I don't like playing whack-a-mole with my server, issuing logical commands and encountering arbitrary limitations.  I'd like to understand the theory, if there is one, so that I can predict how the system will behave.  BOL is inadequate to that purpose.  Can you recommend a resource?

    --jkl

    Tuesday, April 26, 2011 2:10 PM
  • You are running into these issues because public is a different type of role that all the others. That is, you can put someone into the db_datareader role and when a user tries to read something, the process checks for that role membership. But public is always there, and everyone is part of public. You can't add or remove people from public. And the Database Engine doesn't have to check to see if you are a member of public -- everyone is. So public is just different that all the others. Sorry it's confusing.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, April 26, 2011 3:40 PM
  • Sorry it's confusing.

    Yes, Rick, it's too bad it's confusing.  But I'm not confused. 

    I came to social.msdn very reluctantly.  I don't know its reputation within Microsoft, but where I come from the S/N ratio is so close to zero that we use -site:social.msdn.microsoft.com in our searches.  This thread is a fine example of why. 

    I asked some specific questions that I won't repeat a third time.  The only replies I've received were from Microsoft employees who, though polite (if slightly condescending), didn't answer my questions.  Both pointed out I can't add anyone to public, which of course I plainly didn't try to do or ask to do. 

    Maybe I should delete these lines from sp_addrolemember?  I wonder what that would do? 

    	--cannot change membership of public
    	if @rolename = 'public'
    	begin
    		raiserror(15081, -1,-1)
    		return(1)
    	end
    
    

    'public' isn't "just" different.  It's different in particular ways.  I can't think of any technical or logical reason for the limitation I bumped into, not least because it's undocumented.  I could shrug; I often do.  This time I thought I'd try to find a real explanation.  Why, I don't know.  On the evidence, there is none. 

    Tuesday, April 26, 2011 6:49 PM
  • The docs call it a role, as you do, even if it's listed under "Principals (Database Engine)".  sp_addrolemember rejected 'public' as a "special principal", a term I do not find described in BOL. 
    I can grant things to public.  I can put roles in roles, even if I'm advised not to.  Why can't I add 'public' to a role?  And how else might I put everyone in db_datareader, without enumerating every user?   
    <...snip...>

    A database role (e.g. public) is one type of Principal in SQL Server.
    You can't add public to a role because all other users (aka DB level principals) are already part of public. It seems odd, but it really isn't. I'll explain more later.
    In your specific case, DB_Datareader has the effective permissions SELECT on the database and VIEW ANY DB at the server level. If you want to provide that to public, you just need to grant the same permissions manually to public. For example
    USE mydatabase
    GO
    GRANT SELECT TO public
    GO
    
    The info about Public is scattered around BOL; perhaps it may benefit from some tweaks. Please log a suggestion on connect.microsoft.com/sql so the owners will have it on their radar. For now, you can treat public as
    • a special role that can't have other principals (roles or users) assigned to it since they are all already part of public
    • the default permissions bucket for all users in the DB
    • can't be deleted/dropped
    • the base or ground which all other principals sit on top of
    Think of public as the superset, the "whole" if you will. Everything else are subsets built on top of public, such as db_datareader. As such, trying to add Public to DB_DataReader or any other role is kinda like trying to add canine to golden retriever. I.E. adding the superset to one of its subsets.
    You can't add the superset to one of its subsets. You don't need to add the subset to its superset; it's already a part of the superset. You can, however, redefine the boundaries and definitions of both (e.g. change permissions for public). Permissions are attributes that enable specific actions to the whole or to a subset. For instance, subset Foo that has attribute Bar so it can perform some action(s). You can also do all kinds of interesting things between subsets though it's generally not a good idea from a security standpoint.
    Btw, if you felt being pointed to BOL as condescending, we don't mean to be. It's done primarily because:
    1. There may be a lot of content to cover so if it already exists and we don't want to put up a really long post
    2. We want to know if the content is serving its intended purpose; if not, we want to know what to fix

     


    No great genius has ever existed without some touch of madness. - Aristotle
    • Marked as answer by jklowden Wednesday, April 27, 2011 5:49 PM
    Tuesday, April 26, 2011 10:48 PM
  • Dawns the light.  Thanks, Joe, I think I get it. 

    The server won't permit roles to contain other roles if it's defined as superset or the same set. 

    1> create role foo
    2> go
    1> create role bar
    2> go
    1> sp_addrolemember foo, bar
    2> go
    1> sp_addrolemember bar, foo
    2> go
    Msg 15413, Level 11, State 1, Server MPQUANTSQL08, Procedure sp_addrolemember, Line 92
    Cannot make a role a member of itself.
    

     

    I thought db_datareader was magical but, from what you say, I could duplicate it myself:
     
    1> create role datareader
    2> go
    1> grant select to datareader
    2> go 

    meaning, of course, that db_datareader exists only as a convenience and as a modicum of standardization across servers.  Lesson: use GRANT unless what you're doing fits neatly in one of the fixed roles.
      
    'public' is defined as the set of all users, ergo 'public' cannot be placed in any role.  Case closed.
    GRANT SELECT TO public

     
    I didn't know that was valid!  (It's obvious in hindsight, though.  I often grant other statement permissions to roles, usually to create objects.)
    Regarding being referred to BOL, I'm absolutely sure no condescension was intended.  I know from having read threads here before that many questions can be answered by a close reading of the documentation (or of Codd or Date) and in general you do the person a service by putting him in touch with the relevant parts of the manual.  I tried to phrase my question such that it would be clear I'd done my homework, even if I'd overlooked something, and that I was looking for information about intention more than help with one particular problem.  When none of my questions were answered, and I was referred to pages I'd read many times, I hope you'll forgive me for thinking I might be wasting my time.
     
    Regarding what to fix, I'll offer up an opinion in case you want to discuss it.  The BNF grammar in BOL is generally very good.  I find I'm able to do most anything just by unpacking the recursive defintitions.  But on GRANT, they decided to punt: "For a list of the permissions, see the Remarks".  It's difficult -- I would say impossible -- to get an overall view of the syntax.  When will there be an "ANY" qualifier, for example?  What subsumes what else?  (BOL says "implies" but I'm not sure that's the right word, really.)
     
    The GRANT taxonomy is unhelpful too, speaking as someone who refers to it frequently.  It should follow the grammar: it should be organized about what can be granted, not the objects on which permissions be granted.  I cannot look up "grant control" or "grant alter"; I would like to be able to look up GRANT X and see the object types on which X can be granted, and the superiors and inferiors of X (the broader and narrower alternatives).  The GRANT DATABASE page tries to communicate this, but I had to know e.g. that GRANT SELECT would be on the GRANT DATABASE page.
     
    As a grantor, I'm not interested in all the things that can be granted on a specific object type, or all the statement privileges.  I'm interested in possibilties and scope: what kinds of privilege might I grant (to make something possible) and how narrowly can I grant it?
     
    Finally, I'd like to see all T-SQL permutations reduced to a reference table, say, sys.tsql.  Then I could select from that table, looking for the syntax based however I'm thinking about the problem.  I bet the folks writing your documentation would appreciate such a table, too.
     
    I know, I could send this to connect.  Maybe I will.  But you said you wanted to know how BOL doesn't work.  Now you have one man's opinion.  :-)
     
    --jkl
    • Edited by jklowden Wednesday, April 27, 2011 8:05 PM formatting
    Wednesday, April 27, 2011 5:19 PM
  • I have created a chart that lists all the SQL Server permissions. This is still in development so it's not publically posted yet, but I'm happy to share it with you (jklowden) or anyone else that is working on understanding the Database Engine permission system. With all 214 permissions (in SQL Server Denali) presented graphically, it's a bit daunting. E-Mail me at Rick.Byham@Microsoft.com if you want a copy of the draft. I'm happy to get the feedback.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, April 28, 2011 4:03 PM
  • So in SQL 2008

    if databases are being dropped and created on a SQL 2008 server

    it is secure enough not to want ppl with sysadmin access

    but there is a bunch of ppl that need full select access to all the databases on it

    so I want to have a server role with SELECT access to all databases without do anything as I drop and restore databases

    the answer above is to manually give public select access

    it does not achieve giving public select access to all databases on a server

    the only answer I can see is get SQL 2012 and define a custom server role?

    Tuesday, February 26, 2013 5:18 AM