| 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");
}
|