[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Bit mask advantages/disadvantages

Posted on 2009-04-15
19
Medium Priority
?
1,013 Views
Last Modified: 2012-05-06
Hi guys,

just wondering if you could give me your input on the usage of a bit-mask field for storing user access rights, rather than having a separate table. Yes I know it is not normalized. Input on indexing is also appreciated.

Database: SQL Server

Thanks in advance
0
Comment
Question by:miral13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
19 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24149923
Not exactly sure what you mean...can you give an example?
0
 

Author Comment

by:miral13
ID: 24149980
Let's say I have the rights stored in a table
1 - Home
2 - Products
4 - Downloads
8 - Forum

And I have users in another table, with their access rights
Michael - 15 (access to everything)
Laura - 3 (only Home and Products)

We can use the bitwise operators to query this.

What would be the disadvantages of this solution rather than having a table with the following associations?
Michael - Home
Michael - Products
Michael - Downloads
Michael - Forum
Laura - Home
Laura - Products

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24150004
OK...I gotcha now.  To be honest, I've always kinda liked this idea.  The only tricky thing is if you ever need to do any joins basped on that data...it makes it more difficult to look up.  however, if you don't have that many values, then it might work out.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 400 total points
ID: 24150576
The downside would be that if you want to do a search for everyone who has the downloads privilege, it will not be able to do an indexed search.

Your bitwise would be

select * from users
where UserPrivColumn & 4=4

But it would have to do the bitwise operator against every record to determine if the 4 bit is set.

Alternatively, you can say.

select userid from userprivs where privname = 'downloads'

What you could consider is a hybrid approach.  Since from the application perspective it would be nice to contain a single value of everything, and the database is more search-able with individual values, store it both ways.  Just make sure that the value can only ever be changed via a stored proc so that your logic is in one place.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24153850
>>just wondering if you could give me your input on the usage of a bit-mask field for storing user access rights<<
We use this approach extensively. We have never had to do this, but if you do notice some slowness you can create computed columns on the bit column to represent the all the options:
1 - Home
2 - Products
4 - Downloads
8 - Forum
You can then index those columns.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24153857
Slightly off-topic, but it is mind blowing what a hard time I have had explaining this subject at work:  People just don't get it.  To the point that I have had to write functions just for extracting the values into "readable" format.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 200 total points
ID: 24154515
also, kinda off-topic, but a way in which you can parse out your bitmask values (an article I wrote for auditing)

http://articles.techrepublic.com.com/5100-10878_11-6175865.html
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 24163995
Thanks for that.  sys.fn_IsBitSetInBitmask looks like an interesting function.  I need to look into it.
0
 

Author Comment

by:miral13
ID: 24165996
I already had this article  http://sqlfool.com/2009/02/bitwise-operations/ which covers the sujbect much better. But I am looking for input from people who actually used this and found any disadvantages and how they managed the indexing.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24167119
It looks like that has been covered.  What else were you looking for?
0
 

Author Comment

by:miral13
ID: 24168885
As I said. Indexing and disadvantages from other users' experience
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24169761
I suspect that if you read the comments posted, both Tim, Brandon and I covered that and possible workarounds.
0
 

Author Comment

by:miral13
ID: 24169832
I am looking for new information.  Something based on real experience with bit mask fields. I keep empathising  on the keyword disadvantages. I want to know if somebody has implemented and what was the impact on performance. I am sorry but I don't think that this question received all the possible answers yet. Have a nice weekend.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24169846
Actually, it has....you're received answers for advantages and disadvantages.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24170109
>>Something based on real experience with bit mask fields<<
Yes we gave that to you.

>> I keep empathising  on the keyword disadvantages.<<
And that was covered when Tim discussed Joins and Brandon focused on searching.

>>I want to know if somebody has implemented and what was the impact on performance.<<
We have all told you we have.  I added that in our case the impact was minimal and suggested workarounds if this was not your case.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24314461
Normally I dispute poor grades.  Normally those poor grades are Bs.  You got good information from 3 TOP experts on EE and you dismiss is because you are unwilling, or unable, to comprehend the answers provided.
0
 

Author Comment

by:miral13
ID: 24314525
Sorry Brandon because I upset you. Please check this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59778
In the end it provided more info than the replies posted here. And yet I closed the question.
You can choose to be upset or you can understand that there was more feedback that could have been provided.

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24314752
You asked for "OUR EXPERIENCES".  We gave you our experiences.  Would you have preferred that we made something up?
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question