4 Replies Latest reply on Oct 29, 2014 5:51 PM by Gary Graham-Oracle

    the requirement for EXECUTE on DBMS_LOCK for the unit testing framework.

    Sharma, Ankur

      What is the requirement for requesting EXECUTE on DBMS_LOCK for the SQL Developer's unit testing framework? Woud it not work if this privilege is not given as our security team has denied the access to this privilege?

        • 1. Re: the requirement for EXECUTE on DBMS_LOCK for the unit testing framework.
          Gary Graham-Oracle

          Think about it.  Typically a test will do something like...

          • Make a backup copy of table A.
          • Change table A somehow.
          • Verify the expected change actually occurred.
          • Restore table A from the backup copy and drop the backup copy.

           

          Unit Test wants exclusive locks on all objects involved so no other user (Unit Test user or otherwise) interferes with the test in any way. And if the testing occurs on a non-production DB, as it definitely should, why would the security team be concerned?

           

          Regards,

          Gary

          SQL Developer Team 

          • 2. Re: the requirement for EXECUTE on DBMS_LOCK for the unit testing framework.

             

            What is the requirement for requesting EXECUTE on DBMS_LOCK for the SQL Developer's unit testing framework? Woud it not work if this privilege is not given as our security team has denied the access to this privilege?

            What happened when you tried it?

             

            Don't be afraid of breaking Oracle or sql developer by actually trying things.

             

            Your security team is correct to restrict execute privilege on the DBMS_LOCK package. Use of that package needs to be carefully controlled to maintain the integrity and proper operation of the system. Explicit use of that package is typically only needed for special use cases.

            • 3. Re: the requirement for EXECUTE on DBMS_LOCK for the unit testing framework.
              Unit Test wants exclusive locks on all objects involved so no other user (Unit Test user or otherwise) interferes with the test in any way. And if the testing occurs on a non-production DB, as it definitely should, why would the security team be concerned?

               

              Ok - but that wasn't really the question.

               

              The questions were: 1) does sql developer makes explicit use of the DBMS_LOCK package for the creation and/or execution of unit tests and, 2) will the unit test functionality work at all (or only work for specific test types) if execute on that package isn't granted?

               

              In my experience tables are typically locked using the LOCK statement not by using DBMS_LOCK and privileges on the DBMS_LOCK package are not usually granted in ANY environment. That package can be so easily be misused and it is seldom needed.

              • 4. Re: the requirement for EXECUTE on DBMS_LOCK for the unit testing framework.
                Gary Graham-Oracle

                I like your response to OP: "What happened when you tried it?" 

                 

                Anyway, not a lot of free time today and this is not my area, but just looking quickly at code and assuming it is not dead code...

                • The Unit Test's editor calls some Lock Manager code whenever it commits, rolls back, updates, or refreshes.
                • During lock or unlock, Lock Manager calls DBMS_LOCK.ALLOCATE_UNIQUE to get a handle for the object to lock
                • If something fails (no privilege?), then the handle is set to null, and that exception gets eaten.
                • Not sure exactly how exceptions in lock (DBMS_LOCK.REQUEST) / unlock (DBMS_LOCK.RELEASE) get handled. There are no Catch clauses, so I guess they just rise to some higher level as run-time exceptions.

                 

                Perhaps someone knowing more will take the time to reply.