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.