The right way to manage user privileges (user hierarchy)

Question

I want to allow users of my application to add sub-users and set privileges for what each sub-user is allowed to view or do.

My ideas is to have a separate PRIVILEGES table, like this:

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

Then when the main user selects privileges update the sub users privilege column with the value, for example:

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

But the values do not give unique amounts. For example:

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

but also there is another privilege for value 3 (add new site) so this will not work.

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

Is there a smarter way to manage privileges?

Answer 1

If you want to keep this as one 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

Then you can take a modulus of each to determine if they have that priv.

So..

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

63 = all privs.

and so on.

It may be simpler to just have your priv table allow multiple entries per user.

EDIT: If you still wish to use the single column to store priv, add another column that stores who gave the permission.

But... I'd still suggest storing each priv separately. Create a table with a combined primary key on priv, user_id, and grantor. The combined primary key will ensure that each priv is unique so you don't need to check before inserting. To create a combined primary key:

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

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

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

To delete all priv for a user, DELETE FROM priv WHERE user_id = ?

To delete all sub users for a grantor... DELETE FROM priv WHERE grantor = ?

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