Our web application has a complex access control system which incorporates role-based and object-level privileges.

In the business logic layer, this is implemented by a component that obtains (and caches) all the necessary data with a batch query and computes the user's type and level of access to any object in the system. (A future optimization would be conditional batching based on the data we need for a particular request.)

However, the view privilege logic in this component is duplicated elsewhere in database queries. (We need to hide data in listing screens that the user does not have privilege to view.)

How can we reduce or eliminate this duplication of logic between the application access control component and our database queries?

Two approaches come to mind. I'm sure there are others.

  • Check view privilege in the application for each row that comes back from the server via queries from listing screens.
  • Move more of the access control logic into a stored function that can be called from the queries as well as the application code.

Answers should defend the merits of the proposed method over other methods. For example, if my second suggested approach is desirable, why? If you have suggested a third approach, why does it win over both my approaches?

2 Answers 2


My answer would be approach 3:

Use the same Objects you do in business layer to back the listing screens.

You would:

  1. Remove the duplicated logic from the DB, no need to remember and make duplicate fixes
  2. Have all business logic in the same language, no translation in T or PSQL required
  3. Reuse existing code

I believe that this beats both approach 1 and 2 because it will require less coding, and should also require less regression testing.

  • If I missed something in your original post, it is because I did not have enough reputation to ask clarifying questions. Jul 29, 2011 at 13:54
  • Thank you for the answer! I'm curious how your suggested approach is different from my first suggestion (i.e. check the privilege for each row, in the application). Jul 29, 2011 at 14:07
  • @Matthew The comment, "the view privilege logic in this component is duplicated", to me implies that the objects you spoke about were already implementing the view logic. Assuming that the objects were already doing so, then the rest of the post implies to me that your data listing screens are not using the objects, but instead the DB results more directly. Jul 29, 2011 at 14:25
  • Ah. The component I talk about is independent of the data objects. What you are suggesting is putting the view privilege logic in the data objects themselves. Is that right? That is a different approach than I suggested (+1 for that). Jul 29, 2011 at 14:52
  • @Matthew I'd be dishonest to claim that as my idea, although I think it will work. How many tiers is your application? Maybe adding a little more information on your current implementation would help others formulate more and better approaches. Jul 29, 2011 at 17:49

I'm struggling with some of these issues currently, and was looking for other people's approaches when I came across this question. I thought I'd toss some thoughts up here just in case they help someone coming down this route later.

The approach I've decided upon has the following:

  • The model layer has no access control logic in it, with the exception that model objects respond to a method that takes a current user as a parameter and returns an access control object.
  • The access control layer objects contain references to the model object and the current user object and responds to messages such as can_show? (yup, this is in Ruby), can_edit?, can_destroy?, can_show_some_attribute_name?, can_modify_some_attribute_name?, etc. Note that this means that there is field level access control.
  • There is a decorator layer for the model that is used for the views. Decoration requires that a current user be supplied. The decorators intercept all attribute accessor methods and check them against the access control object before permitting the operation. Read operations return nil if the access is forbidden and write operations raise an error in the same situation. To simplify use, the decorators delegate all methods starting with can_ to the access control object.
  • I'm currently attacking the problem of implementing a query system. For instance, if a user chooses to hide their phone number from public users, then a query to find users with a given phone number shouldn't return that record. Similarly, if one requests that the results be sorted by phone number, one shouldn't be able to deduce that a given phone number must be between those for adjacent records. For now I've decided to simplify implementation by assuming that the SQL queries will be done without respect to access control (which should return a super set of results) and then the query system will filter the results (including ensuring all conditionals are met and re-doing the sort) using the access control object to check access. So the query system uses the Builder pattern to both store the query logic for its own implementation and to pass it along to the ORM.

The query part seems to be the most difficult. I'd like to be able to translate the record level access control directly into SQL queries for performance reasons, even if I'm unsuccessful in also translating the field level access control.

  • Good thinking. I like your use of decorators to intercept and check access. I agree that the query part is the most difficult. Part of the problem in the question I posted is that we want to use efficient DB level paging, which meant we didn't want to defer the access control check to the application layer. May 14, 2013 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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