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

[Shane Hathaway]

On 10/30/2011 07:05 PM, Darryl Dixon - Winterhouse Consulting wrote:
> 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?

I think you're saying your DBAs want to be in charge of all DDL (Data 
Definition Language like CREATE TABLE) while apps can only use DML (Data 
Manipulation Language like SELECT, INSERT, UPDATE, and DELETE). Setting 
the create-schema option to false should do the trick. I wonder if 
you'll run into problems with OID management, since some DDL is required 
there, but other than that and creating the schema, everything 
RelStorage does with Oracle is (or should be) DML.

BTW, I wonder why your DBAs bother with a shadow schema. They can simply 
revoke the permission to use specific DDL instead.

Shane