Storing multiple user roles in a single column(TinyInt) to a database.

 

Role Based Access Control (RBAC):

It is the developer's responsibility to ensure the security of web applications while developing it. Broken access control is one of the critical vulnerabilities in web applications. It allows users to access resources that are not supposed to be accessed by them. To learn more about this vulnerability click here.

This vulnerability can be eliminated by implementing the right access control logic. One of the common and simple methods is Role-Based Access Control (RBAC). In this approach, we assign a user with specific roles. Each role will have specific resource access.

I know most of the web developers will be aware of it. But I've seen many developers use strings to store roles in the database.

Look at the below table, (approach:1)

id	name	role
1	tony stark	admin
2	peter parker	customer
3	bruce	vendor
4	natasha romanof	customer

 

Some application need multiple user roles for single user, so they just do the same as above but using a separate table. (approach:2)

 

id	name
1	tony stark
2	peter parker
3	bruce
4	natasha romanof

id	user_id	role
1	1	admin
2	2	customer
3	3	vendor
4	3	customer
5	4	customer
6	4	vendor

 

Both the approaches are bad, these approaches store a string in a database for every record. These strings are also duplicated. Some developers use one more table to store roles separately and create a user_role table to map users to roles. (approach:3)

 

id	name
1	tony stark
2	peter parker
3	bruce
4	natasha romanof

id	role
1	admin
2	vendor
3	customer

id	user_id	role_id
1	1	1
2	2	2
3	3	3
4	3	2
5	4	3
6	4	2

The above approach is good. But we have to use three tables here. What if we could achieve this multiple user roles for one user with just one table, to be more precise we will be using only one column to store user role. 

 Note: I'm not saying that you should not use three tables to achieve this, I'm just saying that you could also achieve this without three tables(both will have their own pros and cons).

Okay, Let's look at some of the issues with the above-mentioned approaches.

The 1st two approaches use strings to identify roles. Assuming that role names may contain up to 10 characters, we will be using 10 bytes to store user roles in each record. Since user roles can be predefined (like enum) we don't need to store that in string format.

 
The 3rd approach makes sense. We are just storing user roles once in one table and we are mapping it with users by using a bridge table. All right, now we are going to see another method that doesn't require those tables (except User).

We are going to use a single byte integer (tinyint) to store multiple user roles. The size may increase based on your user role requirements (1 role per bit).

User Roles in Binary Representation:

In MySQL, tinyint can store 1 byte of data. 1 byte has 8 bits. Practically speaking, we only need one bit to represent one role.

Let's assume you have the user role of admin. If someone asks you are you admin? you just need to say (Yes (true) / No (false)).

Like this, we can use one bit for each user role. In most of the applications, we will have only less than 8 user roles, so 1 byte will be enough for those applications.

 

If you ever used Linux or Unix-based filesystem and tried to set permissions for some file like chmod 421 somefile  The number 4 represents read 2 represents write and 1 represents execute. we can add those combinations and get any permissions we need. If you are not sure about this, that's fine. Let's dive into our sample code in JavaScript.

ACL.js

 class ACL {
    index = 1;
    all_roles = {};

    setRoles(roles) {
        roles.forEach((role, i) => {
            this.all_roles[role] = this.index << i;
        });
    }

    getRoleCode(roles) {
        return [...new Set(roles)].reduce((result, role) => this.all_roles[role] + result, 0);
    }

    getRoles(code) {
        return Object.entries(this.all_roles).reduce(
            (result, [role, key]) =>
                key & code ? result.concat(role) : result,
            []
        );
    }

    hasAny(code, roles) {
        return roles.some((role) => this.all_roles[role] & code);
    }

    hasAll(code, roles) {
        return roles.every((role) => this.all_roles[role] & code);
    }
}

Let's play with our code. 

Somehow we need to store user roles in human-readable format (string). Here we have multiple-choice, we can store it in the config as an array or we can store it in a table. It's up to you. Assuming we are not using a table for it, we are just storing it in a variable for now (storing in the config file will be similar).

let roles = ["admin", "finance", "vendor", "customer"];

let AclInstance = new ACL();
AclInstance.setRoles(roles);

console.log(AclInstance.all_roles); // { admin: 1, finance: 2, vendor: 4, customer: 8 }

In the above code, we are creating a new instance of our class, we can set roles for our instance(with setRoles method), it generates values for each role. We can use the same instance to generate and verify the user roles. You may guess right, we can bind this is as a singleton.

The setRoles method just assigns value based on the index of role in the given array. The bitwise left shift operator shifts one bit left for each role.

const tony_stark = AclInstance.getRoleCode(['admin'])                       // 1
const peter_parker = AclInstance.getRoleCode(['customer'])                  // 8
const bruce = AclInstance.getRoleCode(['customer', 'vendor'])               // 12
const natasha_romanof =  AclInstance.getRoleCode(['admin', 'finance'])      // 3

The getRoleCode method accepts an array of roles and returns the role code that we need to store in our database. Have you understood what the function did here? It just adds previously generated codes (same as Unix-based file system permissions). 

The multiple user roles for every user can now be stored in the database in a single byte.

id	name	role
1	tony stark	1
2	peter parker	8
3	bruce	12
4	natasha romanof	3

 

Sounds cool, But how can we get back the original values? I've written methods for that too, let's look at it.

Sometimes we need to get user's roles as a string ( To displaying the list of users in the admin dashboard). For that, we have method getRoles that simply returns all user roles for that user. It just takes role code as an argument that we saved in our database.

Let's see that in code.

 

const tony_stark_roles = AclInstance.getRoles(tony_stark);              // [ 'admin' ]
const peter_parker_roles = AclInstance.getRoles(peter_parker);          // [ 'customer' ]
const bruce_roles = AclInstance.getRoles(bruce);                        // [ 'vendor', 'customer' ]
const natasha_romanof_roles = AclInstance.getRoles(natasha_romanof);    // [ 'admin', 'finance' ]

In most scenarios, we just use roles to decide whether to allow the user to access the resource or not. We can check that easily by using hasAny and hasAll methods. The hasAny method returns true if the user belongs to any of the given roles. The hasAll method returns true only if the user belongs to given all roles.

 

const only_admin = AclInstance.hasAll(tony_stark, ['admin']);                           // true
const only_admin_1 = AclInstance.hasAll(peter_parker, ['admin'])                        // false
const admin_or_customer = AclInstance.hasAny(natasha_romanof, ['admin', 'customer'])    // true
const customer_or_vendor = AclInstance.hasAny(peter_parker, ['customer', 'vendor'])     // true
const customer_and_vendor = AclInstance.hasAll(bruce, ['customer', 'vendor'])           // true
const customer_and_vendor_1 = AclInstance.hasAll(peter_parker, ['customer', 'vendor'])  // false

In the above code, we have tony_stark has admin role. So both hasAny and hasAll methods should return true if we test against admin role. Likewise, we can test the user against multiple roles. I hope this can be easily implemented as middleware.

 
I hope you learned something new today, If you have any doubts ask me in the comment section below(suggestions are accepted). To receive email updates on new posts click subscribe.

 

Follow me on twitter @cybersrikanth 

Thank You