Re: [ZODB-Dev] RelStorage on Oracle RAC (doing now)

[Shane Hathaway]

Darryl Dixon - Winterhouse Consulting wrote:
 We have a client with an existing Oracle RAC infrastructure (4 node RAC).
 We are going to using RelStorage + cx_Oracle to connect to this from
 RedHat Linux servers, running Plone 3.3.x. This note is to say we're
 here and to provide some technical notes on our experience for the
 benefit of other implementers.

Cool, thanks!

 One (aesthetic?) problem is that the string for the relstorage_util
 package is hard-coded to SYS; eg, in
 relstorage.adapters.locker.OracleLocker.hold_commit_lock is the string
 sys.relstorage_util.request_lock. Our DBA finds this distasteful and
 would prefer to keep the relstorage_util package in the Zope schema that
 he has created to partition this stuff off.

As a result of this choice, you had to do this instead of following the 
RelStorage documentation:

 GRANT EXECUTE ON DBMS_LOCK TO zope;

By doing that, you gave the zope user the ability to do arbitrary 
things with arbitrary Oracle locks.  Do you know what the implied 
security risks are?  I don't know, but the fact that Oracle restricts 
access to the DBMS_LOCK package implies there must be some important 
security risks.  Therefore, Oracle seems to be saying that no one should 
ever GRANT EXECUTE ON DBMS_LOCK TO zope.  I have not found any docs on 
the specific risks, but maybe the DBMS_LOCK package could allow someone 
to execute a cross-database denial of service attack.

The RelStorage docs say to put the stored procedure in the SYS 
namespace; that way, it's not necessary to GRANT EXECUTE ON DBMS_LOCK 
TO zope.  Either solution is distasteful, though.  I blame Oracle. 
Postgres and MySQL have much simpler advisory locking systems that allow 
any database user to acquire named locks without cross-database security 
risks.

Here are some reasonable choices:

1) Leave it as-is and explain in the RelStorage docs why you should not 
change it.

2) Assume the Oracle security risk is insignificant (or nonexistent) and 
have everyone GRANT EXECUTE ON DBMS_LOCK TO zope.  This would make 
RelStorage slightly simpler and faster.  I'm not opposed to that.

I don't want to make the choice of how to use DBMS_LOCK configurable.  I 
want everyone to use DBMS_LOCK the same way, whatever that way is going 
to be, to minimize support costs.

FWIW, I believe that DBMS_LOCK doesn't really have any security risks. 
Instead, I think its API is broken, so Oracle silently discourages its 
use.  If there were an alternative that works in 10g, we would use that 
instead.  We can't use table locks because the WAIT keyword was not 
added to the LOCK TABLE statement until release 11g.

One we sort this out, we can make a final release of RelStorage 1.4.

Shane
___
For more information about ZODB, see the ZODB Wiki:
http://www.zope.org/Wikis/ZODB/

ZODB-Dev mailing list  -  ZODB-Dev@zope.org
https://mail.zope.org/mailman/listinfo/zodb-dev

[ZODB-Dev] RelStorage on Oracle RAC (doing now)

[Darryl Dixon - Winterhouse Consulting]

Folks,

We have a client with an existing Oracle RAC infrastructure (4 node RAC).
We are going to using RelStorage + cx_Oracle to connect to this from
RedHat Linux servers, running Plone 3.3.x. This note is to say we're
here and to provide some technical notes on our experience for the
benefit of other implementers.

First, we have created a buildout recipe which knows how to install the
necessary oracle instantclient libraries as instructed by the cx_Oracle
package - hopefully we will pypi-ify this recipe shortly. It sets things
up such that the cx_Oracle egg can then be built and installed by buildout
simply by specifying eggs = cx_Oracle.

Second, the recipe exports the necessary values for ORACLE_HOME and
LD_LIBRARY_PATH in its options so that other buildout parts (eg,
[instance]) can take these and put them in to zope.conf's environment
section.

Third, it accepts tnsnames entries and puts them in to an appropriate,
instance-specific tnsnames.ora file ready for Zope to find and use.

From that point, all that remains is for the user to specify the usual
RelStorage zope.conf options (%import relstorage, etc) and use the
oracle tag with user/password/dsn.

This works. We are going to be testing RAC failover etc shortly to gauge
the failure characteristics of the client connector library. Our
expectation is that it should probably be seamless.

One (aesthetic?) problem is that the string for the relstorage_util
package is hard-coded to SYS; eg, in
relstorage.adapters.locker.OracleLocker.hold_commit_lock is the string
sys.relstorage_util.request_lock. Our DBA finds this distasteful and
would prefer to keep the relstorage_util package in the Zope schema that
he has created to partition this stuff off. This all works fine, except
that we must currently manually hotfix locker.py to remove 'sys.' from the
front of that string. Options for handling this differently might be:
1) Hardcode the string as relstorage_util.request_lock and advise
users/provide the necessary SQL to add a synonym into their schema for
sys.relstorage_util
2) Make this a configurable parameter inside the oracle zope.conf tags
that would override the default string of 'sys.relstorage_util'; along the
lines of 'utilpackage relstorage_util' or 'utilpackage
sys.relstorage_util'
3) ??? Better database/oracle people than me can comment here :)


Aside from that everything has been relatively smooth. We are going to be
running this up on both 32bit and 64bit Linux clients. It is a shame that
the Oracle client libraries downloads are hidden behind both a
login/registration mechanism and a hokey javascript 'Accept this licence'
scheme, which prevents them from being automatically fetched and installed
a-la-buildout. We are keeping the necessary copies on our internal
buildout index server to make deployment to our various environments
smooth here.

We were surprised to discover that with cx_Oracle and the
oracle-instantclient libraries, that connection passwords must be supplied
in lower case in zope.conf, regardless of whether upper case was used when
specifying the password initially.

I will keep the community posted on some basic performance metrics and
failover tests as we perform them.

Footnote - the SQL the we used to initialise the zope user and create the
relstorage_util package is:
--8--[cut]
As SYS execute:

CREATE USER zope IDENTIFIED BY 
DEFAULT TABLESPACE XXtablespaceXX
TEMPORARY TABLESPACE sys_temp
QUOTA UNLIMITED ON XXtablespaceXX
QUOTA UNLIMITED ON XXindexXX

GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO zope;

GRANT EXECUTE ON DBMS_LOCK TO zope;


As ZOPE execute:

CREATE OR REPLACE PACKAGE relstorage_util AS
FUNCTION request_lock(id IN NUMBER, timeout IN NUMBER)
RETURN NUMBER;
END relstorage_util;
/

CREATE OR REPLACE PACKAGE BODY relstorage_util AS
FUNCTION request_lock(id IN NUMBER, timeout IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN DBMS_LOCK.REQUEST(
id = id,
lockmode = DBMS_LOCK.X_MODE,
timeout = timeout,
release_on_commit = TRUE);
END request_lock;
END relstorage_util;
--8--[cut]


many regards,
Darryl Dixon
Winterhouse Consulting Ltd
http://www.winterhouseconsulting.com
___
For more information about ZODB, see the ZODB Wiki:
http://www.zope.org/Wikis/ZODB/

ZODB-Dev mailing list  -  ZODB-Dev@zope.org
https://mail.zope.org/mailman/listinfo/zodb-dev