September 15, 2009 at 7:22 am
My web developers want to use bitwise permissions to know when certain buttons/drop downs/pages/etc are available to the users. For example, some users can view Commission Statements and others cannot. If the user cannot, the "View Commission Statements" button will not even appear on the web page. I found this article (http://www.colderfusion.com/presentations/darthur/Bitwise_Security.ppt) which was a good start in how bitwise works but it doesn’t go into depth on how I can get bitwise to work with roles, and I’d like to use roles to manage this.
To manage these permissions, I would like to assign a bitwise value to each “function”. These would be stored in the cdPermissionFunction table. And, I want to have “roles” that have multiple functions assigned to them. These would be stored in the cdPermissionRole table. A particular user could be a member of more than one role and could have functions in addition to those that are part of any roles.
I will run a nightly stored proc to update the PermissionValueRole and PermissionValueFunction columns, then calculate the PermissionValue column based on the values in the those two columns.
I want the web developers to be able to access just one column (PermissionValue) to know if a particular function is granted. I need to use a BitwiseOR to calculate this column however, I am totally struggling on how to do this. In this example, John Larsen is a member of both the Head and PGA roles (that’s why the PermissionValueRole column is populated with a 3). The bitwise value for those two roles would be 30 (22 | 30). I need the PermissionValue column to be 94 (30 | 64) since he also has permission to the 'See Cancelled D Prods' function.
Does anybody have a script that will do this? I can’t figure out how to get the bitwiseOR of 22 and 30 so I can get the do a bitwiseOR with 64.
Any help is greatly appeciated! Bitwise operations are pretty cool but totally new to me. Thank you!
--Here are the tables…
CREATE TABLE dbo.cdPermissionFunction(
PermissionFunction int NOT NULL,
PermissionFunctionDesc varchar(50) NOT NULL)
CREATE TABLE dbo.cdPermissionRole(
PermissionRole int NOT NULL,
PermissionRoleDesc varchar(50) NOT NULL,
PermissionValue int NOT NULL)
CREATE TABLE dbo.Agent(
AgentID char(10) NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,
PermissionValueRole int NULL,
PermissionValueFunction int NULL,
PermissionValue int NULL)
--Here is some test data.
INSERT INTO dbo.cdPermissionFunction
(PermissionFunction, PermissionFunctionDesc)
SELECT '1','Proxy View' UNION ALL
SELECT '2','Quote' UNION ALL
SELECT '4','Commission Statements' UNION ALL
SELECT '8','Agency Dropdown' UNION ALL
SELECT '16','Producer Dropdown' UNION ALL
SELECT '32','See Cancelled C Prods' UNION ALL
SELECT '64','See Cancelled D Prods'
INSERT INTO dbo.cdPermissionRole
(PermissionRole, PermissionRoleDesc, PermissionValue)
SELECT '1','Head','22', UNION ALL
SELECT '2','PGA','30', UNION ALL
SELECT '4','MarketingEmployee','3', UNION ALL
SELECT '8','NewBizEmployee','7'
INSERT INTO dbo.Agent
(AgentId, FirstName, LastName, PermissionValueRole, PermissionValueFunction, PermissionValue)
SELECT '0100170002','JOHN','LARSEN','3','64,'0'
September 15, 2009 at 9:07 am
If I got it right, thst's what you need. If the 'select' is OK then put it in the INSERT statement.
I also changed your sample code to allow us to better serve you here with table variables instead of permanent tables:
--Here are the tables…
DECLARE @cdPermissionFunction TABLE (
PermissionFunction int NOT NULL,
PermissionFunctionDesc varchar(50) NOT NULL)
DECLARE @cdPermissionRole TABLE (
PermissionRole int NOT NULL,
PermissionRoleDesc varchar(50) NOT NULL,
PermissionValue int NOT NULL)
DECLARE @Agent TABLE (
AgentID char(10) NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,
PermissionValueRole int NULL,
PermissionValueFunction int NULL,
PermissionValue int NULL)
--Here is some test data.
INSERT INTO @cdPermissionFunction
(PermissionFunction, PermissionFunctionDesc)
SELECT '1','Proxy View' UNION ALL
SELECT '2','Quote' UNION ALL
SELECT '4','Commission Statements' UNION ALL
SELECT '8','Agency Dropdown' UNION ALL
SELECT '16','Producer Dropdown' UNION ALL
SELECT '32','See Cancelled C Prods' UNION ALL
SELECT '64','See Cancelled D Prods'
INSERT INTO @cdPermissionRole
(PermissionRole, PermissionRoleDesc, PermissionValue)
SELECT '1','Head','22' UNION ALL
SELECT '2','PGA','30' UNION ALL
SELECT '4','MarketingEmployee','3' UNION ALL
SELECT '8','NewBizEmployee','7'
INSERT INTO @Agent
(AgentId, FirstName, LastName, PermissionValueRole, PermissionValueFunction, PermissionValue)
SELECT '0100170002','JOHN','LARSEN','3',64,0
SELECT
AgentId, FirstName, LastName,
SUM(DISTINCT (PermissionFunction)) FunctionBits,
PermissionValueFunction,
PermissionValueFunction | SUM(DISTINCT (PermissionFunction)) AS FinalBits
FROM
@Agent A
INNER JOIN @cdPermissionRole PR ON
A.PermissionValueRole & PR.PermissionRole = PR.PermissionRole
INNER JOIN @cdPermissionFunction PF ON
PR.PermissionValue & PF.PermissionFunction = PF.PermissionFunction
GROUP BY AgentId, FirstName, LastName, PermissionValueFunction
The trick is to have each bitmask decomposed to its single bits and them SUM(DISTINCT) over them
September 16, 2009 at 8:04 am
This works beautifully! It's just what I needed.
Thank you!
September 16, 2009 at 10:56 am
Happy to help.
Also, I'm thrilled to see some people still opting for bitwise operations. It is very powerful yet so underestimated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply