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 its membership in Public (which happens automatically) and AppUsers has select, insert, and delete due to its 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.
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!
set quoted_identifier offexec 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%'
create Proc usp_DBA_MovePublicToEveryone @EveryoneRoleName varchar(100) as
--2/16/01 law
--Creates an 'EVERYONE' role with the same permissions that are assigned to Public,
--then adds all current users in the database to the EVERYONE role, then revokes all
--permissions from Public
Declare @RevokeSQL varchar(1000)
Declare @GrantSQL varchar(1000)
set nocount on
--default to Everyone
if @EveryoneRoleName is null or rtrim(@EveryoneRoleName)=''
set @EveryoneRoleName='EVERYONE'
--if the role already exists, warn user and exit. Otherwise we create it!
if exists (select * from dbo.sysusers where name = @EveryoneRoleName and uid > 16399)
begin
print 'Role ' + @EveryoneRoleName + ' already exists. You should remove this role or select a different name for your ''Everyone'' role'
return
end
else
EXEC sp_addrole @EveryoneRoleName
--now we need to add all existing users to the everyone role
DECLARE cur CURSOR
READ_ONLY
FOR Select name from sysusers where hasdbaccess=1 and name<>'dbo'
DECLARE @UserName as SysName
OPEN cur
FETCH NEXT FROM cur INTO @UserName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
exec sp_addrolemember @EveryoneRoleName, @UserName
END
FETCH NEXT FROM cur INTO @UserName
END
CLOSE cur
DEALLOCATE cur
--next we need to get a list of all the permissions currently granted to Public.
--Im using a temp table just to make things clear, you could use it directly in
--the cursor that follows
select P.ID, U.Name as UserName, o.name as ObjectName,
case P.ProtectType
when 204 then 'GRANT_W_GRANT'
when 205 then 'GRANT'
when 206 then 'REVOKE'
end as ProtectType,
case p.action
when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE'
end as PermissionGranted
into #Temp
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 'sync%'
order by UserName, ObjectName
--now we apply the permissions that are in the #Temp table to the new EveryoneRole
--and at the same time revoke those same permissions from Public
