[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