title: Database Model for authorization
body: <p>As part of a JSF/JPA Web application project I need to implement a complete user authorization module. I am using Apache Shiro for authentication and may use it for authorization as well if it can fit the requirements. However, right now I am designing the database schema model and have come up with below tables. I am not sure if this is the best approach and need some feedback.</p>
  
  <p><strong>Requirement</strong></p>
  
  <p>Authorize users based on their roles and group memberships. Roles can be assigned to groups or individual users. The data is scattered across multiple tables but here I will just give an example of a table that store project details.</p>
  
  <p><strong>List of tables for authorization</strong></p>
  
  <pre><code>Table:APP_USER : This table will store the user details along with hashed password
  Columns: ID/Username/Password
  
  Table:APP_ROLES : This table stores the roles definitions
  Columns:ID/Rolename/Desc
  
  Table: APP_PRIVILEGES : This table stores the actual privileges that are assigned to roles
  Columns: ID/Privilege Name/Privilege Type/Role ID
  
  Table: APP_GROUPS: This table stores the group definitions
  Columns: ID/GroupName/
  
  Table: APP_USER_GROUPS_MAPPING: This table stores mapping of users to groups and has references to APP_USERS &amp; APP_Groups tables
  Columns: USER_ID/Group ID
  
  Table: APP_GROUP_ROLES_MAPPING: This table stores the mapping of groups to roles and has references to APP_ROLES and APP_GROUPS
  Columns: Group_ID/Role_ID
  
  
  Table: APP_USER_ROLE_MAPPING: This table stores the mapping of users to roles in case the role is directly assigned to users and has references to APP_USERS and APP_ROLES tables
  Columns: USER_ID/ROLE_ID
  
  Table: APP_PROJECTS_DETAILS: This is one of the many tables that store the data. This specific table holds project details
  Columns: ID/PROJECT_NAME/DESC etc
  
  Table: APP_GROUP_PROJECTS_MAPPING: This table stores the permission mapping of which groups has access to which projects.
  </code></pre>
  
  <p><strong>Authorization Example: User tries to delete a project Test1</strong></p>
  
  <hr>
  
  <ol>
  <li>Retrieve Project/group mapping for project Test1 from APP_GROUP_PROJECTS_MAPPING</li>
  <li>Retrieve User Groups from APP_USER_GROUPS_MAPPING</li>
  <li>Check if any of the user group has access to project Test1</li>
  <li>Assuming user has the access, check if the user directly or via group has the DELETE_PROJECT privileges by querying APP_USER_ROLE_MAPPING and APP_GROUP_ROLES_MAPPING respectively</li>
  <li>Delete project Test1</li>
  </ol>
  
  <p>I personally find this a bit complicated but not sure how this can be improved</p>