THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Data Driven Security

I have worked on a number of systems where security setting were defined in the data itself. Bit flags were used to determine if a Role did or did not have a certain type of access, namely Select, Insert, Update, or Delete security. The fun part of the design was not the table, which as you can see here, is pretty straightforward.

CREATE TABLE RoleObjectSecurity
(
 RoleID Int NOT NULL,
 ObjectID Int NOT NULL,
 AllowSelect Bit NOT NULL,
 AllowInsert Bit NOT NULL,
 AllowUpdate Bit NOT NULL,
 AllowDelete Bit NOT NULL,
 CONSTRAINT PK_RoleObjectSecurity PRIMARY KEY NONCLUSTERED (RoleID, ObjectID)
)

And perhaps you had data like this:

INSERT INTO RoleObjectSecurity VALUES(7, 100, 1, 1, 1, 0)
INSERT INTO RoleObjectSecurity VALUES(8, 100, 1, 0, 1, 0)
INSERT INTO RoleObjectSecurity VALUES(9, 100, 1, 0, 0, 0)

INSERT INTO RoleObjectSecurity VALUES(7, 200, 1, 1, 1, 1)
INSERT INTO RoleObjectSecurity VALUES(8, 200, 1, 0, 1, 0)
INSERT INTO RoleObjectSecurity VALUES(9, 200, 1, 1, 1, 0)

INSERT INTO RoleObjectSecurity VALUES(7, 300, 1, 1, 0, 1)
INSERT INTO RoleObjectSecurity VALUES(8, 300, 1, 0, 0, 0)
INSERT INTO RoleObjectSecurity VALUES(9, 300, 1, 0, 1, 0)

Upon examining the data, you will see that for object 100, role 7 has access values of 1, 1, 1 and 0 for Select, Insert, Update and Delete respectively. Role 8, on the other hand, has values of 1, 0, 1 and 0, in these same fields. So if a person were in both of these roles, what would his/her effective permissions be? The answer, of course, relies on the choice between a least or most restrictive permission set model. For the least restrictive, the effective permissions would be 1, 1, 1, 0 and for most restrictive, 1, 0, 1, 0.

I'm hoping this is all pretty evident. The real reason for this post is about how one to obtains these resulting permission values. For least restrictive, the query is simple - sum the bits (you'll have to cast them to int first), then take the sign of that value, and finally, cast back to bit. So if any one bit is true (1), then the sum will be at least 1, the sign of which will return 1, which, when cast back to bit means, true (or on, etc...). Take a look at the code, or better yet, copy it and try it out yourself.

SELECT  ObjectID,
 Cast(Sign(Sum(Cast(AllowSelect AS Int))) AS Bit) AS SelLeastStrict,
 Cast(Sign(Sum(Cast(AllowInsert AS Int))) AS Bit) AS InsLeastStrict,
 Cast(Sign(Sum(Cast(AllowUpdate AS Int))) AS Bit) AS UpdLeastStrict,
 Cast(Sign(Sum(Cast(AllowDelete AS Int))) AS Bit) AS DelLeastStrict
FROM  RoleObjectSecurity
WHERE RoleID IN (7, 8) --or you can join to another table with Users and Roles
GROUP BY ObjectID

Most restrictive, on the other hand, requires a little more work, since we need to know if all bits are on, in which case we return a bit value of 1. The process starts off the same, summing the bits, but then we subtract that value from the count of rows that are used for the sum. This will return a value between 0 and Count(*), where 0 means all rows have the bit turned on and any other value means. The sign of this value will thus return a 0 if the role has the permission and 1 if not. So, subtract the value from 1 to get it back to proper bit values, resulting in 0 for no access and 1 for access. Finally, we cast back to bit. Here is an example:

SELECT  ObjectID,
 Cast(1 - Sign(Count(*) - Sum(Cast(AllowSelect AS Int))) AS Bit) AS SelMostStrict,
 Cast(1 - Sign(Count(*) - Sum(Cast(AllowInsert AS Int))) AS Bit) AS InsMostStrict,
 Cast(1 - Sign(Count(*) - Sum(Cast(AllowUpdate AS Int))) AS Bit) AS UpdMostStrict,
 Cast(1 - Sign(Count(*) - Sum(Cast(AllowDelete AS Int))) AS Bit) AS DelMostStrict
FROM  RoleObjectSecurity
WHERE RoleID IN (7, 8) --or you can join to another table with Users and Roles
GROUP BY ObjectID

Cool. Now, another problem was presented to me that was very similar, but certainly more difficult to solve. I will save the solution(s) for another entry, and in the meantime, present the problem to you for your consideration.

The security table has this definition:

CREATE TABLE RoleObjectSecurity
(
 RoleID Int NOT NULL,
 ObjectID Int NOT NULL,
 Security Binary (1) NOT NULL, --Bit Mask: 1 Select, 2 Insert, 3 Update, 4 Delete
 CONSTRAINT PK_RoleObjectSecurity PRIMARY KEY NONCLUSTERED (RoleID, ObjectID)
)

Notice how the security values are in a single binary field. The designer of this thought it would take up less space, and thus be faster. The problem I present: Find the solution that will give effective security permissions for both the least and most restrictive models. It can be done in T-SQL a number of ways. I will present one way in a future post and a second way using a new feature of SQL Server “Yukon” that will make light of the work.

Published Monday, June 26, 2006 12:59 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement