title: MySQL PHP User permissions & Group permissions combined - best practice?
body: <p>I am attempting to create a small application which will have various user groups, around 6. I am attempting to figure out the best way to create the access control model. Here are few requirements:</p>
  
  <ul>
  <li>The permissions should be easily editable as the application evolves
  and new permissions are needed or old ones become redundant</li>
  <li>A user can be a member of multiple groups</li>
  <li>Groups should have a flexible set of permissions which can easily be
  edited</li>
  <li>Users can also have individual permissions which should override
  those stated in the group permissions controller</li>
  </ul>
  
  <p>Here is the table layout I have come up with through researching various different methods/ideas.</p>
  
  <p><a href="https://i.stack.imgur.com/lzoVB.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lzoVB.jpg" alt="enter image description here"></a>
  My thought was that by using this table structure I can run an SQL query like this (using the user_id from the currently logged in user):</p>
  
  <pre><code>SELECT users.id, users.forename, permissions.name
  FROM permissions
  INNER JOIN groups_permissions ON permissions.id = groups_permissions.groups_permissions_permissions_id
  INNER JOIN groups ON groups_permissions.groups_permissions_groups_id = groups.id
  INNER JOIN groups_users ON groups_permissions.groups_permissions_groups_id = groups_users.groups_id
  INNER JOIN users ON groups_users.users_id = users.id
  WHERE users.id =4
  UNION 
  SELECT users.id, users.forename, permissions.name
  FROM permissions
  INNER JOIN users_permissions ON permissions.id = users_permissions.users_permissions_permissions_id
  INNER JOIN users ON users_permissions.users_permissions_users_id = users.id
  WHERE users.id =4
  </code></pre>
  
  <p>That should give me a combined list of permissions, including all group and any user specific permissions. I can then cache this query locally so I don't need to run it multiple times. (I guess I will setup some kind of method to clear the cache if the permissions table is changed.)</p>
  
  <p>Does this seem like a reasonable plan or are their obvious negative aspects that I am overlooking?</p>
  
  <p>Thanks</p>