[Checkins] SVN: relstorage/trunk/ Oracle: call the DBMS_LOCK.REQUEST function directly instead of using

[Shane Hathaway]

Log message for revision 110759:
  Oracle: call the DBMS_LOCK.REQUEST function directly instead of using
  a small package.  The small relstorage_util package was confusing to DBAs
  and provided no real security advantage.
  

Changed:
  U   relstorage/trunk/CHANGES.txt
  U   relstorage/trunk/README.txt
  U   relstorage/trunk/relstorage/adapters/locker.py
  U   relstorage/trunk/relstorage/tests/README.txt

-=-
Modified: relstorage/trunk/CHANGES.txt
===================================================================
--- relstorage/trunk/CHANGES.txt	2010-04-12 17:17:07 UTC (rev 110758)
+++ relstorage/trunk/CHANGES.txt	2010-04-12 17:40:54 UTC (rev 110759)
@@ -4,6 +4,10 @@
 - History-preserving storages now replace objects on restore instead of
   just inserting them.
 
+- Oracle: call the DBMS_LOCK.REQUEST function directly instead of using
+  a small package.  The small relstorage_util package was confusing to DBAs
+  and provided no real security advantage.
+
 1.4.0b3 (2010-02-02)
 --------------------
 

Modified: relstorage/trunk/README.txt
===================================================================
--- relstorage/trunk/README.txt	2010-04-12 17:17:07 UTC (rev 110758)
+++ relstorage/trunk/README.txt	2010-04-12 17:40:54 UTC (rev 110759)
@@ -117,35 +117,13 @@
     $ su - oracle
     $ sqlplus / as sysdba
 
-The commands below will create a PL/SQL package that provides limited
-access to the DBMS_LOCK package so that RelStorage can acquire user
-locks. Using ``sqlplus`` with ``SYS`` privileges, execute the
-following::
-
-    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;
-    /
-
+You need to create a database user and grant execute privileges on
+the DBMS_LOCK package to that user.
 Here are some sample SQL statements for creating the database user::
 
     CREATE USER zodb IDENTIFIED BY mypassword;
     GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO zodb;
-    GRANT EXECUTE ON relstorage_util TO zodb;
+    GRANT EXECUTE ON DBMS_LOCK TO zodb;
 
 
 Configuring Plone

Modified: relstorage/trunk/relstorage/adapters/locker.py
===================================================================
--- relstorage/trunk/relstorage/adapters/locker.py	2010-04-12 17:17:07 UTC (rev 110758)
+++ relstorage/trunk/relstorage/adapters/locker.py	2010-04-12 17:40:54 UTC (rev 110759)
@@ -136,9 +136,14 @@
         # Hold commit_lock to prevent concurrent commits
         # (for as short a time as possible).
         status = cursor.callfunc(
-            "sys.relstorage_util.request_lock",
+            "DBMS_LOCK.REQUEST",
             self.inputsize_NUMBER,
-            (self.commit_lock_id, self.commit_lock_timeout))
+            keywordParameters={
+                'id': self.commit_lock_id,
+                'lockmode': 6,  # exclusive (X_MODE)
+                'timeout': self.commit_lock_timeout,
+                'release_on_commit': True,
+            })
         if status != 0:
             if status >= 1 and status <= 5:
                 msg = ('', 'timeout', 'deadlock', 'parameter error',

Modified: relstorage/trunk/relstorage/tests/README.txt
===================================================================
--- relstorage/trunk/relstorage/tests/README.txt	2010-04-12 17:17:07 UTC (rev 110758)
+++ relstorage/trunk/relstorage/tests/README.txt	2010-04-12 17:40:54 UTC (rev 110759)
@@ -53,42 +53,21 @@
     $ su - oracle
     $ sqlplus / as sysdba
 
-The commands below will create a PL/SQL package that provides limited
-access to the DBMS_LOCK package so that RelStorage can acquire user
-locks. Using ``sqlplus`` with ``SYS`` privileges, execute the
+Using ``sqlplus`` with ``SYS`` privileges, execute the
 following::
 
-    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;
-    /
-
     CREATE USER relstoragetest IDENTIFIED BY relstoragetest;
     GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest;
-    GRANT EXECUTE ON relstorage_util TO relstoragetest;
+    GRANT EXECUTE ON DBMS_LOCK TO relstoragetest;
     CREATE USER relstoragetest2 IDENTIFIED BY relstoragetest;
     GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest2;
-    GRANT EXECUTE ON relstorage_util TO relstoragetest2;
+    GRANT EXECUTE ON DBMS_LOCK TO relstoragetest2;
     CREATE USER relstoragetest_hf IDENTIFIED BY relstoragetest;
     GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest_hf;
-    GRANT EXECUTE ON relstorage_util TO relstoragetest_hf;
+    GRANT EXECUTE ON DBMS_LOCK TO relstoragetest_hf;
     CREATE USER relstoragetest2_hf IDENTIFIED BY relstoragetest;
     GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest2_hf;
-    GRANT EXECUTE ON relstorage_util TO relstoragetest2_hf;
+    GRANT EXECUTE ON DBMS_LOCK TO relstoragetest2_hf;
 
 When running the tests, you can use the environment variable
 ORACLE_TEST_DSN to override the data source name, which defaults to