0

Context

I want to design a procedure or system task to load into a database, data that's needed by the system in order to work. The easiest example I can come up with is a ACL (or RBAC, not sure which one is the right term) where an user need permission to perform certain actions (create a resource, view a resource, view a scoped list, view a global list).

In this example, as a basic schema we'd have a user N:M role N:M permission relationship model.

The permission table contains a column (let's say name) with a value that the system will use to determine the permission needed to perform this action:

if loggedUser.can('reports.view.global')
  reports = reportsRepo.all()
else
  reports = reportsRepo.allForDepartment(loggedUser.department)
end

Since the system is dependant on these "hard coded" values, they need to be in sync with the database on each deploy. Meaning, if the next release will add 2 new features (with 2 new permissions) and delete 1 feature (with 1 permission), the deploy task should be updating the database to keep it in sync with the permissions that the next version works with.

Questions

Having explained that, I also want to take in consideration I'm not falling under the XY Problem and that this is actually the correct approach for this problem:

  1. Should these kind of system data (not sure if the concept itself has a name) be stored in a database (for query-ability purposes) or is there a better standard way?
  2. Is a cli task that checks for those system data values (in some config file) and sync them with the permission table (adding or deleting rows) a good approach, or is it not due to some reason I'm failing to see at the moment?

I've never seen an implementation of this common scenario before, so I'm wondering whether there's already a standard approach for this particular problem (maybe it's even named and I just have to google it but I don't know it's name...)?

  • It is common to require database upgrades when upgrading the application. For example, adding a column, or even larger schema changes requiring migration. See, this, for example. Do you see this as somehow different or having more challenging/special requirements? – Erik Eidt Jun 8 '17 at 0:59
  • Well, this is not really a schema change, it's data itself that the system depend on. Meaning it's different from a database schema migration. – Christopher Francisco Jun 8 '17 at 1:01
  • I get that it is not necessarily a schema change, but changing data can also be done the same way, no? A schema change may involve moving data from one form, in one table, to another form in another table (then perhaps delete the original); that's a fair amount of capability. One should be able to either do the same with a permission table, or just delete all the rows and repopulate with the latest. – Erik Eidt Jun 8 '17 at 1:03
  • That's question number 2, whether a cli task like $my-app sync:permissions would suffice by running it as part of the deployment script, or does it have know problems like when doing zero downtime, the minutes between the data changes and the system isn't completely deployed yet, etc – Christopher Francisco Jun 8 '17 at 1:08
1
  1. Should these kind of system data (not sure if the concept itself has a name) be stored in a database (for query-ability purposes) or is there a better standard way?

Yes, storing it in the database is a reasonable design choice.

  1. Is a cli task that checks for those system data values (in some config file) and sync them with the permission table (adding or deleting rows) a good approach, or is it not due to some reason I'm failing to see at the moment?

Yes, your database upgrade task is the right place for the data upgrade.

If you have zero down time requirements, then I'd suggest you add a version number or count to name of the role-to-permissions table so that two or more permissions tables can be both present in the database at the same time.

So, you add a new permissions table that only the new application knows about. This table won't hurt the old application, since nothing it uses has changed. The new upgrade knows about (and uses only) the new permission table, so you can switch over to it or run them both side-by-side.


Alternatives are adding a version number/count column to the permission table, but I think that'd be more complicated.

Another alternative is, if not dynamic, to keep the role-to-permission information in the code itself.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.