Permissions Table

[ejaboneta]

I know it might just be a matter of preference but whats a good way to make a permissions table? My users will have permissions for different parts of the site. Should I add each permission as a column on the users table or make a seperate permissions table? 

[ignace]

You could create something like:

 

acl (user_id, role_id, permission, rule)

 

Or you could create something like:

 

user (.., group_id, .., permission, ..)

group (.., permission, ..)

 

where permission is an integer-value and acts as a bit-map where each bit represents an allowed/disallowed rule, easy and effective if you only have <= 32 permissions (bigint gives you 64)

 

This has the advantage of being able to give individual permissions, select it like:

 

SELECT g.permission | u.permission AS permissions, .. FROM user u JOIN group g ON u.group_id = g.id WHERE ..

 

Use it in your application like:

 

if ($row['permissions'] & SOME_PERMISSION)

 

I just wrote this out of the top of my head and am actually uncertain their is a perfect conversion between MySQL int and PHP int altough it should work (for the int-part). A bigint will result in a float and possibly in a loss of data.