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

[Darryl Dixon - Winterhouse Consulting]

Hi Shane,


> 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.

[...snip...]

>> 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.
>

Thanks for the informative reply. We seem to have got this working with
the setup described (everything linked via synonyms and a different user
for the Zope connection from the one that owns the objects in Oracle).

The primary changes made were:
1) create-schema false in zope.conf
2) A set of GRANTs in Oracle for user B to access the synonyms which were
more restrictive than full ownership (I'll talk to the DBAs tomorrow and
send the full list)
3) TRUNCATE TABLE changed to DELETE FROM (see below)

The last change was to solve an intractable problem with the packing code
- the Oracle scriptrunner uses 'TRUNCATE TABLE' when packing, but in
Oracle, one cannot TRUNCATE a synonym, even with complete permissions to
the source object. Instead I altered the code to use DELETE FROM which
seems to perform adequately for our needs.

Everything now seems to be working successfully. Thanks all (especially
Shane) for a great product and the assistance at this last hurdle.

regards,
Darryl Dixon
Winterhouse Consulting Ltd