dcsimg

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:

  • 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

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:

  • 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:

select count(*) as PublicPermissionCount from sysprotects P inner join sysusers U on P.Uid = U.UID inner join sysobjects O on P.ID=O.ID where P.uid=0 and o.Type<>'S' and o.Name not like 'sys%' and O.Name not like 'sync%'

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.

set quoted_identifier off

exec sp_msforeachdb @Command1="select '?' as DBName, count(*) as PublicPermissionCount from ?..sysprotects P inner join ?..sysusers U on P.Uid = U.UID inner join ?..sysobjects O on P.ID=O.ID where P.uid=0 and o.Type<>'S' and o.Name not like 'sys%' and O.Name not like 'sync%'

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!








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers