How to store user privileges and queries in mySQL?

Question

What I have:

I have a column in my table with a name privilegesthat stores a privilege row for a user. (-1 = owner, admin = 0, 1 = moderate comments, etc.) So, I would line like this in the sql: 1,2,3.

Problem:

If I wanted to select all users who are admins and moderators administrators, this becomes a bit complicated with the conditions LIKE. Right now, if I want an admin, I am just browsing all the users who are looking for 1 (using PHP is inefficient).

I am looking for a solution that is easily “plugged in” by plugin developers and easily requested. My solution works, but if users had to grow to hundreds, several times it was possible to ride a bicycle all over them to find a certain privilege.

Does anyone have a better way or thoughts on this?

Answer 1

SELECT  *
FROM    privileges
WHERE   FIND_IN_SET(0, privilege)
        AND FIND_IN_SET(1, privilege)

You will have a faster query if you normalize your table (create an additional table user_privilegeswith a separate entry for each privilege). So you can run:

SELECT  user
FROM    user_privileges
WHERE   privilege IN (0, 1)
GROUP BY
        user
HAVING  COUNT(*) = 2

and this can use an index on privilege.

You may also consider storing privileges in a native type SET.

Answer 2

Priviledge, . UserPriviledge, used_id priviledge_id, , INNER_JOIN

Answer 3

Your table should be normalized as suggested by guiman. Per Wikipedia: Normalized tables are suitable for general-purpose queries. This means that any queries to these tables, including future queries whose details cannot be expected, are supported. In contrast, tables that are not standardized lend themselves to certain types of queries, but not others.