It takes over 100 ms to establish and unauthenticated sockets connection
across a 100 mbit line. Firebird's connection might be cheap, but it
can't be faster than the underlying wire protocols.

For this reason, I think that in a pooled connection scenario it would
be best to have a place in the wire protocol for the application user
+role rather than depending on the user and/or role of the initial
connection for all security.

Unless and until the statement user authorization is separated entirely
from the concept of the initial logon connection authentication and
prepare authorization, we will return to the status quo, which is server
level passwords for the database (i.e. no security), and implementation
of security in the application.

In this email group, when I function through the web interface, the
connection that I viewed on is shared by a million other users. It may
or may not be the connection that I use to reply on. At no time does
the back end server need to stop and do a prepare, because the
statements have been prepared and reused since the application was last
restarted, which could be several months.

Users do not ever have connections "assigned" to them for more than a
few statements at a time.

For pooled connections to know the user "assigned" to them for a given
statement, we must start by separating the concepts of the user and
connection. In today's world, the user is, effectively, the connection.
In a pooled connection environment, the connection is owned by an
application and has no contact with the end-user.

In medium to large environments, where a few hundred connections must
serve potentially millions of concurrent users, the overheads of
establishing and cleanly dropping connections at the sockets level would
make a separate connection per user not viable - installations would
revert to security implemented entirely in the application layer as they
do today because of performance.

For effective DBMS security that allows for pooled connections, the user
+role must be carried in the wire protocol directly on every start-
transaction, and every statement execution must allow for the security
API call that authorizes the user+role to execute. The security plugin
is responsible for implementing a security architecture (for example -
SQL92 or LDAP).

In SQL92 security, the user that established the initial connection
establishes the security. This level of security is too coarse in the
world of pooled connections, but the security plugin for this is
conceptually simple (most API calls simply return "pass" - 70ms per
million calls on 800 MHz desktop).

In a pooled connection environment, the user that established the
initial connection needs rights to log in and to prepare statements, but
does not need rights to execute statements or view data. The
application user will be tied to a connection for the duration of a
single transaction, so the transaction user+role must override the
connection user ID. No prepares should (generally) occur during any
end-user transaction. On commit/rollback, the connection authorities
should revert to the connection user.


On Sun, 2005-08-28 at 19:43 +0000, Firebird-Architect@yahoogroups.com
wrote:

> >Shouldn't each user have a connection which is assigned to them, so
> that
> >the database knows the user's true identity?
> >
> >If the cost of the connection seems too high, them use a pool but
> relate
> >the db connection to an web application session. Most web
> application
> >maintain some basic server-side session information.
> >
> >
> A cheap database attachment/connection is a very virtuous
> characteristic, but one that requires great attention. The Interbase
> attachment used to be dirty cheap, but has gotten quite expensive
> over
> the years and desparately needs a cleanup / shake down. A connection
> starts with virtually no state, so creation should be dirty cheap.
> In
> use, a connection acquires state and resources to be released after
> use. In theory, at least, it should also be cheaper to creation a
> new
> connection than cleanup and reuse a cached one, though current
> practice
> may not bear this up.
>
>