[Zope-DB] Per-user connections

Dario Lopez-Kästen dario@ita.chalmers.se
Thu, 5 Dec 2002 09:21:05 +0100

Hello, I am interested in this too.
We currently do not have per-user oracle accounts, but this will probably
change in the future since we need to track actions from particular user
made in the database, and we are thinking of connectin Oracle to our
kerberos servers for easier user maintenace.

Here is an idea for soemthing that might work:

What we have been doing is actually an attempt to speed up the execution of
ZSQL-methods, and as a side effect it might be used as a per-user db
connection mechanism, though we haven't tried it as such yet.

We are using an external method that accepts a sql-string, an oracle
shema/user name, it's password and which then executes the sql, returns the
results as a simple dictionary to zope (he speed in crease on large result
sets is dramatic, btw).

Since we have to pass the username and password to the external method, this
can be used to create per user connections for every sql-query. After
execution of the query we close the connection. This opening and closing of
connections seems to work better than using one of the normal DCO2
Zope-connections - they tend to hang on the db after a while, forcing us to
restart the zope-server (this is in part due to insanely wierd SQL coding -
we are fixing that now).

How this is going to work in conjuction with kerberos (which is what we
need) i have no idea yet, but for "normal" SQL*Net usage it ought to work.

There are som unresolved issues here: in a non-kerberos environemt, we need
to know the username and password of the user, and then we need to store it
in a way that that can be re-accessed for as long as the user's session is
active - Charlie's db_pair implementation is interesting.

Also, we are not really sure if there is a problem with closing and opening
connections like that - according to a colleague who looked at this briefly,
it should not decrease performance. If I understood her correctly, she says
that our oracle instance is configured to manage large amounts of short
connections and that SQL*Net implements it's own "pre-fab" connection

From: "Charlie Reiman" <creiman@kefta.com>
> 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.

I can provide the basis for the external method and some test PythonScript
scripts we are currently building (they are not done yet) - the idea is that
we pass the name of a ZSQ-method, the arguments it needs, username/password
combo to the PythonScript method and then it renders the zsql-method into a
string that it passes to the external method for execution.

Let me know if anyone is interested in this.