0

So the app we are writing has certain privileges a user can have, and at first we just made a simple bitmask and saved each privilege as a separate bit and stored the final value into our DB table as a long. But now we have so many permissions that the value won't fit as a long in the database anymore. I suppose we could store it as a string rather than a long and do a conversion, but it makes me wonder if this is the best way to approach the problem.

ex: user has permission 1-3-5-7

his security value is 2^0 + 2^2 + 2^4 + 2^7 = 149

but once I have 100 permissions this approach seems a bit messy, as calculating 2^99 seems expensive and hard to look at in a DB table (that would have to be stored as a string since it's bigger than a long). So does anyone have a different approach to solving this problem when the number of permissions gets very large that works quickly and is not too complicated?

Kevin DiTraglia
  • 25,746
  • 19
  • 92
  • 138

2 Answers2

1

I believe I have found the best approach for this, so I'll go ahead and answer my own question since it's been open for so long. The best approach seems to be to make an XRef table within the database that ties a user to permissions, something like this.

Security Table:

ID | User Right
--------------------------
1  | Able to edit records
2  | Able to create records
...etc

User Table:

ID | Username
--------------
1  | Kevin
2  | Bob
3  | Bill

XRef Table:

UserID | SecurityID
--------------------
1      | 1
1      | 2
2      | 2
3      | 1
3      | 2

This allows you to have as many permissions as you want and not limited to the size of a long to create permissions based on.

Kevin DiTraglia
  • 25,746
  • 19
  • 92
  • 138
0

You could add a second long column. If the index of the permission is larger than x, you look it up (or store) in the second column?

pzecevic
  • 2,807
  • 22
  • 21