Storing unchangeable data in database vs in code

Question

Hi I am trying to create a role based dynamic route authorization system on my NodeJS ExpressJS powered API server.

Scenario:

• There will be some roles like Admin, StandardUser etc. and they can't be edited or removed.

• More roles can be added by authorized users dynamically. These roles of course can be edited or removed.

• Authorized users can set all the route permissions. They can set Role A can use Route X. They can do it for all kind of roles. Dynamic roles or "unchangeable" roles like Admin and StandardUser.

Now my scenario shows that I need a Roles Table because of my dynamic roles and a RoutePermissions Table for dynamically assign route access to my roles. However I can't find a way to store my "unchangeable" data.

1. Where should I put my "unchangeable" (I don't know if there is a term for it. Please let me know.) role data. Where should I put SuperAdmin and StandardUser roles? Because if I put them in roles table in my DB I will have 2 unchangeable rows in it my dynamic data table.

2. Where should I put my "unchangeable" route data. Since the routes (POST /users/deactivate) are coded in the system I don't think it is correct to put them in database. Because I think, the database should contain only the dynamic data but my routes are not dynamic.

3. If i don't put my "unchangeable" data to DB. I can store it in my code like enums or consts but how can I dynamically create permissions with them? Because permissions are dynamic for each role. (Even for Admin and StandardUser remember they are not dynamic roles.)

Answer 1

Data being "unchangeable" doesn't necessarily mean it cannot be changed. It just cannot be changed through the application. Set an "unchangeable" flag on each row. When true the application refuses to change the data. When false it can change it. How you enforce this depends on how paranoid you are.

If you limit this to your application tier, all modifications to data must go through the app tier, otherwise even with "unchangeable" flags on the database tables something else can still change the data. Depending on your needs, this might be desirable.

You could write stored procedures to enforce this behavior. Revoke all INSERT, UPDATE and DELETE privileges from all users, except the user that owns the stored procedures. This forces all data modifications to go through the stored procedures.

Mixing data in code and data in the database, while not impossible, does increase the testing effort. The "data in code" presents a fork in your logic that must be tested everywhere. While this won't be a burden at first, this will start to drag on your process later.

Keep it simple. Keep it as data. Decide which layer of your infrastructure should enforce the immutability of the data (app tier in NodeJS or stored procedures in the database).

Answer 2

There is nothing inherently wrong in storing some data in your DB which will never change later. Just make sure there is no easy way to mess around with this data.

1. Where should I put SuperAdmin and StandardUser roles?

Just put them in your roles table, but introduce a boolean "read only" column in that table. So you can mark the records which should not be changed by the application, and the application can act accordingly.

If you want to be sure noone changed that data in an inconsistent manner, implement a sanity check which validates if all expected records are in the role table, with their expected content. Find a place or process where this sanity check can be done (for example, at startup time of your server, or when this is not frequent enough, in some process for editing the roles table).

2. Where should I put my "unchangable" route data?

It is not clear to me whether you need that route data in the DB or not, but in case you need it because you have also "changeable" route data and want this to be managed consistently with "unchangable" data, the same strategy as for #1 can be applied:

• mark the data with some custom "read only" flag to make it distinguishable from changeable data

• validate that noone has fiddled around with the data and that the routes stored in the DB match the ones hardcoded in the code.

3. If I don't put my "unchangable" data to DB ...

Forget about this. Just store the data in the DB.