Duplication of view access control logic in database queries and application component

Question

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?

Answer 1

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.

Answer 2

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.