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?
Best Answer
First of all, what type of security model do you plan to implement? Role-based Access Control (RBAC) or Discretionary Access Control (DAC)?
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:
(one to one relationship)
1) RBAC (many-to many relationship)
2) DAC (many-to many relationship)