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.

 - Resource
    - Permissions
 - Resource
    - Permissions


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.

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

enter image description here

Need you suggestion which approach to prefer.

  • Please use ER diagrams. – Tulains Córdova Jul 31 '15 at 14:25
  • Shouldn't all FK's point to another table's PK in the diagram? – Tulains Córdova Jul 31 '15 at 14:48

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.

  • Jeff, Thanks for Answering!. Admin of any particular application wants to create 2 resources lets say Fashion and Electronics and no specific permissions and requires a response as specified in scenario section above. So with approach 1 you cannot give access to a resource without giving permission, which essentially means you create a default permission with each resource and assign it to user when you give access for a resource. Imagine giving access as a selection a check box against a resource (which open up permission list with checkbox) – Pratik Garg Jul 31 '15 at 14:41

Certainly not the the approach 2, as it will require an extra overhead of keeping User_Resources and User_Permissions in sync. for eg. an entry in User_Resources would override everything in User_Permissions for a particular resource.

I think, solution lies in defining the terms more elaborately. If I think in terms of OOP I dont see how permissions can be defined/owned by resource creator. Instead what can be/must be defined by the resource creator are the "Operations" that can be performed on the resource, If a resource can not be operated upon its not a resource at all, hence it will be a good design to make it mandatory that each resource have some operations defined.

Then you can define an Entity/Table called Permissions (User_ID,Operation_ID,Type) which will define the kind of access a user have on the resource.

For Eg. As a Resource Definer I might Define (Resource) Fan -> (Operations) TurnOn, TurnOff, setSpeed Then I can define permissions like

userA turnOn READ userA turnOff WRITE userA setSpeed UPDATE

meaning userA can only turnOff or change te speed of the Fan, or check if the Fan is ON but that user can not turn the fan ON.

You can have resourceIds in the table as well for the performance reasons, my suggestions were more around the design.

  • What you call operations OP calls permissions. LIke "permission to turn fan off/on", etc. It' the same thing with another name. Permissions to perform operations on resources. – Tulains Córdova Aug 29 '16 at 6:34
  • Approach #2 is definitely wrong because it allows for insertion anomalies.
  • I'd go with approach 1 but with one improvement: if you don't have roles the administration will be a nightmare sou you need roles.
  • With roles you don't have to assign the same set of permissions again and again to different users.
  • The problem of giving a user absolute permissions over a given resource is no longer a problem also because you would have already created a "super user" ROLE will all permissions on that object. You simply grant that role to the user.

enter image description here

  • A more complete model should allow for permissions to be defined separatelly and associated with resources so you don't have to create the same permissions over and over again for different applications/resources with the risk of repeating permissions with different spellings (an anomaly normalization seeks to avoid). So you have a general permission tables and in the RESOURCE_PERMISSION table you define which permissions are suitable for what resources. ROLE would them those.

enter image description here

  • In a system where users manage users, could you essentially chop off the right hand side. A user and a role would both be a resource that other users manage if they have permission to do so. – Kiee Feb 22 '18 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.