Managing Database Driven Application Permissions In SQL Server

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
Many websites and desktop applications utilize a relational database to manage user access to certains aspects of the application or ability to view/modify data.  I've worked on many different types of applications like this and I'd like to discuss the most flexible and powerful methodology I've found for managing database driven application permissions.  Often times, we'll get the urge to try and create users, user types, and user type permissions as a way of handling this task.  The downside to such an implementation is that we are now forced to allocate specific permissions to a particular user type.  Thus, not accounting for users of a particular user type that need to be granted special permissions or temporary access to a particular permission.  In internal data warehouse applications, this often shows itself when a user is being groomed for a promotion and needs access to items not granted to their official job title.
In the end, we'll want to separate user permissions from user types altogether.  In it's place, we'll establish a relationship between specific users and specific permissions regardless of user type to give us unlimited flexiblity.  Take a moment to review the Users and UserPermission table below.  In order to grant a specific user one or more permissions, simply add the appropriate UserID/PermissionID combination to the table.  To remove the permission, just remove the record.
One of the more cumbersome tasks is keeping track of what permissions a user has once they have logged into the application.  To make this convenient, I've created a user defined function called dbo.GetUserPermissions to return back a comma delimited string of PermissionIDs for the passed in UserID.  This user defined function is utilized in the stored procedure below called dbo.UserLogin.  Notice how referencing the user defined function in our login query allows us to easily append the delimited string as a column value returned in our data set.


In a web based application, you would typically store this delimited string in cache or in session variable.  Then, whenever a user wanted access to a particular part of the site or wanted to manipulate data, you would iterate through the permission list checking to see if they have the appropriate permissions.  The C# code sample below displays a sample enum of permissions, a validation method, and an example of how to call the method in your code.  When you add permissions to an application, you typically have to apply new business rules as well as the new PermissionID in the database.  So, making it a requirement to add another enum option really doesn't complicate matters much.  Plus, utilizing an Enum makes it much easier to communicate the available permissions to user interface developers.  Intellisense on Enums is a wonderful thing...
Notice that the permission enum sample below contains a baseline of permissions and an alternative list of sub permissions.  For instance, a user might have baseline permissions to access an administrative section of the site.  This permision type is fairly static from the time the user logs in.  However, a user may have other sub permissions that vary while they are logged in.  For instance, a user may have access to 5 different sets of data.  But, the user may not be permitted to execute all of the same tasks against all 5 sets of data.  They may have only read access to some, write access to others, and administrative rights to the rest.  To accomodate this type of environment, I've broken out the validation to support sending in different sets of permissions at runtime.  If we need to validate a baseline permission, we send in that permission list.  If we need to validate a specific set of data access rights, we send in the permission list specific to that set of data.
There you have it.  Simple yet effective which is just the way I like it.  Using this methodology has gotten me out of a lot of potential jams.  The flexibility of user permissions is hardly ever discussed or requested by the requirements definition group until it is too late.  So, I've learned to plan on the need for extreme flexibility up front.
 
User and UserPermission Schema
  
 
CREATE TABLE [dbo].[Permission] (
	[PermissionID] [int] IDENTITY (1, 1) NOT NULL ,
	[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO 

CREATE TABLE [dbo].[UserPermission] (
	[UserPermissionID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NOT NULL ,
	[PermissionID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users] (
	[UserID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Pwd] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FirstName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LastName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO 

ALTER TABLE [dbo].[Users] ADD 
	CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED 
	(
		[UserID]
	) WITH  FILLFACTOR = 70  ON [PRIMARY] 
GO

 insert into Users (UserName,Pwd,FirstName,LastName) values ('user1','user','User','One')
 insert into Users (UserName,Pwd,FirstName,LastName) values ('user2','user','User','Two')
 insert into Permission (Description) values ('Permission 1')
 insert into Permission (Description) values ('Permission 2')
 insert into Permission (Description) values ('Permission 3')
 insert into UserPermission (UserID,PermissionID) values (1,1)
 insert into UserPermission (UserID,PermissionID) values (1,2)
 insert into UserPermission (UserID,PermissionID) values (1,3)
 insert into UserPermission (UserID,PermissionID) values (2,1)
 insert into UserPermission (UserID,PermissionID) values (2,2)

dbo.GetUserPermissions
 
   
CREATE FUNCTION dbo.GetUserPermissions
  (
    @UserID int
  )  
RETURNS varchar(100) AS  
BEGIN 

 declare @RowCnt int
 declare @MaxRows int
 declare @PermissionID int
 declare @String varchar(100)
 declare @Delimiter char(1)

 select @RowCnt = 1
 select @Delimiter = ','
 select @String = ''

 declare @MyPermission table
 (
    RecordID int identity,
    PermissionID int
 )

 insert into @MyPermission
 select PermissionID
   from UserPermission
   Where UserID = @UserID

 select @MaxRows=@@rowcount

 while @RowCnt  <= @MaxRows
 begin

   select @String = @String + 
                    case
                    when @RowCnt  < @MaxRows then ltrim(rtrim(cast(PermissionID as varchar(20)))) + @Delimiter
                    when @RowCnt = @MaxRows then ltrim(rtrim(cast(PermissionID as varchar(20))))
                    END
     from @MyPermission 
     where RecordID = @RowCnt 
  
     select @RowCnt = @RowCnt + 1
  
 end
  
  return @String
END
 
Typical Login Stored Procedure
 
 
CREATE PROCEDURE dbo.UserLogin
 (
    @username nvarchar(20) = NULL,
    @pwd nvarchar(10) = NULL
)
AS
  
         select Users.UserID,
                FirstName,
                LastName,
                dbo.GetUserPermissions(Users.UserID) as PermissionList
	   from Users
           where (ltrim(rtrim(username)) = @username)
              	and (ltrim(rtrim(pwd)) = @pwd)

C# Sample Code
 
  public enum PermissionTypes
  {
    Permission1 = 1,
    Permission2 = 2,
    Permission3 = 3,
    SubPermission1 = 4,
    SubPermission2 = 5,
    SubPermission3 = 6
  }

public bool ValidatePermission(string PermissionList,MyNameSpace.PermissionTypes DesiredPermission)
   {

      bool Ret = false;
      string delimStr = ",";
      char [] delimiter = delimStr.ToCharArray();

         try
         {

              if (PermissionList.Length < 1) { return Ret; }
 
              string[] Permissions = PermissionList.Split(delimiter,100);

              int Permission = (int)DesiredPermission;

              for(int i=0;i <Permissions.Length;i++)
              { 
                if (Permission.ToString()  == Permissions[i].ToString()) 
                {
                   return true;
                }
              } 
            
         }
         catch  { }
         return Ret;
   }

 // Sample Method Call - 1,3 is hard coded to display visually a delimited list of permissions.
  if (!ValidatePermission("1,3",MyNameSpace.PermissionTypes.Permission1))
{
     Response.Redirect("default.aspx");
}



Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.