Role based security database design

(First published on What The # Do I Know?

I’ve recently got one more upvote to my second most upvoted answer on StackOverflow, bringing the score of that answer to a nice round figure of 60. This motivated me to write this blog post, in an attempt to improve that answer by adding some background.

I’m not going to explain all there is to know about role-based security – first, because that will require an awful lot of time for me to study this subject extensively and second, well, it’s because I like to keep things as simple as possible.
If you want to learn this in depth you can always start with Wikipedia and see where that takes you.

That being said, we do need a minimal background – so: Role-based security (A.K.A. role-based access control or RBAC) is a security approach that allows access to certain resources (A.K.A securable) based on different roles the authorized users might have.
There are four main entities involved in role based security:

• Subject – (A.K.A User) which is the user that use the system. It can be either a human or an automated process – doesn’t matter.
• Resource (A.K.A Securable), which is a part of the application which you want to secure.
• Permission, which is granted on a resource. Permissions defines the actions that a subject might perform on (or with) a resource.
• Role, which is a collection of permissions on a specific collection of resources.

Different permissions might be relevant to different resources – for instance – Suppose your application contains information pages and different forms for the user to fill out – So Read permission is relevant to both information pages and forms, however Write permission is only relevant to forms.

In this post, I’m going to show the design of a very simple permissions approach, which is all or nothing – so I won’t be dealing with permissions at all – the roles are going to be linked to the resources directly – so this design only have three entities: The subject, the role and the resource – and the connections between them would simply be subject to role (many to many), role to resource (many to many) – this means we will need only five tables on our database.

Please note that I’m not going to touch authentication issues in this post, but only describe the authorization system. If you’re not sure what’s what – authentication is the process of identifying a user (usually using a a combination of username / password) and authorization is the process of allowing / denying access to resources.

So – assuming the authentication process is already handled and you have your users table, let’s describe the tables needed for role based authorization.

1. User (user id (PK), user name (unique), password (salted and hashed!), first name, last name, phone etc’)
2. Role (role id (PK), role name (unique), role description)
3. Resources (resource id (PK), resource name (unique))
4. User To Role (user id, role id) – PK is both columns combined
5. Role to Resource (role id, resource id) – PK is both columns combined

This database design provides a basic yet flexible role base security, making it super simple to create and maintain different roles – making the entire authorization of your software that much easier.