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.