Database Model for authorization

Question

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.

Requirement

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.

List of tables for authorization

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 & 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.

Authorization Example: User tries to delete a project Test1

---

1. Retrieve Project/group mapping for project Test1 from APP_GROUP_PROJECTS_MAPPING
2. Retrieve User Groups from APP_USER_GROUPS_MAPPING
3. Check if any of the user group has access to project Test1
4. 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
5. Delete project Test1

I personally find this a bit complicated but not sure how this can be improved

Answer 1

Sounds like a good design except you might be reinventing the wheel. Java EE already provides both declarative and programmatic security facilities which is similar to what you're trying to implement.

Answer 2

## Prepare your database relation like this  ##

User----

@OneToMany(mappedBy = "User")
@XmlTransient
private List<GroupPermissions> groupPermissionsList;

@ManyToOne
@JoinColumn(name = "roleId", referencedColumnName = "id", insertable = false, updatable = false)
@XmlTransient
private Role role;
private static final long serialVersionUID = 5667633010066722654L;

GroupPermissions

private int userId;
private int groupId;

@ManyToOne
@JoinColumn(name = "userId", referencedColumnName = "id", insertable = false, updatable = false)
@XmlTransient
private User user;

@ManyToOne
@JoinColumn(name = "groupId", referencedColumnName = "id", insertable = false, updatable = false)
@XmlTransient
private ProjectGroup group;

ProjectGroup Permission

private int groupId;
private int projectId;

@ManyToOne
@JoinColumn(name = "groupId", referencedColumnName = "id", insertable = false, updatable = false)
@XmlTransient
private ProjectGroup projectGroup;

@ManyToOne
@JoinColumn(name = "projectId", referencedColumnName = "id", insertable = false, updatable = false)
@XmlTransient
private Project project; 

Role

  Define your filed in rile table

Execute these steps

Check on project page in load method

1 Retrive user assign role ex (delete,modify,view) if having role( Step 1) than check second step other wise redirect on Unauthorizesd Access 2 First retrive User assign Groups--->Projects