4 Replies Latest reply on Jun 27, 2011 3:24 PM by JustinCave

    Unable to delete a savepoint following an upgrade to 11.2

      We have an application (written in APEX) that was working correctly in Oracle 10.2 that has developed an error following an upgrade to Oracle 11.2 when it tries to delete a savepoint in the LIVE workspace.

      In the 11.2 database, DBA_REGISTRY reports the version of Workspace Manager as
      I believe that in the 10.2 database, DBA_REGISTRY reported the version of Workspace Manager as but I am not 100% confident about that-- we didn't think to record that information before the upgrade so I'm working off notes from what I believe was the last time we upgraded Workspace Manager in that database.

      User A in our system owns a package that contains the procedure
        procedure move_savepoint( p_savepoint_name VARCHAR2 )
          dbms_wm.deleteSavepoint( 'LIVE', p_savepoint_name );
              '<<savepoint description>>' );
        END move_savepoint;
      User A has been granted the WM_ADMIN_ROLE role.

      The APEX application logs in to the database as the APEX_PUBLIC_USER user. The APEX_PUBLIC_USER user has been granted EXECUTE access on User A's package and has been granted the WM_ADMIN_ROLE role. The existing savepoints are all in the LIVE workspace and are all owned by APEX_PUBLIC_USER

      Following the 11.2 upgrade, the call to the MOVE_SAVEPOINT procedure is failing with an ORA-20022 error when we're logged in as User A
        a.package_name.move_savepoint( '<<savepoint name>>' );
      ERROR at line 1:
      ORA-20022: insufficient privileges to delete savepoint
      ORA-06512: at "WMSYS.LT", line 6913
      ORA-06512: at "DW_APEX.PKG_TOTS", line 320
      ORA-06512: at line 2
      We tried granting User A all the WM system privileges
              '<<User A>>' );
      but that didn't change the error message.

      If we change the package to be AUTHID CURRENT_USER and log in as A, we can run the MOVE_SAVEPOINT procedure successfully. But we cannot run it from the application when we're logged in as APEX_PUBLIC_USER.

      I can't think of any privilege that we're missing at this point. And the fact that it was working before the in-place 11.2 upgrade leads me to suspect that something may have changed in Workspace Manager or that there are problems with our upgrade. Is there something that we're overlooking?


      Edited by: Justin Cave on Jun 15, 2011 11:30 AM

      Formatted the code a bit better
        • 1. Re: Unable to delete a savepoint following an upgrade to 11.2
          Ben Speckhard-Oracle
          Hi Justin,

          This is expected. It was a fix to be more consistent with definers/invokers rights within Workspace Manager. Since the owner of the package is A (and defined as definer's rights), user A needs to have the necessary privileges in order to delete/create the savepoint. However, since he is not the current owner of the savepoint, that is not the case by default. The way around this is to grant the WM_ADMIN_ROLE as you have done. The system/workspace privileges do not have any affect on DeleteSavepoint.

          Unfortunately, within procedures roles are not enabled as enforced by the database. On, the procedure was succeeding due to APEX_PUBLIC_USER being the owner of the savepoint, not due to the WM_ADMIN_ROLE being granted to either user. The solution would be to create the package as authid current_user. It is not quite clear why the procedure cannot be invoked in your case?

          Also, I just realized this is more of a temporary restriction. Would it be possible to manually do the delete/create of the savepoint as user A outside of the procedure. The definer's rights will apply to the workspace creation as well, so as of the owner of the savepoint will be the package owner. As a result, the next time move_savepoint is executed by APEX_PUBLIC_USER it would work as expected.

          1 person found this helpful
          • 2. Re: Unable to delete a savepoint following an upgrade to 11.2

            Thanks for that info-- very helpful.

            Is there a supported way to change the owner of an existing savepoint? I'm not seeing anything in the DBMS_WM package that would do that. It may be a little tricky to manually do the delete/ create outside of the application. We're creating savepoints when the business thinks they've reached a particular milestone (i.e. May end-of-month processing complete) but it often takes a few tries before everyone agrees that the milestone is really, really reached. We give the users the ability to "move" a particular savepoint by deleting and re-creating it. Dropping and recreating a savepoint that reflected a "really, really reached" milestone would be a non-starter but it's hard to know which of the milestones are really fixed other than seeing that they haven't been moved in a long time.

            • 3. Re: Unable to delete a savepoint following an upgrade to 11.2
              Ben Speckhard-Oracle
              Hi Justin,

              There is no supported way to change the owner of a workspace or savepoint, only to rename. The owner is fixed after its creation. However, if the savepoints are always created using this procedure, then you should be able to see which one are migrated based on the current owner (APEX_PUBLIC_USER vs A).

              If it is not feasible to modify outside the procedure, then it would be straight forward to internally update the owner. Just file an SR.

              • 4. Re: Unable to delete a savepoint following an upgrade to 11.2
                Thanks Ben! Appreciate the assistance.