Upgrade Oracle APEX 5.1 – Grant Issues

Many of us have upgraded or are planning an upgrade to the all new Oracle APEX 5.1 to give a shot at all the new amazing features, the refined Universal Theme, JET charts, the super duper Interactive Grid and so on.

APEX 5.1 OTN Upgrade

Throughout the process of this upgrade, lots of issues have risen such as zero-downtime upgrades, plugins breaking, custom CSS needing re-modification. All of these issues have their own solutions and/or workarounds.

One of the problems that I ran into today and want to share is that I upgraded 5.0 to 5.1 manually. That means I ran these three scripts one after another :

@apexins1.sql
@apexins2.sql
@apexins3.sql

Note: I stopped my ORDS deployment before starting the 3rd script. (It takes less than a minute to complete successfully)

Now, what happened in my case was that I didn’t drop the APEX_050000 user for some internal reasons but I realized some issues especially when exporting and importing applications between different environments and Workspace IDs being switched to other values.

Doing a lot of debugging and mainly checking the APEX_ADMINISTRATOR_ROLE role, I noticed the two grants below :

GRANT EXECUTE ON APEX_050000.WWV_FLOW_INSTANCE_ADMIN TO APEX_ADMINISTRATOR_ROLE;
GRANT SELECT ON APEX_050000.WWV_FLOW_PRIVATE_TO_ADMIN TO APEX_ADMINISTRATOR_ROLE;

Therefore the APEX_050000  user was doing some internal functions instead of the newly created APEX_050100 user (I don’t know why it didn’t raise any errors and compatibility warnings though). So what’s the solution? Simple! Revoke those GRANTS and you’re back on track.

REVOKE EXECUTE ON APEX_050000.WWV_FLOW_INSTANCE_ADMIN FROM APEX_ADMINISTRATOR_ROLE;
REVOKE SELECT ON APEX_050000.WWV_FLOW_PRIVATE_TO_ADMIN FROM APEX_ADMINISTRATOR_ROLE;

I highly recommend dropping the APEX_050000 user after you’ve upgraded to 5.1 as there might be more unanticipated problems. (I highly appreciate comments on this last statement)

There are more problems I’ve also ran into mostly including Themes. Most of the applications developed in my scenario all had legacy themes as their base theme and ever since they’ve been deprecated we’ve felt the need to change a lot of parts (even though we had tested for a couple of weeks before going through the actual upgrade). Hopefully I will post more on these topics in the upcoming weeks.

Lots of people might think I’m discouraging users to not upgrade to 5.1 but that’s not the point at all. It’s quite the opposite. Upgrading the core itself to 5.1 and eventually themes to UT (Universal Theme) is an inevitable step in keeping applications consistent with the ongoing technological and web design advances we are going through daily. All I’m saying is that it should be well planned and thought through thoroughly. There are lots of good blogs out there providing life-saving and time-consuming solutions, I hope this post also directs you in the same direction.

2,020 total views, 3 views today

6 Replies to “Upgrade Oracle APEX 5.1 – Grant Issues”

  1. Hi Farzad,

    I fully agree on doing a well planned update and dropping the old schema after verifying that it was successful. However, I do not understand why grants on the old APEX_050000 objects were a problem for you. Name resolution (e.g. via the public synonym APEX_INSTANCE_ADMIN) should have guaranteed that the package in 5.1 was invoked. If there is a bug in the upgrade, we want to fix it. Can you please give more details on that?

    Regards,
    Christian

    1. Hi Christian,

      Thanks for the reply and sorry for the delay in getting back to you. What happened is that I updated our three Development, Test and Production environments in the same week. An issue that is bugging us is that only in the Development and Test environments, when exporting an application via Application Builder, the Workspace ID in the file is different from the actual workspace ID the application is residing in.
      Do you have any insights on this?

      1. Hi Farzad,

        sorry, I did not see your message. If your are doing UI export in 5.1, the default is to export with original IDs. Several customers complained, so we changed that back to export with the current application ID in 5.1.1. If you can not upgrade, you should manually change the attribute before exporting.

        Regards,
        Christian

  2. Hi,

    still having the grant on APEX_050000.WWV_FLOW_INSTANCE_ADMIN shouldn’t be an issue, the question is where does the public synonym APEX_INSTANCE_ADMIN point to? Does it still point to the APEX_050000 or already to the APEX_050100 schema? How did you do the export/import? On the command line or via GUI?

    I don’t see a need to immediately drop the APEX_050000 schema, because it allows to revert to the old release. But I do agree that it should be dropped as soon as there is an agreement to go forward with the new release.

    Regards
    Patrick – APEX Team

    1. Hi Patrick,

      Thanks for your comment. It points to APEX_050100. I did the exports via GUI and it’s returning some weird workspace ID. But checking the workspace ID in the “about” section shows the original workspace ID.
      What went wrong?
      I still haven’t dropped the APEX_050000 schema yet.

Leave a Reply

Your email address will not be published. Required fields are marked *

 
Loading Facebook Comments ...