Database model with Users, Resources, Permissions

Question

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.

Answer 1

-- User USR exists.
--
user {USR}
  PK {USR}

-- Resource type RTY exists.
--
res_typ {RTY}
     PK {RTY}

-- Resource RSC, of resource type RTY,
-- owned by user USR, is named RSC_NAME.
--
resource {RSC, RTY, USR, RSC_NAME}
      PK {RSC}

      SK {RSC, RTY}

      FK1 {RTY} REFERENCES res_typ
      FK2 {USR} REFERENCES user

-- User USR was granted permission PER to
-- resource RSC by owner of that resource.
--
res_perm {USR, RSC, PER}
      PK {USR, RSC, PER}

FK1 {USR} REFERENCES user
FK2 {RSC} REFERENCES resource

CHECK (PER in ('read', 'write'))

Now the resource subtypes. The proper way to implement constraints for subtypes would be to use assertions (CREATE ASSERTION), but it is still not available in major DBs. I will use FKs instead, and as all other substitute methods it is not perfect. People argue a lot, here and on the SO, what is better. I would encourage you to check other methods too.

-- Car RSC, resource type 'C', ... car specific.
--
car {RSC, RTY, ... }
 PK {RSC}

 FK {RSC, RTY} REFERENCES resource

 CHECK (RTY = 'C')

-- Location RSC, resource type 'L', ... location specific.
--
location {RSC, RTY, ... }
      PK {RSC}

      FK {RSC, RTY} REFERENCES resource

      CHECK (RTY = 'L')

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key