Yeah, OK. I see that dynamic security assessment is one way
of having connection pooling work while keeping authorisation
definition centralised in the database.

This requirement comes about because, at the moment, a
"connection" represents both a users session with the database
and also the communications session with the server process.

It seems to me that a better way of expressing a solution to
the n-tier scaling problem is to separate the two concepts.


I will use the term "session" to represent a users session
with the database and "connection" to represent a communication
connection between two machines. Multiple sessions can run
over a single connection. (Some discussion could be needed to
get the terminology correct for best implementation.)

- A user's client application would create a "session" request
and pass it to the client library.

- The Firebird client library would see that "session" request
and, if it does not already have one, would create a
"connection" to a server. That server may be a middle tier or
an actual database server. The session request is passed to
the server.

- Middle tiers can act as pass-through or can do something
more interesting with the traffic that comes through.

- Database servers can have less communications connections
while retaining a full list of active user sessions - from
which authorisation etc can be managed. Presumably such
session information will want to be light-weight, in order to
easily manage a very large number of sessions.


How this effects the caching of prepared statements etc I am
not sure. I imagine that a large part of a prepare is the
establishing of access rights - hence caching would be by
session. This has resource implications so it may be decided
to cache without rights, or cache rights information with the
session and cache generic aspects globally. This is all
implementation detail. The main point is that with a user
session object it would become possible to validate a users
rights at prepare time for a session because now the database
"knows" about a given user session.

Note also. Another reason for middle tiers is to offload
various resources to distributed servers. It could be possible
to create (trusted) cooperating servers that allow some of the
caching to take place at the middle tiers while still retaining
the central definition of rights etc. In such situations a
client need never know whether it is talking to a middle tier
server or not.

Such a solution could also be of benefit to installations that
do not use n-tier. For example I have an application that does
make use of multiple database connections (sessions). If
sessions become less expensive - by operating over the same
connection to a server - then that application would benefit.

Note that establishing a "session" could/would carry information
about the end user machine (address details etc) so that the
servers can report, and perhaps perform validation etc, against
such details. This ability would be lost if n-tier becomes a
simple pool of connections.

--
Geoff Worboys
Telesis Computing