Database model with Users, Resources, Permissions

2 votes
1 answer
1578 views
                          I am fairly new to the DB design. I need to handle what a user can do on a specific resource.

One of the main queries I need to drive is:

> Get all the cars a user has write permission to.

I think if I only had the one resource it would be easier, but I will have plenty of resources and so I am trying to design something via a ResourceType model, so that I don't have to create a lot tables as the number of resources grow.

I saw this question: Discretionary Access Control (DAC) model 


For what I can tell that is the closest thing to what I am trying to accomplish because it differentiates between roles approach and discretionary. I do not have roles and won't have roles for now. When a user creates a resource (like Car, Location, etc...) the server assigns the "owner" permission for that user. The owner can in turn grant permissions to other users, like read, write...

I have created the following tables:

        CREATE TABLE ResourceType
        (
           Id (PK)
           Name
           resourceId
           ...
        )

        CREATE TABLE Car
        (
           Id (PK)
           model
           resourceTypeId (FK to car ResourceType)
           ...
        )

        CREATE TABLE User
        (
           Id (PK)
           Name
           ...
        )

        CREATE TABLE Permission
        (
           Id (PK)
           canRead
           canWrite
           ...
        )



then I created a junction table:

        CREATE TABLE ResourceTypeToUserPermission
        (
           Id (PK)
           permissionId (FK to Permission)
           resourceTypeId (FK to ResourceType)
           userId (FK to User)
        )

I was hoping to use that junction table to get what I needed with inner joins and where clauses on userId = x... 

I am obviously not doing this right and I am wondering how I can modify things correctly so that for any resource I will have in the DB  I will be able to get all resources of a given type with a certain type of permission for a given userId.

Any help would be much appreciated.


                        
Asked by zumzum (123 rep)
Apr 14, 2020, 04:21 AM
Last activity: Jul 25, 2025, 06:01 PM
Database model with Users, Resources, Permissions

Related Questions
