Database model with users, roles and rights

Question

I have a database model with a user table and role table. I want to control the access (rights) to up to 10 different elements. The access can be granted to either a role or a single user. Below is the table definition of users, roles and items:

CREATE TABLE users
(
  id serial NOT NULL PRIMARY KEY,
  username character varying UNIQUE,
  password character varying,
  first_name character varying,
  last_name character varying,
  ...
);

CREATE TABLE roles
(
  id serial NOT NULL PRIMARY KEY,
  name character varying NOT NULL,
  description character varying,
  ...
);

CREATE TABLE element_1
(
  id serial NOT NULL PRIMARY KEY,
  name character varying NOT NULL,
  description character varying,
  ...
);

...

Now I have two different ways of designing the rights. One table with a rights type column or 10 rights tables - one for each element I want to control the access to.

What are the pros and cons of one rights table vs. one rights table per element? - or is the a more suitable way to do this?

Answer 1

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)
    ...
)

Answer 2

With a rights table for each element, as soon as you add an element you will need to add a table. This would add to application maintenance.

The downside of putting everything in one table is that you might run into scaling issues, but those could be mitigated using partitioning, materialized views, and/or virtual columns. Likely such measures would not be necessary.

As far as the table design, if this were on Oracle I might suggest something like this:

CREATE SEQUENCE UserRoleID;

CREATE TABLE USERROLE 
(
  USERID NUMBER(7) NOT NULL 
, ROLEID NUMBER(7) NOT NULL 
, CONSTRAINT USERROLE_PK PRIMARY KEY 
  (
    USERID 
  , ROLEID 
  )
  ENABLE 
) 
ORGANIZATION INDEX;

CREATE TABLE PERMISSIONS 
(
  ID NUMBER(7) NOT NULL 
, ELEMENTID NUMBER(7) NOT NULL 
, CONSTRAINT USERROLE_PK PRIMARY KEY 
  (
    ID 
  , ELEMENTID 
  )
  ENABLE 
) 
ORGANIZATION INDEX;

The package code could use the UserRoleID sequence to populate the Id in the Users table and the Id in the Roles table as necessary. The Permissions table could then have elements assigned to roles that are in turn assigned to users and/or have elements assigned directly to users.