There are two options for you here: without referential integrity and with . A without is certainly simpler if you do not mind the lack of foreign keys, but if you find this indispensable there is an alternative with them - however you will not escape creating an additional table for each entity in your model. / p>
To get started, create the basic templates: Usuario , Grupo , Permissao , and the many-to-many GrupoUsuario , PermissaoUsuario , and PermissaoGrupo associations. This separation will make your life much easier. Explanation: A user belongs to zero or more groups and has zero or more permissions. In addition, the user inherits group permissions (i.e. does 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 greater number of objects of type PermissaoUsuario (since each new user must 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's up to you.
Solution without referential integrity
The Permissao template should be kept as simple as possible: the nome of the permission, the tabela it refers to, and the 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
Verifying that 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);
(Remember: if you omitted Grupo or PermissaoUsuario the bottom or top of union , respectively, is not required; and this is null test is only in case a user has permission to access all table rows, if your model does not have it then you can omit that part)
Solution with referential integrity
In this case your Permissao template will only contain nome (ie it will not have tabela nor linha ), but a series 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. Thus, every line of PermissaoProduto will be inheriting from a line of Permissao - since both have the same primary key. The referential integrity of Usuario is for Permissao , referential integrity for Produtos is for PermissaoProduto . The permission check is also simple, it only has a join more:
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, keeping a table of permissions different for each model, the integration between each type of permission and the user is the same - because it occurs through the Permissao table. Personally, I would use the solution without referential integrity, but it is up to each one.
