[RESOLVED] How to best store site-permissions?

DanielLiljeberg

I have a wee bit of trouble deciding how I'm going to store the permissions for users on my site. It must be a way where it's easy to add new types of permissions and easy to add and delete permissions for a user.

My initial thought was to have a table that look like this.

user (id of user)
permission (string to represent a permission)

Then fill it with things like (1, "may_read_forum"), (1, "may_post_in_forum") and so on and so fourth. Then in the relevant places in the code you do an sql query like "SELECT * FROM permissions WHERE user = users_id AND permission = 'may_read_forum' LIMIT 1"

Another way would be to have a permissions filed in the user table and have like a comme delimited list of permissions represented by numbers (1, 4, 6, 7, 8) and then have a table where these permissions were stored with the numeric id and their name. Here you could do a quesry like "SELECT permissions FROM users WHERE user = users_id LIMIT 1" in the beginning of the site and parse and store all the users permissions.

Any other ideas? The first one is clearly the easiest to do. The second would require parsing the result from the query. However, the first on could be a pretty BIG table if the number of users get hig. Say 50.000 users and then say that we have 10-20 permission setting on each user. That would total 1.000.000 records. And then think how often these would be looked through.

Any smart ideas on a good sollution are most wellcome.

tomhath

The first option is best. Put an index on the userid column; it should be very fast, even with thousands of users.

DanielLiljeberg

tomhath wrote:
The first option is best. Put an index on the userid column; it should be very fast, even with thousands of users.

But how does it work to put an index on that column? The same user id can be present several times in the table. Doesn't that stop the use of index?

tomhath

The "UNIQUE" keyword is optional. If you leave it off, the index can have duplicates (which is what you want).

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)

bbreslauer

If you used another table with permission_id and permission_name, then you could use the permission_id in the table correlating users and permissions. This way, you reduce the chance of error (i.e. mistyping the name of the permission), and you also have a table with all the permissions that a user can have, so it makes it easier to see if you already have something that does what you want.