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?

The longer you stay, the worse it will get (in terms of your being up to date on current technology). Go now.