Database design: manage permissions on multiple applications resources

Question

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.

Answer 1

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.

Answer 2

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

• 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 then use those.

Answer 3

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.