[ZODB-Dev] RelStorage with Oracle and separation of rights

[Darryl Dixon - Winterhouse Consulting]

Hi All,

Part of the setup of our Oracle RelStorage environment involves the DBAs
wanting to separate ownership of the schema from the rights to actually
use the schema. In other words, user A owns all the tables etc that
RelStorage creates, but then when it comes to actually making use of them,
the application (Zope) uses user B for the connection. Apparently this is
a standard Oracle risk-mitigation strategy and the DBAs are quite firm on
this requirement.

The way they achieve this is by creating what I would call a 'shadow
schema' for user B that consists of a big pile of synonyms and grants
pointing back at the 'real' user A schema.

This doesn't work with parts of RelStorage. For example, it seems that
RelStorage will unilaterally try to 'CREATE TABLE pack_lock' even though
that table already exists. When this code runs as user A, Oracle seems to
treat this as a no-op and continues on its merry way. When this code runs
as user B, Oracle throws a fit and complains that
"cx_Oracle.DatabaseError: ORA-00955: name is already used by an existing
object". Because presumably for user B, 'pack_lock' already exists, but it
is a *synonym* not an actual, for-real table. I suspect that other such
situations may arise involving, eg Indexes or Sequences.

Is there any straightforward way to resolve this?

regards,
Darryl Dixon
Winterhouse Consulting Ltd
http://www.winterhouseconsulting.com