web applications - How to expand single user WebApp to multiple users -

there similar threads without concrete solution , thought better start new one.

i facing situation have webapp hosted in resin (just tomcat guess). far have been developing app using db4o since i'm alone , needed complete app asap, have db users , db app data single user (me), app done i'm move postgresql , thinking db per user if db holds data multiple apps since handle kinda confidential data , thought having separate db best (security wise). there rudimentary session management stores user data id in browser. wondering how can expand multiple users/db.

i thinking expand listener class keeps context data pass right db object app instance, or maybe set filter purpose.

.update.

i wanted give more insight of have.

i have:

context holds reference objects, 1 of objects connects db , checks user , password.

presentation servlet (httpservlet) mapped "/" has login form post /login.

login servlet (httpservlet) mapped "/login" checks httpsession user password attributes against respective object rests in context, if there match sets httpsession attribute holds userid , redirects user app located @ /index-debug.html if not creates new html page login form again.

authorization , authentication filters mapped /index-debug.html verifies httpservletrequest userid attribute , checks whether or not user has permission access app.

finally db bean in charge of reading , writing webapp user data db. when execute method in webapp cp2javaws matches method respective method in bean, problem bean has static database , far allows 1 user @ time.

what somehow allow db bean instantiate once per user , read , store corresponding data depending of current logged user.

the idea of 1 db per user discarded don't know how pull off.

you mentioned postgres database backend , has feature called schemas. have 1 physical database , multiple schemas inside database. experience comes rails, concepts same. method avoids mashing people's data in same set of tables sounds primary concern. know you're using java, watch talk on multi-tenant apps in rails background guy naor on how works, trade-offs, etc.

here concrete steps started down path of using postgres schemas:

1. there public schema in postgres default. put user authentication tables , other generic meta-data tables user logins, etc. see postgres docs more info on how schemas work

2. come naming convention each schema create (e.g. user_001, user_002, etc.). pre-allocate bunch of empty schemas tables setup , when user registers or logs in first time, assign them schema , store schema name in user record in public schema , in user object have in httpsession. there no need run table creation scripts first time user - performance drag in web app. need stay ahead of rate of new users. example have bunch of empty user_standby_1 ... user_standby_100 schemas , when logs in or registers, run sql:

   myquery = "alter schema user_standby_? rename user_?"; myquery.setstring(1,standby_id); myquery.setstring(2,user_id);

3. when create db bean (use superclass this, see below), pass in schema name user object httpsession, execute sql before every operation isolate them schema only:

   myquery2 = "set search_path ?";
myquery2.setstring(1,user.search_path);

4. if have empty full schema in public, want omit public search path otherwise have 2 tables same name in search path. if want users search path include set search_path user_001,public after creating tables, drop data tables public other users , meta-info need.

5. for maintenance, write script can run via command line drop empty user_standby schemas, create new user_standby schemas , equivalent of rails migrations java minor table changes.
6. for large maintenance activities might best create new schemas, e.g. user_v2_001, each user , write scripts migrate data in. depends on how complex changes tables.

if go alternative route , have users data in 1 set of tables, best approach have user_id in every table , write sql use every time. if use traditional normalization , joins user_id, better make sure don't accidentally miss join or users start seeing each others data.

the postgres schema feature allows lock users access own data. after figuring out basics, use superclass in java write step 3 above every mytabledbbean extends masterdbbean , uses super class constructor isolate search path user's schema. have 1 place in code done , don't have remember every table or query more business logic.