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?
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 = ?