Using Granular Access Control - Implementing Database Security and Auditing - page 177

6
Using Granular Access Control
Once upon a time, when we had client-server systems, we would assign a
separate database login for every end user accessing the application. The
application client would log in to the database, and the user model in the
application relied on the database user model and privileges definitions.
Some permissions were managed by the application layer, but others could
be enforced directly within the database.
Along came three-tier architectures, n-tier architecture, and application
servers, and we suddenly found ourselves with multiple user models. The
application user model and the database user model drifted apart. Applica-
tion logins are no longer commonly associated one-for-one with database
logins. Instead, the application server manages a connection pool of data-
base connections. Every time an application thread needs to access the data-
base it requests a connection from the pool, uses it to execute queries and/
or procedures, and then surrenders the connection back to the pool. Each
connection in the pool is logged into the database using the same database
login. Therefore, all of the database authorization mechanisms become triv-
ial and cannot be used effectively (or even used at all!).
This is not a healthy situation, and remedying this issue is the main
focus of this chapter. However, database connection pools are not the
enemy, and you should not try to move away from them, because they sim-
plify the architecture and allow for much better performance. Therefore, in
aligning the user models, I certainly do not mean to suggest that you should
do away with the notion of reusing database connections, getting rid of the
application user model and going back to a one-to-one relationship
between application logins and database logins. Among other reasons, this
is completely impractical in the many Web applications where there could
be hundreds of thousands and even millions of users. Instead, aligning the
user models simply means that when the application gets a connection from
the connection pool, the first thing it should do is to communicate with the