Subject Re: Database Culture and Progress
Author Roman Rokytskyy
> That's what I meant :) 'Client' is quite confusing in n-tiers.

Oh, yes! :)

> Which authentication methods are used?
> Where security accounts are stored?

Outside the application server and the database - JAAS is responsible
for this.

For example, you can specify in the web application deployment
descriptor that some context (in other words part of the URL) requires
a role "manager". Then web container (part of the application server)
checks whether user was authenticated, if not, it redirects the
request to a login form, wait for user submits the form, authenticates
him, and later checks the role to decide whether to let the user into
that context or not. Authentication method is specified in the config
file, usually that is part of the application server configuration.

> 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.

100% correct.

> 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.

Much more harder. When I deploy a session bean I have a possibility to
specify that some method invocation requires role "manager". However
this call can cause multiple database calls. Also the row-level
security that Jim mentioned is a good argument.

> BTW, do you think this model is different from SQL standard roles,
> except several roles can be active at a time?

In "my" model there is only one active role at a time, this differs
from the "group" where user possesses multiple roles at the same time.

> In the other scenario the app server passes the security context
> obtained from user authentication, instead of authenticating itself.
> That would imply that:
> -- app server is not authenticated. The database sees only end
> user's identity.
> -- each time the application starts actions on different user's
> behalf, it should propagate user's security context to the database,
> since that may be expensive, a permanent security session may be
> helpful;

That might be interesting, however it seems to be harder that the
model above.

> So the application does not choose roles? If so, how they are
> determined?

Well... as you have already said, concept of the "client" is very
fuzzy in n-tier context. Same applies to the applications. The
required role is specified in the deployment descriptor. Usually it is
created by the application developer, however the deployer can
override it during deployment. So, the database server has to trust
the environment and the application deployer.

> Is there some system-level mapping of user identity to a
> set of role names?

That is the responsibility of the JAAS. When user is successfully
authenticated, JAAS modules store the public and private credentials
of the user in the subject. The database role is the credential.

> How that mapping is stored and how it is
> protected so that the database server can trust it?

Mapping storage does not matter - it is somehow mapped during login
and application server takes care to pass the right subject at the
right moment. Sure, it can happen that somebody deploys a malicious
component that will intercept the subject and store data somewhere.
This is the same as when you put a plastic bank card into the cash
machine - you trust that the card reader is not forged, but you never
can be sure.