title: Group and Personal Permission Design MVC
body: <p>We currently have a structure that gives certain users roles and associates their roles with permissions, which are essentially features. <code>EditUser</code>, <code>ViewUser</code>, etc. </p>
  
  <p>When the user logs in it loads all of their permissions into the auth cookie and our custom Authorize attribute checks to see if they have the permission required to access the function. This works great. </p>
  
  <p>The new requirement is to have the ability to have individual users have permissions that <em>override</em> the group permissions. So if my user is in a group that has <code>EditUser</code> but we want to remove that permission for that particular user, we should be able to. In the same vein, if a user is in a group that doesn’t have that permission we should be able to give that access if needed. Much like Windows folder permissions. </p>
  
  <p>I would like to do this in SQL if possible. Return a list of all the permissions they have. I’m not sure of the best way to implement this. We have <code>Roles</code>, <code>Users</code> <code>UserRoles</code>, <code>Permissions</code>, and <code>RolePermissions</code>. Users are connected to a permission through that chain. </p>
  
  <p>I assume I need to make a <code>UserPermission</code> table, but I’m not sure how I would write the query to have rows in that table override the ones from the <code>RolePermission</code> table. </p>
  
  <p>Any design principles or direction is greatly appreciated. I’m willing to completely uproot if necessary. </p>
  
  <p>EDIT: Here's my current query</p>
  
  <pre><code>SELECT PermissionID, PermissionKey 
  FROM Permission p 
       INNER JOIN RolePermission rp ON rp.PermissionID = p.PermissionID 
       INNER JOIN UserRole ur ON ur.RoleID = rp.RoleID 
       INNER JOIN Users u ON u.UserID = ur.UserID
  WHERE u.Username = @Username
  </code></pre>