Olá! Muito interessante sua pergunta.

Há várias formas de se realizar o controle de acesso aos recursos de uma aplicação, desde as mais simples até as mais complexas. Há diferentes modelos de concessão de permissões também.

Para algumas aplicações, a restrição de um nível de acesso (a tal da "role") pode ser granularizada por ação (criar, ler, alterar ou apagar algo), que poderíamos chamar de "escopo" de autorização. Em outras, você vai encontrar restrições mais "globais", onde um papel de usuário pode acessar determinados conjuntos de recursos definidos pelas regras de negócio.

Os papeis dos usuários podem ser cumulativos (níveis de acesso mais elevados agregam permissões de papeis com menos privilégios) ou exclusivos (os níveis de acesso concedem privilégios distintos e não hierárquicos entre si).

O que e como você vai implementar o controle de acesso depende da modelagem dos casos de uso e da arquitetura do projeto. Por exemplo: em sistemas de gestão, você normalmente encontrará um usuário admin/master, que pode fazer de tudo (cumulativo), e usuários cujos papeis acessam áreas diferentes do sistema (RH, financeiro, suporte etc)(exclusivo).

Conceitualmente falando, tudo isso que falei pode ser resumido em duas siglas: RBAC (Role-Based Access Control) e ABAC (Attribute-Based Access Control).

Honestamente, se for um sistema mais simples para poucos usuários, eu adotaria uma abordagem menos robusta ("less is more"), sem muita granularidade. Agora, se você tem em mente algo maior e mais complexo, talvez você queira ter um controle mais fino de acesso a determinados recursos.

Você pode implementar o controle de acesso pela própria aplicação (se for uma API REST, por exemplo, é possível definir regras de acesso aos recursos expostos pelos endpoints).
Se os papeis de usuário forem fixos, pra mim não vale a pena cadastrá-los em base de dados, mas sim definidos em alguma estrutura de lista ou "enum" (vim do mundo Java, não penso em outra estrutura melhor que essa pra esse fim, rs).
Com relação às permissões, idem. Se elas não forem mutáveis, melhor deixá-las na aplicação mesmo.

Para finalizar: tudo isso vai depender da proposta, da modelagem e do tamanho do seu sistema (a grosso modo). Os conceitos e formas de controle de acesso costumam ser padronizadas. Escolha um modelo e manda bala.

Espero ter ajudado. Boa sorte!