SQL Permissions: The Public Role
February 18, 2001
In this article I'd like to discuss both how to use the Public Role and how using Public may cause you more problems than it's worth by describing some common security scenarios.
The Public role is the equivalent of the NT Everyone or Authenticated Users group. Any user added to the database is automatically added to that role - including the guest login if you choose to allow it. This means that any permissions you grant to Public automatically extend to all users. Plus, you cannot drop the Public role. Public exists to make life easy for you - giving you an easy way to quickly add or remove permissions to all users regardless of role membership.
Let's start with a very simple scenario - you have a database where everyone in your company needs select, insert, and delete permissions. You can accomplish this very quickly by granting the permissions to Public and then adding the NT Everyone group and making it a member of Public. Quick and easy.
Then management gives you the first change. You need to give the "Trainees" group read only access, the "AppUsers" group select, insert, and delete permissions, and no access to anyone else. How can you do that? Here is one way:
At this point Trainees has select only because of it's membership in Public (which happens automatically) and AppUsers has select, insert, and delete due to it's membership in both the AppUser role and Public. Anyone who is not a member of Trainees or AppUsers has no access.
A few more weeks go by, management let's you know that there will be a contractor on site for several weeks who will have a domain account for email and will also need select only access to two of the many tables in your database. The contractor will not be a member of Trainees or AppUsers. What do you do? One possibility is to do this:
Hopefully you see that the problem we have is that because Public has more than the minimum permissions we want to give the contractor, we have to use DENY to take some away. Nothing wrong with that technique. If you don't want to use DENY, then you have to rethink your permissions. If you start from scratch, here is one way you might do it:
The advantage to this solution is that you permissions are very clear and easy to follow.still, it is useful to have a base set of permissions. One idea that I'm currently using is to create an "Everyone" role that serves much the same purpose as Public. By granting your base permissions to Everyone instead of Public, new users no longer acquire permissions just because they were added to the database. It forces you to explicitly grant them access by adding them them to one or more roles. If you apply this idea to the current scenario, you would have this:
The possible advantage here of course is that if you end up creating more roles, you're betting that most of them will need at least select access to all tables, so you can leverage the Everyone role. On the other hand, if your design changes so that you want to remove access to one or two tables, you can modify Everyone and the change affects all users who are a member of Everyone (but not the contractor!).
Before you can really decide if you want to pursue this course, you probably want to take a look at what permissions Public has right now. You can do it in Enterprise Manager, or from Query Analyzer by running the following query:
If you're not familiar with the system tables sysprotects, sysobjects, and syspermissions you might want to take a quick look at them just to have a better idea of how they work together. This query relies on the Public role always having a UID of 0 in the sysprotects table. We don't want to change the permissions on the system tables, so we use the Type field in sysobjects to filter out our system tables. In my testing I found that the sync tables SQL creates for replication are given permissions in Public, so we will also skip any table that begins with sync.
You'll probably want to run this query on each database. This is an ideal time to use the sp_msforeachdb stored procedure to save writing a cursor. If you haven't used it before, you might want to take a look at this article by Brian Knight.
Once you've identified databases that have permissions assigned to Public, here are the steps you need to take if you want to give the "Everyone" role a try:
Unfortunately SQL doesn't offer the ability to easily copy permissions from one role to another. My first solution was to script out the permissions, use search and replace to change Public to Everyone, then run the script again. Not bad, but clunky. Or you can click your way through the permissions dialog in Enterprise Manager. Or you can download the script I wrote!
Ultimately the problem with assigning users to multiple roles is that of calculating and managing effective permissions, a subject I'll address in an upcoming article. As always, please email me if you have comments or questions!