user, role, permissions and a specific group RBAC?

Question

I am trying to design a database schema for a project using mysql as the database. and i am stuck on the authorization part, the business rules are as follows:

1. a role can have many permissions, and a permission and can be inserted in many roles
2. a user can have many roles, and one role can be assigned to many users
3. now this is the part that can't get it right, Every user can be in many "groups" but in each he can be assigned only to one role (by groups i mean group of people and not group of roles)

the following picture is what i came up with till now, what should be amended?

Answer 1

Based on your question and answer to my comment, here is the model I came up with:

This meets your requirements. The main difference with your model is how the groups are setup.

• Group has a 1:1 relation with Role, which can be null (i.e. a group can exist even it no role is associated to it).
• In each Group, you have a foreign key to the Role table.
• If you need to know the roles a users has: individual roles through User_has_Role + role attached to each group the user is a member of (through Group_has_User).