Database model with users, roles and rights 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?
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)
    ...
)