I would like to get an advice on how to tackle such a problem.

Currently we're using SQL Server as our database and the whole application is a fat, juicy monolith which we'd like to break down. There's one task that I cannot find a right solution for.

Users in our application can purchase certain data sets and based on these row level permissions, only this data will be shown to them. Only what they subscribe to.

Queries in SQL Look like that:

FROM dbo.Data AS D
INNER JOIN dbo.Permissions AS P
    ON P.CategoryID = D.CategoryID
    AND P.MarketID = D.MarketID
WHERE P.UserID = @UserID
AND P.CategoryID IN (/* category list here */)
AND P.MarketID IN (/* market list here */);

Which is fairly simple and works OK in a monolith. If we'd move to microservices, the goal would be to separate these two into distinct microservices:

  • Permissions service
  • Data service

And then in order to get permissions based on user selections I'd run this request:

GET /permissions/{UserID}/{Categories}/{Markets}

and then based on result of it, we get combinations of Categories and Markets, which then would be ingested to Data service and data would be queried like that:

FROM dbo.Data AS D
INNER JOIN @PermissionsFromService AS P
    ON P.CategoryID = D.CategoryID
    AND P.MarketID = D.MarketID;

However permissions service is quite fat and one user migt have access to thousands/millions combinations and that most likely is going to work slower than it is now.

I'd like to know whether there is a better and more optimal way to handle this.


dbo.Data table sample:

| CategoryID | MarketID | Val1 | Val2 | VAL3 |
|        515 |      812 |  123 |  456 |  789 |
|        753 |      917 |  123 |  456 |  789 |
|        163 |      987 |  123 |  456 |  789 |
|        156 |      222 |  123 |  456 |  789 |
|          4 |       99 |  123 |  456 |  789 |
|          3 |       19 |  123 |  456 |  789 |
|        ... |      ... |  ... |  ... |  ... |

dbo.Permissions table sample:

| UserID | CategoryID | MarketID |
|  68146 |        753 |      917 |
|  68146 |        163 |      987 |
|    ... |        ... |      ... |
  • 4
    What is your motivation to move to microservices? – Robert Harvey Jul 28 '17 at 16:52
  • @RobertHarvey Better code isolation and decoupling. Reduced amount of time to deploy and ability to deploy a small part of the application instead of the whole chunk. I'm working more on the backend (database) world, so we'd like to tackle similar problems.Currently our applications share the same database and it quite problematic to make changes when a removed column can impact another not intended applications. Hopefully that answers your question. – Evaldas Buinauskas Jul 28 '17 at 18:00
  • what exactly limits the permissions in the current example? whether the categories and markets exist in the permissions table? – Ewan Jul 28 '17 at 18:26
  • @Ewan 100% right. I'll add a data example to make it clear. – Evaldas Buinauskas Jul 28 '17 at 18:37
  • @Ewan Added data sample for reference. So in this case my just second and third lines from dbo.Data are brought back to user, because he subscribes to that category/market combination. – Evaldas Buinauskas Jul 28 '17 at 18:44

The basic problem behind splitting a monolith into microservices with regards to rights/authorizations can perhaps be understood via analogy. Here I go----

Imagine you are given a prescription for a drug and the doctor phones your first refill into the local pharmacy. You go to the pharmacy to pick it up. Here's how it usually works currently:

  1. You give the pharmacist your name and date of birth, and/or show your ID
  2. They use your information to find your order in the computer. The order tells them which cubbyhole your drugs can be found in.
  3. They go to the cubbyhole to get your stuff, make sure it is right, and give it to you.

Pretty common and straightfoward. Now image splitting the process into micro-pharmacies--

  1. You give the pharmacist your name and date of birth, and/or show your ID
  2. They use your information to find your order in the computer. The order tells them which cubbyhole your drugs can be found in.
  3. They tell you the cubbyhole number and set you around back where the drugs are kept
  4. You tell the cubbyhole number to a different employee
  5. They go to the cubbyhole to get your stuff and give it to you.

Doesn't seem so bad. But there are a couple drawbacks

  • Now you have to know about the cubbyhole number. If they ever change systems, you will now need to learn their new system. Feels like coupling.
  • On the way between step #3 and #4 you could forget the number, or misremember it, or purposefully change it. If that were to happen you would get the wrong drugs. If you were a junkie you could just try different numbers until you find some opiates. Feels like a vulnerability (it's actually a real-world example of an OWASP A4).

There are a few ways to solve it. One is to have the second pharmacy call the first and verify every order. But then you'd have to show your ID twice, and your pharmacies have to spend time on the phone with each other. Another way is to have the first pharmacy sign and watermark the piece of paper that has the cubbyhole number on it, so that it is tamperproof. This sounds pretty good, but the two mini pharmacies have to agree on what sort of watermark. Feels like digital signing with an agreed key.

So, back to your problem--

First of all, let me just throw out there that there are a couple business entities missing from your object model... here I go again....

  1. A customer is a type of user who has purchased a product
  2. A product grants one or more licenses
  3. A license is associated with a start and end date and a license type. Your business may have thousands of licenses but probably only a few license types, which you know ahead of time.
  4. A license type is associated with an end user license agreement and one or more categories of data.
  5. A category of data is associated with data

The reason I structure it this way, in part, is because I'm pretty sure business stakeholders need this level of detail when computing profit and loss, market analysis, usage metrics, etc. And also it will cover your OLTP system in the case of things like disputes, returns, multiple purchases, etc. If you think it is overkill you can just keep what you believe you need.

Anyhoo, given the above, I'd structure my microservices like this:

  1. Licensing service

    The licensing service accepts a customer ID and a nonce (probably your session ID) and returns a temporary token that is bound to customer and nonce/session. The token contains a list of the user's currently active licenses (including a license type identifier) and has the customer ID and nonce embedded in it, and is then signed.

  2. Product service

    The product service has the ability to (1) enumerate products available to a user, so a the user can pick what they want, and (2) accept a product code and a user ID (and perhaps a purchase confirmation number) to bind the product the user. Once the user has one or more products, he becomes a Customer.

  3. Data service

    The data service accepts a license token, validates the signature, customer ID, and session/nonce against the current context, and extracts the license type identifiers. It then submits the license type identifier(s) to a stored procedure which returns the associated data.

By structuring it this way you avoid exposing any identifiers that ought to be internal-only (i.e. you avoid implicit logical coupling), and you avoid anyone tampering with the identifiers and getting data they shouldn't be able to access. In addition, you avoid having to pass a list of market or category IDs in the stored procedure call-- you just supply the license-- resulting in a cleaner prototype and WHERE clause.

On the back end, the stored procedure joins the data tables to the license and license type tables to determine which rows to return.

  • I wish I could upvote this more than once.But there's one small caveat I'm missing. For instance I've purchased a product x that contains y categories, the current implementation allows customer to cherrypick categories and markets combinations, passing just a license key would not really work in this case, would it? – Evaldas Buinauskas Aug 1 '17 at 8:05
  • Not sure what "cherrypick" means in this context. Does it mean purchase? Or does it mean filter within the set of data that have already been purchased? – John Wu Aug 1 '17 at 9:31
  • Apologies for being too vague. It means filtering within set of data that customer has purchased. – Evaldas Buinauskas Aug 1 '17 at 10:15
  • My answer addresses row level access with regards to permissions. Ad hoc filtering is out of scope. I would think that such filtering wouldn't be associated with a user or license but with a session or even just a view, and would be accomplished via a where clause applied on top of the joins that enforce licensing. – John Wu Aug 1 '17 at 11:00
  • True, that's out of scope and should be a separate question. Thank you John! – Evaldas Buinauskas Aug 1 '17 at 11:01

One can keep the application the same query wise even if you go to a service architecture. The permission service should return back a token once the user authenticates. That token can be used for any future query as long as it does not expire and/or becomes invalid.

There will be an additional step to figure out which user the token was issued for during the data service step, but once you have the user that the queries will work the same and should be seamless to the end user.

This also allows one to control the token (expire it, etc.). Also, one could implement the token as simply an additional table if it is contained in SQL Server or they (the tokens) could be controlled/maintained elsewhere.

  • Apologies if I sound ignorant, but how having a authentication token would allow row-level permissions control? – Evaldas Buinauskas Jul 28 '17 at 18:04
  • Yes, it is an authentication token, but that token is also tied to user (user id) so effectively your SQL queries remain the same. One could simply create a table called user_token that would store tokens to users and then relate that. – Jon Raynor Jul 28 '17 at 18:47
  • I see what you mean now. My idea was to check to what kind of category/market combinations I have permissions for in advance and then based on results request data from Data service just for these combinations. But wouldn't it simpler just to replicate permissions and don't bother writing them to Data db? – Evaldas Buinauskas Jul 28 '17 at 19:43

Ok, so your auth/permissions service should return a token which contains the list of markets and categories the user has permission to see.

Then your data service can read that list and ensure that the requested markets and categories are ones to which the user has access.

You no longer need the join.

  • So this kinda becomes Application-side join? – Evaldas Buinauskas Jul 28 '17 at 19:46
  • hmm...? same where clause, no join – Ewan Jul 28 '17 at 19:52

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.