Total a numeric field by using a bitmask

[Codemonkey]

Hi,

I was wondering if there is any way to total a group of records by using a
bitwise OR function to get a combination of values?

For example
-----------

I have a "Users" table and a "Groups" table joined by a "Users_Groups" table
so that Users can belong to many groups and a group can have many users.

Each group has a long integer field called "Permissions" which is a bitmask
of the following values:

-1 =All Permissions
0 =No Permissions
1 =Logon Permission
2 =View Permission
4 =Edit Permission
8 =Delete Permission

If the permissions field for a group "Group1" was "3" then any users
belonging to that group would have Logon and View permissions. If the
permissions field for a group "Group2" was "4" then any users belonging to
that group would have Edit permissions. Any users belonging to both "Group1"
and "Group2" would have a permission value of 7 (Logon, View and Edit
permissions).

I want to get the permissions for a user by using a bitwise OR on the values
of the Permissions field for each group that the user belongs to. Simply
adding them together won't work (SUM) as two groups could have the same
perission which would cause the total to be invalid.

I'd appreciate any help anyone can give.

Cheers,

Trev.

 

[Billy Yao [MSFT]]

Hi Trev,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this issue.

From your description, I understand that you wanted to get the user's permission by means of
summing the bitmask of all the groups that the user belongs to. Have I fully understood you? If
there is anything I misunderstood, please feel free to let me know.

I think it's a create idea and also a feasible way to decide a user's permission. However, I'm
not so sure about what you mentioned by "as two groups could have the same permission
which would cause the total to be invalid"

In my opinion, the permission of each group could be unique and there is no need to create
groups with duplicated permission. If a user has some kind of permission, he can be put into
the corresponding permission group and I believe six different permission groups are enough
and reasonable.

So could you specify why there should be two groups with the same permission in case I miss
some subtle knack or you have some concerns? Any simple sample with detailed table
information, the query you used and the excepted results are greatly appreciated.


Best regards,

Billy Yao
Microsoft Online Support

 

[Codemonkey]

Hi Billy,

Thanks for your response. I'm glad someone thinks my idea is worth while.

Here's an overall view of the tables as requested (with a bit of sample
data - I've just typed this in-it should be enough to demonstrate my
question, but sorry for any mistakes).

Permissions (Constant enumeration in application)
-----------
Value Name
-1 All Permissions
0 No Permissions
1 Logon
2 Send Message
4 Read News
8 Write News
16 View Pictures
32 Upload Files
64 Delete Files
128 Manage Messages

and so on... (can have up to about 30 different permissions)


Users
----------
ID Username
1 Trev
2 Joe
3 Rowen
4 Orla


Groups
----------
ID Name Permissions (Description - not in table)
1 Users 1 Logon
2 Message Senders 3 Logon, Send Message
3 News Readers 5 Logon, Read News
4 News Writers 45 Logon, Read News, Write News, Upload Files
5 Administrators -1 All permissions
6 Day News Managers 125 Logon, Read-Write News, View pics,
Upload-Del files
7 Night News Managr 125 Logon, Read-Write News, View pics,
Upload-Del files


Users_Groups
------------
UserID GroupID (Desc -not in table)
1 2 Trev -> Message Senders
1 7 Trev -> Night News Managers
2 5 Joe -> News Readers
3 4 Rowen -> News Writers
3 2 Rowen -> Message Senders
4 6 Orla -> Day News Managers
4 2 Orla -> Message senders


Here are the expected permissions for each user, based on their group
membership:
User Permission (description)
Trev 127 Logon,Send Msg,Read-Write News,View pics,Upload-Del
files
Joe 5 Logon,Read news
Rowen 47 Logon,Send Msg,Read-Write News,Upload files
Orla 127 Logon,Send Msg,Read-Write News,View pics,Upload-Del
files

In my opinion, the permission of each group could be
unique and there is no need to create
groups with duplicated permission

As you can see, the purpose of a group is not only to have a set of
permissions, but to group users by their function. This makes it easy for
other parts of the application (e.g. Send a message to all Night News
managers etc. Because users may belong to more than one group, and each
group may share some of the permissions of other groups, I cannot simply add
the values together - I need to combine them by using an inclusive OR
function. For example, if I used a query to SUM the permissions, I would end
up with the following:

User Permission
Trev 128
Joe 5
Rowen 48
Orla 128

As you can see, this returns incorrect permissions compared to the expected
results.

Currently, I'm simply returning a datatable of all the group permissions
that a user belongs to to my application and combining them there, but I was
looking to do it a bit smarter and more efficient in the database, thus
cutting down the amount of data sent to my app.

I suppose the ideal thing (in an ideal world) would be if SQL supported
bitwise aggregate functions so that something like the following could be
done:

SELECT OR(Groups.Permissions) AS Permissions
FROM Groups INNER JOIN Users_Groups ON Groups.ID = Users_Groups.GroupID
GROUP BY Users_Groups.UserID;

Thanks again for your help,

Trev.

 

[Billy Yao [MSFT]]

Trev,

Thank you for your feedback and detailed informaiton!

There is really such an OR operation in T-SQL and it performs a bitwise logical OR operation
between two given integer values as translated to binary expressions. However, I think you
can only perform the OR operstion on the column level, so it's need to pivot the table first.

I draft a script for you and hope you are delighted with my support.


--Create the Table Groups
------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Groups]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Groups]
GO

CREATE TABLE [dbo].[Groups] (
[ID] [int] NULL ,
[Name] [varchar] (50),
[Permission] [int] NULL
) ON [PRIMARY]
GO

--Intert Data into Groups
--------------------
INSERT INTO [groups] ([ID],[Name],[Permission])VALUES(2,'Message Senders',3)
INSERT INTO [groups] ([ID],[Name],[Permission])VALUES(6,'Day News Managers',125)
INSERT INTO [groups] ([ID],[Name],[Permission])VALUES(4,'News Writers',45)



--Create teh Table Users_Groups
------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users_Groups]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users_Groups]
GO

CREATE TABLE [dbo].[Users_Groups] (
[UserID] [int] NULL ,
[GroupID] [int] NULL
) ON [PRIMARY]
GO


--Intert Data into Users_Groups
------------------
INSERT INTO [users_groups] ([UserID],[GroupID])VALUES(1,2)
INSERT INTO [users_groups] ([UserID],[GroupID])VALUES(1,6)
INSERT INTO [users_groups] ([UserID],[GroupID])VALUES(3,4)
INSERT INTO [users_groups] ([UserID],[GroupID])VALUES(3,2)


-- Join the Table into #temp
-----------------
SELECT UserID, GroupID, Permission into #temp
FROM Groups INNER JOIN Users_Groups ON Groups.ID = Users_Groups.GroupID


-- Pivot the table, Perform an OR operation and Return the result
-----------------
SELECT UserID,
SUM(CASE GroupID WHEN 1 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 2 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 3 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 4 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 5 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 6 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 7 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 8 THEN Permission ELSE 0 END) AS Permission

FROM #TEMP
GROUP BY UserID
GO


Trev, does that answer your question? Please apply my suggestions above let me know if
this helps resolve your problem. If there is anything more I can do to assist you, please feel
free to post it in the group.

Best regards,

Billy Yao
Microsoft Online Support

 

[Codemonkey]

Billy,

Thanks for the help. I was hoping to do this with Access SQL, but I guess
there's no easy way around it. Thanks for the code. I'll keep it in mind in
case I upsize to SQL server or MSDE.

Best Regards,

Trev.

Trev,

Thank you for your feedback and detailed informaiton!

There is really such an OR operation in T-SQL and it performs a bitwise logical OR operation
between two given integer values as translated to binary expressions. However, I think you
can only perform the OR operstion on the column level, so it's need to pivot the table first.

I draft a script for you and hope you are delighted with my support.


--Create the Table Groups

object_id(N'[dbo].[Groups]') and

OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Groups]
GO

CREATE TABLE [dbo].[Groups] (
[ID] [int] NULL ,
[Name] [varchar] (50),
[Permission] [int] NULL
) ON [PRIMARY]
GO

--Intert Data into Groups
--------------------
INSERT INTO [groups] ([ID],[Name],[Permission])VALUES(2,'Message Senders',3)
INSERT INTO [groups] ([ID],[Name],[Permission])VALUES(6,'Day News Managers',125)
INSERT INTO [groups] ([ID],[Name],[Permission])VALUES(4,'News Writers',45)



--Create teh Table Users_Groups

object_id(N'[dbo].[Users_Groups]') and

OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users_Groups]
GO

CREATE TABLE [dbo].[Users_Groups] (
[UserID] [int] NULL ,
[GroupID] [int] NULL
) ON [PRIMARY]
GO


--Intert Data into Users_Groups
------------------
INSERT INTO [users_groups] ([UserID],[GroupID])VALUES(1,2)
INSERT INTO [users_groups] ([UserID],[GroupID])VALUES(1,6)
INSERT INTO [users_groups] ([UserID],[GroupID])VALUES(3,4)
INSERT INTO [users_groups] ([UserID],[GroupID])VALUES(3,2)


-- Join the Table into #temp
-----------------
SELECT UserID, GroupID, Permission into #temp
FROM Groups INNER JOIN Users_Groups ON Groups.ID = Users_Groups.GroupID


-- Pivot the table, Perform an OR operation and Return the result
-----------------
SELECT UserID,
SUM(CASE GroupID WHEN 1 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 2 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 3 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 4 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 5 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 6 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 7 THEN Permission ELSE 0 END) |
SUM(CASE GroupID WHEN 8 THEN Permission ELSE 0 END) AS Permission

FROM #TEMP
GROUP BY UserID
GO


Trev, does that answer your question? Please apply my suggestions above let me know if
this helps resolve your problem. If there is anything more I can do to assist you, please feel
free to post it in the group.

Best regards,

Billy Yao
Microsoft Online Support