Hi All,

Sorry if this is drifting off topic but it is a question that's a little
about JayBird, a little about Firebird, and a litttle about Tomcat (or your
favorite servlet container/JSP server).

> > I'm curious about the use of role based security for applications like

this

> > rather than name based.

I didn't pose the question well. My question stems from the trouble I had
converting a Java app originally using Interclient to a web app using
JayBird.

I guess a full blown J2EE server solves many of these problems. Perhaps
someone with that kind of experiece with JayBird can comment?

The Java app used direct user logins to the database in a local network. It
also used a connection pool. You could use a pool there since all the db
connections in any given use of the program were to the same user/role.

To "webify" the app it wasn't practical for a lot of reasons to have the
users log in with their db names, security being the most obvious.

So we ended up using the typical single generic username for all database
accesses and granting that user sufficient permissions to do anything to the
tables and columns involved that any web user might ever need to do.

Then we used the roles capability built into the JSP/Servlet spec (in
Tomcat) to restrict access to certain web pages to specific users with
certain Tomcat roles.

This was good because it let us use roles with a connection pool, but it was
bad because errors in Tomcat or our setup could allow users undesired
access. It was also good from a security point of view since only Tomcat
passwords are transferred across the net and they can be protected with SSL.

To get even more fine grained and and provide access control on a
record-by-record basis we put an "owner" and "group" field in each record
and use program logic to implement permissions on records.

The upshot of this is that Firebird Roles are not used at all or would only
be useful for for gross access security.

Andrew's question about using Firebird roles with JayBird and JNDI data
sources got me wondering if it would be practical to have multiple data
sources, each with a different Firebird role with each user only allowed to
attach to the data source having a role appropriate to their permission
level?

That would allow Firebird to enforce access restrictions down to the table
and column level.

If you did this you would have to let Tomcat manage the pooling rather than
JayBird because I don't think FBWrappingDataSource can connect with roles,
can it? That is more in line with J2EE anyway.

Would the web app be able to look up its create, modify, update, and delete
permissions from one of the system tables of the db, or do the JayBird
metadata extraction methods work well enough for this?

If not you would have to put what the permissions are into each data source
and keep that coherent with the database tables to prevent errors.
Otherwise you would just have to let users try things they don't have
permission to do and trap errors. That would be both annoying to the user
and inefficient for the system.

I guess a disadvantage of this would be that your web app would only work
with other SQL databases that implement roles.

Rck