4

My team is designing a RESTful API in JAX-RS, and we have the need to restrict the availability of certain rows in our database based on the ID of the authenticated "Operator" (our word for User). In other words, an Operator should only be able to access entities that are under his jurisdiction. At the beginning of each request, we authenticate the Operator making the request, allowing us to provide security features based on the Operator's role and ID.

Authorizing endpoints, endpoint methods and even entity content (what gets serialized) has proven fairly straightforward, but row-level authorization seems to be a big hairy beast.

Note that we are not using Spring, and have no plans to use Spring Security in our project.

We've come up with a few potential solutions, but we're unsure which is best. It's also very possible that there's a solution we haven't considered; I'm open to anything at this point. Here's what we've got so far:

  1. Database level implementation (as discussed in this post). This would presumably involve using the request's security context to pass the Operator's ID to the database on each request. I'm unclear on the specifics of implementing this approach, so if this is the best way to do row-level security I'd appreciate some further advice. For example, it makes sense how this would work for entity retrieval, but how would we modify row-level permissions for newly created or updated entities?
  2. JPA level implementation (as discussed in this post from 2008). This might involve generating a parameterized Hibernate filter into which we'd feed the ID of the Operator making the request. I've never used Hibernate filters so it's very possible that this idea is off-base.
  3. Facade level implementation. We've actually given this idea a decent amount of thought since we weren't aware of options (1) and (2) until recently. This would involve performing joins between our tables to construct a Criteria API predicate that would restrict our queries to only include those entities accessible by a given Operator. This is essentially the "manual" approach, as far as I understand it, and seems far from ideal.

It would be awesome if someone familiar with JAX-RS and/or database security could help make some sense of all of this.

Here's our tech stack for the project (at least as far as is relevant to this problem):

  • Database: MS SQL
  • JPA Provider: Hibernate
  • JAX-RS Implementation: RESTEasy
Community
  • 1
  • 1
Pablo Napolitano
  • 116
  • 1
  • 8
  • 1
    You can mark off the DB as a way to implement row level security unless you are using SQL Server 2016: Row Level Security (RLS) is a concept that provides security at the row level within the database layer, instead of at the application layer. RLS is accomplished by using a function and the new security policy feature that is being rolled out with SQL Server 2016. – Neo Oct 14 '16 at 15:13
  • Why do you need row level security? – Neo Oct 14 '16 at 15:15
  • Is using SQL Server 2016 an option? – Neo Oct 14 '16 at 15:17
  • @MisterPositive It's critical to our project that each user's information be accessible only to that user and all users above that user in a hierarchical tree. It's possible I misunderstand what row-level security is intended for, so feel free to correct me if I'm on the wrong track! – Pablo Napolitano Oct 14 '16 at 15:22
  • You may want to consider a Group / Role approach. If you actually have to look at each ROW of data being returned by a query it will be very expensive from a resource perspective. – Neo Oct 14 '16 at 15:24
  • @MisterPositive We do have control over the version of SQL Server we use, so it's technically an option, but we don't currently have a license for 2016. – Pablo Napolitano Oct 14 '16 at 15:24
  • @MisterPositive Would you mind elaborating on the Group / Role approach? We already have roles, but we use these to authorize access to tables and CRUD methods rather than particular entities. As I understand it, it would be more difficult to dynamically assign, change and remove permissions to individual entities if we authorize using role instead of identification. – Pablo Napolitano Oct 14 '16 at 15:32

2 Answers2

1

Use SQL Server 2016: Row Level Security (RLS) is a concept that provides security at the row level within the database layer, instead of at the application layer. RLS is accomplished by using a function and the new security policy feature that is being rolled out with SQL Server 2016. ( If you really need row level security )

Neo
  • 3,309
  • 7
  • 35
  • 44
0

I've thought about this subject more than I would have liked but after lots of pondering, I have formed the following opinion: it must be done through joins, on the database level. From the database POV, there is no way to tell who is the "owner" of a row other than by following a chain of foreign keys to arrive at a certain user (operator in your case). It makes sense that there is no straight forward declarative way to define row-level security (in code), because annotations can not be placed on "rows" (instances of entity classes), only on code elements available at compile-time (i.e., methods). Hence security at the row level must be done programmatically and seeing as the "jurisdiction" is a business concept, it can only be enforced by joining the entity to the operator matching the current user (or the group to which they belong). I don't think there will ever be a better solution, unless JPA would introduce an annotation to mark a relationship as an "owner of" concept, and an annotation to mark an entity as an "owner". That way, perhaps JPA could automatically do the joins for you... but until then, just pass the current user ID to your DAO/repositories and add a bunch of joins in your JPQL/criteria query. This will filter out rows from other jurisdictions.

RDM
  • 4,986
  • 4
  • 34
  • 43