October 22, 2014 at 2:30 am
I am trying to get a culmulative Bitwise OR operation on a column by query - rather than using a CLR function.
The problem is shown below:
CREATE TABLE #Operations (
PK INT NOT NULL IDENTITY(1,1),
UserName VARCHAR(50) NOT NULL,
UserProcess VARCHAR(50) NOT NULL,
ServerOperation VARCHAR(50) NOT NULL,
Permission INT NOT NULL );
INSERT INTO #Operations (UserName, UserProcess, ServerOperation, Permission)
SELECT 'Tom', 'Ledger', 'Finance', 1 UNION ALL
SELECT 'Tom', 'Ledger', 'Finance', 2 UNION ALL
SELECT 'Tom', 'Ledger', 'Finance', 4 UNION ALL
SELECT 'Bill', 'Dispatch','Export', 2 UNION ALL
SELECT 'Bill', 'Dispatch','Export', 4 UNION ALL
SELECT 'Mary', 'Audit', 'Finance', 1 UNION ALL
SELECT 'Mary', 'Invoice','Export', 1 UNION ALL
SELECT 'Mary', 'Invoice','Finance', 8 UNION ALL
SELECT 'Tom', 'Audit', 'Finance', 15 UNION ALL
SELECT 'Mary', 'Dispatch','Export', 3
SELECT * FROM #Operations;
-- Get the full permissions a user has on a Server Operation
-- Solution: Bitwise OR on all permissions by user/serverOperation
--e.g. Tom/Finance perms = 1 | 2 | 4 | 15 = 15
-- Mary/Export perms = 1 | 3 = 3
DECLARE @BITFLAG INT = 1 | 2 | 4 | 15;
SELECT @BITFLAG AS BITFLAG
-- Expected Output
UserName, ServerOperation, Permission
Bill, Export, 6
Mary, Export, 3
Mary, Finance, 9
Tom, Finance, 15
So Far I've tried SUM - wrong results, and STUFF - which doesn't seem appropriate for bitwise operation - neither give useful results.
-- SUM Operation - fails
SELECT DISTINCT
UserName,
ServerOperation,
SUM(Permission) AS Permission
FROM #Operations
GROUP BY UserName, ServerOperation
-- STUFF 'OR' bitwise operation XML PATH
SELECT DISTINCT
Op2.UserName,
Op2.ServerOperation,
STUFF( (SELECT 0 | Op1.Permission
FROM #Operations AS Op1
WHERE Op1.PK = Op2.PK
FOR XML PATH('')),1,1,'') AS Permission
FROM #Operations AS Op2
GROUP BY Op2.UserName, Op2.ServerOperation, Op2.PK
It may be possible to materialise the permissions each time one is changed (e.g. by to use a cursor ), however I would prefer a query or function or combination to generate the result
October 22, 2014 at 3:00 am
Interesting.
If you could find a way of breaking down the permission at row level (eg, from 7 to (4,2,1)), it may be possible to do a SUM() on the UNIONed permissions (with the UNION taking only distinct values). Not straightforward though. I'm sure that others will have better ideas.
--Edit: fixed incorrect example of breakdown of powers of 2 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 22, 2014 at 3:36 am
You can do it by breaking each permission down into a sum of powers of two. Something like this should work:
SELECT UserName, ServerOperation, SUM(DISTINCT p)
FROM #Operations o
OUTER APPLY (
SELECT p FROM (
SELECT POWER(2,n) FROM (
VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8)
) Nos(n)
) Powers(p)
WHERE p & o.Permission = p
) Bitmask
GROUP BY UserName, ServerOperation
John
October 22, 2014 at 5:13 am
John Mitchell-245523 (10/22/2014)
You can do it by breaking each permission down into a sum of powers of two. Something like this should work:
SELECT UserName, ServerOperation, SUM(DISTINCT p)FROM #Operations o
OUTER APPLY (
SELECT p FROM (
SELECT POWER(2,n) FROM (
VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8)
) Nos(n)
) Powers(p)
WHERE p & o.Permission = p
) Bitmask
GROUP BY UserName, ServerOperation
John
Awesome work John 😎
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 22, 2014 at 5:18 am
Thanks John,
Brilliant lateral thinking.
October 22, 2014 at 5:20 am
Blush! Thanks, guys.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply