Aleksey Karyakin wrote:

>An application server starts under its own identity (A) and initially
>authenticates itself (A) to db server. A db server checks if A is a
>trusted identity in such a sense that it is allowed to use a defined
>set of roles (R1, R2 for example). This can be formulated as A is
>authorized to use roles R1 and R2:
>
>create user A identified externally;
>grant role R1 to A;
>grant role R2 to A;
>
>Suppose the app server receives a user request. It authenicates a
>user by some means, and determines its identity. Based on the
>identity it chooses role R1 or R2 and sends it to the database. The
>database trusts the role and performs further security checks based
>on that. The database doesn't have to perform any authentication
>since it trusts A to use roles R1 and R2.
>
>However, this security model can be equally implemented if app server
>performs security checks by itself and doesn't send database commands
>that could violate the choosen role permissions. From the database
>p.o.v. it's no difference if the app server _voluntary_ restricts
>user's requests or _voluntary_ sends role name that would futher
>restrict its permissions.
>
>
>

A fair point. There are two reasons that come to mind. The first is
that declarative security in the database is more robust, easier to
implement, easier to test, and easier to maintain that programmatic
security in the application code. One of my customers, for example, is
large law firm with an web application that spans everything from their
public web site to comprehensive document management. Exposure of
confidential client information, however, would lead to potential
disbarment, and, understandably, no lawyer wants to have his license to
practice law dependent on a junior application programmer's skill and
diligence. The ability to declare enforceable security policy at the
database level was an absolute requirement.

The second reason is that when row level access control is implemented,
the set of active roles will determine what rows are seen, not just what
operations are permitted. The queries resulting from recursive
application of per-table filters are simply too complex to be written by
humans whether junior application programmers, experience database
experts, or even senior database architects. (A straightforward single
line query can easily expand to a six page monster when a half dozen
role-based filtersets are expanded.)