I wish to allow customers of my application to include sub-customers and hang rights for which each sub-user is permitted to see or do.

My ideas is to possess a separate Rights table, such as this:

|privilege        |  value |
|create sub users |    1   |
|edit own profile |    2   |
|add new site     |    3   |
|delete site      |    4   |

When the primary user chooses rights update the sub customers privilege column using the value, for instance:

|user_id       | privilege |
|user_1        |     4     | 
|user_2        |     2     |
|user_3        |     1     |
|user_4        |     2     |

However the values don't give unique amounts. For instance:

1 -> create sub users
2 -> edit own profile
= privilege 3 (create sub users, edit own profile)

but additionally there's another privilege for value 3 (add new site) which means this won't work.

So my real question is: How do you make any possible privilege combination unique?

It is possible to wiser method to manage rights?

If you wish to bare this as you column, use base 2 placeholders.

1 - represents priv 1
2 - represents priv 2
4 - represents priv 3
8 - represents priv 4
16 - represents priv 5
32 - represents priv 6

You'll be able to have a modulus of every to find out should they have that priv.


3 = priv 1 and priv 2
9 = priv 1 and priv 4

63 = all privs.

and so forth.

It might be better to simply have your priv table allow multiple records per user.

EDIT: Should you still desire to make use of the single column to keep priv, add another column that stores who gave the permission.

But... I'd still suggest storing each priv individually. Produce a table having a combined primary key on priv, user_id, and grantor. The combined primary key will make sure that every priv is exclusive so you don't have to check before placing. To produce a combined primary key:

ALTER TABLE priv ADD PRIMARY KEY (user_id,grantor,priv_id);

Then to include or totally reset a priv, REPLACE INTO priv (user_id,grantor,priv_id) VALUES (?,?,?)

To remove a priv for any user, DELETE FROM priv WHERE user_id = ? AND priv_id = ?

To remove all priv for any user, DELETE FROM priv WHERE user_id = ?

To remove all sub customers for any grantor... DELETE FROM priv WHERE grantor = ?

Getting all privs for any user inside a grantor: SELECT * FROM priv WHERE user_id = ? AND grantor = ?