Overview
A central web application to supports other web applications(A) for user and its role/permission management. Applications (A) call the central application via API to get the user roles/permissions.
1) Application(A) can have resources.
2) Resources can have permissions.
3) User can have access to resource/permissions.
Admin should able to create resources for an application, Permissions for that resource, assign access of a resource/permission to a user and the response should be something like this.
User
- Resource
- Permissions
- Resource
- Permissions
Scenario
Admin for an application(A) wants to create resources say R1, R2 but not necessarily any permissions under them because it is sufficient for him if he get the response like this.
User
- R1
- R2
Approach 1
Create these tables
1) Application
2) Resource (FK as appID)
3) Permissions(FK as resourceID)
4) User_Permissions(FK as permissionID)
In this case since there are no permissions for a resource so User_Permission table will be empty. But then admin wants to give access to the resources, so either the system creates a default permission OR admin has to create a default permission whenever he created a resource. So we enter the default permissions in the User_Permissions table and we can eventually find out the corresponding resources and return the below response.
User
– R1
– Default Permission
– R2
– Default Permission
Approach 2
Create these tables
1) Application
2) Resource (FK as appID)
3) Permissions(FK as resourceID)
4) User_Resources(FK as resourceID)
5) User_Permissions(FK as userResourceID)
In this case we have the extra table User_Resources which specifies the resources to which the user has access and then permissions on that resource.
Need you suggestion which approach to prefer.
Best Answer
I think requirement #3: "Users can have access to resources/permissions" is a little vague. Until you're more specific in your requirements, it cannot be answered. Here is how you can look at your two scenarios and make a decision. In scenario #1 a user can only have access to a resource by having a permission that is linked to a specific resource. That is not the same as having independent access.
If there is some performance reason, you want to separately track the user's resources & permissions, you may find accessing one table performs better than having to constantly join Permissions to the Resource table.
Also, I think you need an additional foreign key in the User_ tables for UserID. They are your typical Many-to-Many link tables.