SQL Permissions: The Public Role

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:

• Remove the Everyone group as a database user
• Revoke insert and delete permissions from Public
• Add the AppUsers group as a database user
• Add the Trainees group as a database user
• Create an AppUser Role that has insert, delete permissions and add the
  AppUsers group to it

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:

• Add the contractor’s login to the database as a user
• Create a Contractor role that has DENY on select for all tables except the
  two he needs
• Add the contractor login to the Contractor role

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:

• Add the AppUsers group as a database user
• Add the Trainees group as a database user
• Add the Contractor as a database user
• Create an AppUser Role that has select, insert, delete permissions and add
  the AppUsers group to it
• Create a Trainees Role that has select permissions and add the Trainees
  Group to it
• Create a Contractor Role that has select permissions on the two tables
  needed, add the Contractor to it

• Add the AppUsers group as a database user
• Add the Trainees group as a database user
• Add the Contractor as a database user
• Create an AppUser Role that has insert, delete permissions and add the
  AppUsers group to it
• Create a Trainees Role and add the Trainees Group to it
• Create a Contractor Role that has select permissions on the two tables
  needed, add the Contractor to it
• Create an Everyone Role that has select permissions on all tables. Add the
  AppUsers and Trainees Groups to it

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:

• Create an "Everyone" role and assign it the same permissions
  currently granted to Public.
• Add all users to the Everyone role.
• Remove all the permissions from Public.

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!