Okay, I really need some help here.

We're building quite a large application that will serve as the backbone for the whole company. We have to implement some sort of role system. We're debating whether or not to store roles in the DB or in code (some sort of config file). Personally I don't see any reason to do so. What's your thoughts?

That just seems like a security issue waiting to happen

Well if you're already storing the user profile in a database why not add one more column there instead of writing lots of code to load the configuration all the time from a file?

We'll, perhaps I'm not clear as to what I mean. Of course there'll be an extra column in the DB in the users table for role. What I'm saying is that it feels like unnecessary overhead to have a table called something like 'roles' with only two columns (plus id) name, and displayname. I feel like I could just keep that in memory and load it upon server startup.

A separate roles table will make life a lot easier later on when the customer decides that a user should be able to have multiple different roles.

@CrashOverride Well, a separate roles table won't solve that. That would be some sort of oen-to-many relationship, something we'll probably use from the start anyways, but that still doesn't justify having a third table containing only the roles that a user can have.

what i mean is: have your users table, which doesnt need any extra column, a roles table that just contains all the roles, and a table which has 2 columns, the user ID, and the role ID.
Thats the approach i would probably take as it means a user can have multiple roles if required. wanna know what roles a user has? just look up the user_roles table with their user ID

@CrashOverride Yeah, a pivot table. But is that third roles table really necessary? Those entries won't change, and if they will it'll be along with the code, since the actual handling is done at the routes which are defined in code. So it will be a static table that will have to be initialized. Isn't it better to keep static data in the code? No end-user will ever change the role types. I need some form of concrete fact as to why we should use one or the other 😅

@ScriptCoded without the context of what kind of handling is happening in the routes its hard to say really. If you are thinking of having the roles themselves defined in code, it sounds like all you need is a column on the user table for role which is either a string (eugh) or an ID for an enum of some sort. Thats fine I guess, to me it just makes more sense to keep the data in the same place. Its ok to have some tables of static values, its called a ref table, i know my project uses a system much like what i described with a pivot table for user roles.

@CrashOverride

So, yeah. I wrote a comment that was ~1900 characters long. It dissapeared. I'll try and recap...

Basically I said that here is a long explanation of our system. Thank you for your time. Hope I'm not wasting too much of it.

We're running a REST-API on Express. We're using it with our Vue.js front-end.

I'm planning on creating middleware for checking roles. Something like hasRole('admin'). Each user can have multiple roles.

Therefore we need a table (let's call it users_roles) that looks like this (of course with ID):

INT user_id
VARCHAR role

But then, when I want to display the role to the user I don't want it to show "admin". Especially not "standard_user". I wan't it pretty. Therefore I figured I could store an object in the config that looks something like this:

roles: {
admin: 'Administrator',
standard_user: 'Standard User'
}

The other solution would be to store that in a separate table, and use the users_roles table like a pivot table.

*NEXT COMMENT*

@CrashOverride

** CONTINUE FROM LAST COMMENT **

However, I don't see any point in doing this for several reasons. The biggest one being that this is static data.

This info will never change, unless we decide to add a new role, though that will be an implementation in code, and therefore I can't see what advantage it would give us to store this information in the database. If anything I see it as a holdback since not only would we have to query the database every time we display the role (unless we store it with the user on the client). The code wouldn't be independent of the database in the way it is right now. If those specific posts weren't to be in the database the application would fail.

But I don't want to do anything too hasty. I want to be sure that I'm doing the right thing.

I think the best I can do is explain how our roles/permissions system works.

We have separate schemas in our DB for customer data and ref data, mainly because our application is multi-tenant so each tenant has its own schema.

In the ref schema we have our roles table, we also have a permissions table because a role can have multiple permissions. We have a pivot table that maps them. That stuff is all static ref data.

In our tenant schemas there is a user_roles table which maps a user to alll the roles they have.

So in our app on the User object there is a hasPermission(permissionId) method which gets all the user's roles, checks all the permissions for each role and returns true if there is any match. Otherwise we return a 403.

We implement the check with a custom Java annotation on each endpoint which intercepts the invocation but you could just as easily have some method call to do the check.

@CrashOverride Thank you. I think this is the approach I will take after all. I touch about quite a bit, and thought that it would leave greater room for future expansion. Thank you!

@ScriptCoded Happy to help :)