Need help to design model with multiple rights

best practicesdatabase-designSecurity

For a website, I have multiple elements that need to be moderated by many different users.
Until now, I have always used Role-Based Access Control (RBAC) model but I want a more flexible model. I would like to be able to assign some user with some specific rights on particular ressources and after be able to remove/add rights as I want.

For example an element would be a blog with articles. There is the author of the articles who has full rights on his article but I would like to be able to add another user for that article with update right on it. And that for all elements in my website.

Here is what I have:

enter image description here

Elements are 'page', 'article', 'anotherElement'. 'Ownership' is the table where each user has what he can do. 'Right' is the table with specific right (read, write, …). 'Category' is not important, only to subclass my elements.

Is here a better way to do this? The problem will be the size of table 'ownership' after a certain time.

Best Answer

Since my question I have now a great concept.

Security, rights and access are handled by an ownership table. Each row contains :

booleans access for create, delete, read & update
Link to table Element wich contains all manageable elements in my website
Link to table OwnershipType wich tells me if it's an access on ownership, for all ressources or only a specific ressource ID
Link to table User
An ID in case OwnershipType is only a specific ressource

To manage groups, I didn't want to create more tables, what I found clever was to consider a group as a user and assign it some ownerships. To add a user to a group, I have a Parent table wich link a user to another (in this case that user is in fact a 'group'). It explains why I have a UserType table. It tells me if it's a group or a normal user.
In my code I just have to check if a user has a parent and if so add its ownership to the user's ones.
That concept let me having some global ownership on many user who are linked to a group and still have really specific ownership on certain user.

If people are interested I can explain how I implemented that concept in my website, it's quite simple and clean.

My model :

Related Solutions

Database Design – Model with Users, Roles, and Rights

First of all, what type of security model do you plan to implement? Role-based Access Control (RBAC) or Discretionary Access Control (DAC)?

RBAC in the Role-Based Access Control (RBAC) model, access to resources is based on the role assigned to a user. In this model, an administrator assigns a user to a role that has certain predetermined right and privileges. Because of the user's association with the role, the user can access certain resources and perform specific tasks. RBAC is also known as Non-Discretionary Access Control. The roles assigned to users are centrally administered.

DAC In the Discretionary Access Control (DAC) model, access to resources is based on user's identity. A user is granted permissions to a resource by being placed on an access control list (ACL) associated with resource. An entry on a resource's ACL is known as an Access Control Entry (ACE). When a user (or group) is the owner of an object in the DAC model, the user can grant permission to other users and groups. The DAC model is based on resource ownership.

see source

1) In RBAC: you need ElementType table to assign rights to role (users are assigned to role(s)). RBAC defines: "What can this role/user do". Administrator assigns rights for roles and permissions to roles, assigns users to role(s) to access resources. 2) In DAC: users and roles have rights to elements via access control list (ownership). DAC defines: "who has access to my data". User (owner) grants permissions to owned resource.

Any way I suggest this data model:

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

CREATE TABLE ElementBase
(
    Id (PK)
    Type (FK to ElementType)
    ...
)

(one to one relationship)

CREATE TABLE Element_A
(
    Id (PK, FK to ElementBase)
    ...
)

CREATE TABLE Element_B
(
    Id (PK, FK to ElementBase)
    ...
)

1) RBAC (many-to many relationship)

CREATE TABLE ElementType_To_Role_Rights
(
    RightId (PK)
    RoleId  (FK to Role)
    ElementTypeId (FK to ElementType)
    ...
)

2) DAC (many-to many relationship)

CREATE TABLE ElementBase_To_Actor_Rights
(
    RightId (PK)
    ElementBaseId (FK to ElementBase)
    ActorId (FK to Actor)
    ...
)

CREATE TABLE Actor
(
    Id (PK)
    Name
)

CREATE TABLE User
(
    Id (PK, FK to Actor)
    Password
    ...
)

CREATE TABLE Role
(
    Id (PK, FK to Actor)
    ...
)

Database schema for a product with multiple categories and hierarchical categories

What you are proposing is a good solution for your requirement of M:N products to categories and hierarchical categories.

To avoid exposing yourself to numerous updates: You need to do two things to ensure that you don't have a lot of updates in your intersection table.

First, you need to be sure that your categories have a stable, persistent primary key.

Second, you need to link food items to leaf categories. Don't join Cherry to Red, Healthy, Fruit and Food - just join it to Red and Healthy. Your nested sets take care of all of the secondary (and higher level) associations.

Best Answer

Related Solutions

Database Design – Model with Users, Roles, and Rights

Database schema for a product with multiple categories and hierarchical categories

Related Question