ACL / Permissions system MYSQL database design approach

this is a bit of a big question, so if anyone can point me to a great tutorial or something else if its too long to answer here that would be great.

I have a MySQL database, and I want to have an ACL system to manage permissions to an object.

E.g

Customer (read | edit | update | delete)

So I was thinking I need the following tables

users
groups
users_groups (users_id, groups_id)
acl (user_id | group_id, item_id)
acl_permission (user_id | group_id, item_id, (read | edit | update | delete))

I built this, and it works, I'm just wondering if this is an OK way to do it, or if it can be simplified / improved..

Some of the queries can involve a lot of joins to say get the list of customers for user x and开发者_Python百科 list all of the permissions, so maybe I'm doing something wrong.

Many thanks for any feedback..

Just from looking at your definition of acl and acl_permission - which can take either a user_id or a group_id, maybe it would be a little cleaner if every user was a member of their own group as well, e.g. "Just Brett" as well as "All DBAs".

Nothing else leaps to mind.

I worked in similar case, I think the way I did it I have a lot of flexibility:

employee (id, firstname,lastname)
department (id, name, department_id)
rol (id, name)
employee_rol (employee_id, department_id, rol_id)
permission(id, name, description, read,write, etc...)
rol_permission (rol_id, permission_id)

So I can have infinite rols per employee and infinite permission per rol.