[Zope-DB] Per-user connections

Charlie Reiman creiman@kefta.com
Wed, 4 Dec 2002 12:09:57 -0800

> -----Original Message-----
> From: zope-db-admin@zope.org [mailto:zope-db-admin@zope.org]On Behalf Of
> Yves Bastide
> Sent: Wednesday, December 04, 2002 11:49 AM
> To: Zope-DB
> Subject: [Zope-DB] Per-user connections
> Hi,
> For reasons best left in the dark, I'd like to have per-user database
> connections.  Since this is in an application where (most of) the SQL is
> already done in a module, I see two ways of doing:
> * create a Connection object, stored in the Sessin, when the user is
> authentified; to use ZSQL methods, subclass the DA to take the
> Connection parameter in __call__
> * don't count on using ZSQL methods; use a class derived from the one I
> use outside of Zope, and from Shared.DC.ZRDB.TM's TM to know when to
> commit/rollback.
> Can people familiar with Zope's machinery comment on these approaches
> (or give me a better one)?
> Thanks!
> yves

I have done this and I can say it's pretty damn hard. Here's what my system

1) extend exUserFolder to provide a new authentication source. This
essentially declares a user authorized if the supplied user and password can
log onto the Oracle databse. A side effect of this is a user specific
database connection object I call a db_pair (the name is historical).

2) Hack the bejeezus out of DCOracle2 connection to:
  a) Accept incoming db_pairs from the auth source
  b) Manage a cache of user keyed connections (binding name to db_pair)
  c) Still maintain an anonymous connection so the admin can use the system
  d) Use db_pairs everywhere instead of raw connections
  e) Extend DCOracle2's UI to support various new options.
  f) Modify query in db.py to actually get the user specific connections.

Even with all this, I'm still having problems. I often get double login
prompts and the timeouts between the connection cache and authentication
cache need to be tightly coupled. Worst of all, I can never upgrade my
DCOracle2 product since I've hacked it too much from the original source.
exUserFolder is probably upgradable though.

Good luck. If you get a clean solution, please share! I made an early
version of my system available but now it is a little to bizzare to share.