More flexible JDBCRealm implementation ? (for ASP-style webapp)

[Soefara Redzuan <so...@hotmail.com>]

I've setup a JDBCRealm for Tomcat using MySQL. It works OK
but the database schema is not good for an ASP (application
service provider) model. For example, I would like several
companies to use the same webapp (each company should not
know of the other's existence) and each should be able to
create a user 'admin' and a user 'david' but in the way that
JDBCRealm is currently configured only one instance of any
user name is possible since it is the primary key in the users
table. Is there a better way to do this ?

I followed instructions found on many websites for setting up
a JDBCRealm.

The table schema is

create table user_groups (
    group_id int not null auto_increment,
    group_name char(24),
    parent_id int not null default -1,
    primary key(group_id)
    );

create table users (
  user_name     varchar(32) not null,
  user_pass     varchar(32) not null,
  user_groupid  int not null default -1,
  primary key(user_name)
);

create table user_roles (
  user_name         varchar(15) not null,
  role_name         varchar(15) not null,
  primary key (user_name, role_name)
);

And in Tomcat's server.xml I have this in the appropriate context,

  <Realm className="org.apache.catalina.realm.JDBCRealm" debug="99"
	  driverName="org.gjt.mm.mysql.Driver"
	  connectionURL="jdbc:mysql://servername/databasename"
	  userTable="users" userNameCol="user_name" userCredCol="user_pass"
	  userRoleTable="user_roles" roleNameCol="role_name"/>

And finally this in the webapp's web.xml,

  	<security-constraint>
		<web-resource-collection>
			<web-resource-name>ProtectedApp</web-resource-name>
			<url-pattern>/*</url-pattern>
		    <http-method>POST</http-method>
            <http-method>GET</http-method>
		</web-resource-collection>
		<auth-constraint>
		    <description>name the security roles that are allowed to 
access</description>
			<role-name>administrator</role-name>
			<role-name>user</role-name>
		</auth-constraint>
	</security-constraint>

The alternative is to set up a separate webapp for each
company that wishes to use our service but that really isn't
scalable and doesn't allow for users to self-register and
be up-and-running without administrator intervention.

Has anybody solved this problem ?  Thank you in advance,

Soefara.





_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: More flexible JDBCRealm implementation ? (for ASP-style webapp)

[Will Hartung <wi...@msoft.com>]

From: "Soefara Redzuan" <so...@hotmail.com>
To: <to...@jakarta.apache.org>
Subject: More flexible JDBCRealm implementation ? (for ASP-style webapp)


> I've setup a JDBCRealm for Tomcat using MySQL. It works OK
> but the database schema is not good for an ASP (application
> service provider) model. For example, I would like several
> companies to use the same webapp (each company should not
> know of the other's existence) and each should be able to
> create a user 'admin' and a user 'david' but in the way that
> JDBCRealm is currently configured only one instance of any
> user name is possible since it is the primary key in the users
> table. Is there a better way to do this ?

I'm not familiar enough with MySQL, but I'll toss this out.

Since you can configure your Realms on a Webapp basis, that may give you the
flexibility you need.

For example, if you were using, say, Oracle, or most any other database that
has a concept of User ownership for its tables (most DBs do, MySQL may do
this, I don't know), you could set up seperate Users or Schemas in the
database for each Client Webapp. This way, each client would have access to
their own versions of the tables. You would distinguish the realms by each
having their own login to the client database with a client specific
username/password for the database.

If you'd rather share the tables (for whatever reason), perhaps you could
make views on a master table that's limited by the client id.

These views would be placed in the appropriate client schemas and shadow the
master table in a central schema. Something like CREATE VIEW
CLIENT1.USERS(user_name, user_pass, user_goupid) AS SELECT user_name,
user_pass, user-groupid FROM MASTER.USERS WHERE CLIENTID = 'CLIENT1'.

Of course, you can always run extra instances of MySQL, but that seems
excessive.

Finally, if you look at
$CATALINA_HOME/src/share/org/apache/cataline/realm/JDBCRealm.java, it looks
pretty darn simple to tweak that to do whatever you want, or, better, to
subclass and change the relevant methods (not many from the looks of it).
The only fear here is that the TC team can change JDBCRealm behind your back
in a later release.

Stick the pertinent webapp specific entries into ENV-ENTRY, and you can do
all sorts of scary things I would think.

Regards,

Will Hartung
(willh@msoft.com)




--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>