Subject Re: User name SYSDBA
Author johnson_dave2003
> In both cases, the user's application security is known when they
> in; so why can't the same apply to the database? (Log in and go)

Bingo ...

The user logs into the web application, but for any given transaction
they will be using one of about 50 connections from a pool that is
shared with 1000 concurrent users.

When the transaction is started, a connection is claimed from the
pool, which may or may not be the same as the connection this user
claimed in the last transaction, and which may or may not have been
used by another user since the last time this user used it. When
they commit, the connection is returned to the pool for other users
to claim.

In micro-scaled systems, the nominal cost of making and breaking
connections is just that - nominal. In even upper end small scale
systems, it is a serious bottleneck. When you add the overheads of
preparing queries to every connection, it becomes quite serious,
particularly on wIntel type hardware.

In web based architectures, there is no permanent connection between
the user and the application. Connections are transient and shared,
and context information must be looked up on every request. On this
Yahoo groups web page, for example, they use a "cookie" to keep your
context information.

When you post to yahoo via browser, the server requests the cookie
and uses that to provide the illusion of a seamless dedicated
connection. They achieve the responsiveness that they do because
every submission does not have to establish a new connection - the pp
confirms that you have authority to do what is being asked, then it
calls prepared statements that has been prepared for over a month to
do the work you are requesting.

Behind the scenes, the millions of people viewing and posting to the
Yahoo groups application via either the web or the email interfaces
are using a small pool of just a few hundred dedicated connections to
the backing database. The connection I retrieved data on is unlikely
to be the connection that I post this reply on.

Yahoo's security model is primarily implemented in their
application. It is likely that a person with knowledge of the one ID
and password that the application uses would have god-like authority
on the data in the DBMS that backs this email group.

With Jim's proposed model, the application does not need to know the
user's security any more. The application only needs to know the
user and the role that the user wants, and to provide that with the
start-transaction information. As a user, you do or do not have
authority to a role. In the role, you have certain specific

The PAM approach means that low-end systems can use lighter weight
security, while larger and more robust security requirements can be
enforced in the DBMS with minimum impact on performance.

Fortune 500 companies have spent millions of dollars putting this
level of security in their applications, so they are incurring the
performance cost of authorization already. And they have to do it
for every application that they use.

Making it available from the database means that applications can be
simpler and the database more secure. Making it pluggable means that
it is optional, so those that prefer the older style of database
security can take it or leave it.