Modeling for user permissions

Question

I think this question is more of modeling analysis actually do not know if it fits the pattern of questions discussed by meta.pt.stackoverflow.com. I need to make my system in a way that almost everything will have user permissions, I made the following modeling of entities

However I may have to put "ACCESS and CRUD" permissions for the manufacturer of the product or permissions for the user that has nothing to do with the category or manufacturer, for example: permissions for the user to register other users and give permissions for them, the problem is that in my current modeling style I would have to create another permissions table for the manufacturer and another permissions table for registering other users, I would like to know if there is any modeling pattern (Designer Patterns) for this type of situation?

Diagram: Link

Answer 1

There are two options for you here: the without referential integrity and the with. A without for sure is simpler, if you do not care about the lack of foreign keys, but if you find it indispensable there is an alternative with them - however you will not escape creating an additional table for each entity of your model.

For starters, create the basic templates: Usuario, Grupo, Permissao, and the many-to-many associations GrupoUsuario, PermissaoUsuario and PermissaoGrupo. This separation will make your life much easier. Making it clear: a user belongs to zero or more groups and has zero or more permissions. In addition, the user inherits group permissions (i.e. do not need to explicitly assign a permission if one of the groups where it is already has this permission).

You can omit Grupo if you want to simplify your model, at the expense of a larger number of objects of the PermissaoUsuario (as each new user will have to explicitly receive all permissions). Alternatively, you can omit PermissaoUsuario if you are not interested in giving individual permissions to a user, only indirectly through a group. It is up to you.

Solution without referential integrity

The model Permissao should be kept as simple as possible: nome of permission, the tabela which she refers to and linha in the table (possibly NULL), if applicable. Example:

Permissao
nome             tabela         linha
-------------------------------------
create           Produtos       NULL         <-  Usuário pode criar Produtos
read             Produtos       42           <-  Usuário pode ler o produto 42
read             Fornecedores   NULL         <-  Usuário pode ler Fornecedores

Checking if a user is allowed to access a record is simple:

select 1
    from Usuario u
         join PermissaoUsuario pu on pu.id_usuario = u.id
         join Permissao        p  on pu.id_permissao = p.id
    where
         u.id = 10 and
         p.nome = 'read' and
         p.tabela = 'Produtos' and
         (p.linha = 42 or p.linha is null)
union
select 1
    from Usuario u
         join GrupoUsuario   gu on gu.id_usuario = u.id
         join PermissaoGrupo pg on pg.id_grupo = gu.id_grupo
         join Permissao      p  on pu.id_permissao = p.id
    where
         u.id = 10 and
         p.nome = 'read' and
         p.tabela = 'Produtos' and
         (p.linha = 42 or p.linha is null);

(Remembering: if you omitted Grupo or PermissaoUsuario the bottom or top of the union, respectively, it is not necessary; and this test is null is only in case a user has permission to access all table rows, if your template does not have this then you can omit that part)

Solution with referential integrity

In this case your model Permissao will contain only the nome (i.e. will not have tabela nor linha), but a number of other templates will be created to represent the permission to access a specific line of a specific model. For example:

create table PermissaoProduto(
    id_permissao integer not null,
    id_produto   integer not null,
    primary key (id_permissao),
    foreign key (id_permissao) references Permissao(id),
    foreign key (id_produto)   references Produtos(id)
);

The rest stays the same. So, every line of PermissaoProduto will be inheriting of a line of Permissao - because both have the same primary key. The referential integrity to Usuario is on account of Permissao, the integrity referenced to Produtos is on account of PermissaoProduto. Permission check is equally simple, it only has one join the most:

select 1
    from Usuario u
         join PermissaoUsuario pu on pu.id_usuario = u.id
         join Permissao        p  on pu.id_permissao = p.id
         join PermissaoProduto pp on p.id = pp.id_permissao
    where
         u.id = 10 and
         p.nome = 'read' and
         pp.id_produto = 42
union
select 1
    from Usuario u
         join GrupoUsuario   gu on gu.id_usuario = u.id
         join PermissaoGrupo pg on pg.id_grupo = gu.id_grupo
         join Permissao      p  on pu.id_permissao = p.id
         join PermissaoProduto pp on p.id = pp.id_permissao
    where
         u.id = 10 and
         p.nome = 'read' and
         pp.id_produto = 42;

Although it seems complicated at first glance to keep a different permissions table for each model, the integration between each type of permission and the user is the same - because it happens through the table Permissao. Personally, I would use the solution without reference integrity, but at the discretion of each.