44

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?

1
  • 2
    Have you seen the ASP.NET users database that does just this? (as I understand what you're asking, I may be wrong tho)
    – jcolebrand
    Mar 14, 2011 at 21:59

2 Answers 2

37

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)
    ...
)
3
  • 2
    Is it a good idea to make unrelated Element_xxx entities derive from ElementBase? For example, I need to track access control for both my products and my customers. Do you recommend me to create a generic ElementBase and let element_base_id be the primary key for product_id and customer_id even though they are unrelated?
    – Parth Shah
    Jun 4, 2015 at 7:22
  • 1
    RBAC vs DAC, +1
    – Irfan
    Dec 6, 2016 at 5:20
  • @ParthShah what approach you took for your problem? Nov 17, 2017 at 7:51
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.