Row-level security

I'm working on a company and developing an app where user has a CRUD of various type of data. Data, as we all know, could be sensitive, depends on the type.

First, on my company we have different types of "roles". Different roles can see different data, depends on the type of the role. What sounds a bit weird is that we have to filter the data manually, every query we have on the system, has the where clause with the rules and it doesn't looks right to us. We are using hibernate to perform our DAO.

Second, on my app, it's a bit different but have the same goal. The user inserts data and they can only see their data. When they wants to, then a filter is made in every query. I'm using mongodb with morphia, I have to filter by "user" but, depends on the document I'm looking for, the user is on different level.

Example:

Every Fund have a user;
Every Transaction have a Fund;

If I'm listing the funds of the user I have the following document:

[
  {
    "description": "xpto",
    "user": {
      "login": "Someone"
    }
  }
]

If I'm listing the transactions of the user I have the following document:

[
  {
    "value": 1234.23,
    "date": "2017-01-01",
    "fund": {
      "description": "xpto",
      "user": {
        "login": "Someone"
      }
    }
  }
]

So, in the query of funds I have this:

datastore.find(Fund.class).filter("user.login", getUser().getLogin());

In the transactions:

datastore.find(Transaction.class).filter("funds.user.login", getUser().getLogin());

I made some research and find some results that aren't so clear to me:

Row-level security;
@Filter in hibernate

Row-level security, is better made by database. So every query that is made, the database looks at the rules and filters the data. Looks great after all, but, how am I going to tell the database what user? Every user of my system, has to be grant in the database? Has to be a user IN the database?

@Filter in hibernate, makes the same as i, but at least it's in the class. In every query I have to say what a have to filter. Is the last of my choice.

My goal is understand how is it working nowadays? How does trello know which cards are from the user? How does Facebook know which post is yours? How are they filtering the dataset? Is it normal do the filter in every query? Do they do it in the database or in the application?

Solution

I think too many questions are asked in this single Stackoverflow question. But let's try to answer most of them:

Row-level security I don't think you want to use row-level security on the database. This is only needed if you give the data users direct access to the database. If you give access only by API, you can let the API handle all the filtering (and limit the ways they can query it!). Because normally you want to keep you're database grants fairly static and not let the DBA change the rights every time (unless you only have a few big DB users, that don't change that often this would be an option).

Filter in hibernate I am confused by this one. You mention SQL/Hibernate en Mongodb/Morphia. Two different object mappers for two different database types. Also I would not call this option "Filter by Hibernate". Let's name it "filter by API" and use a facade design pattern. The facade class will be in front of both the Morphia and Hibernate API's and the API user can only access the database via this API.