Re: [ZODB-Dev] RelStorage with Oracle and separation of rights
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 ___ For more information about ZODB, see http://zodb.org/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage with Oracle and separation of rights
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 ___ For more information about ZODB, see http://zodb.org/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
[ZODB-Dev] RelStorage with Oracle and separation of rights
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 ___ For more information about ZODB, see http://zodb.org/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
