AD_ZD.grant_privs and the controversial patch?
Recently Oracle has introduced a controversial patch that fixes
“Bug 19674458 - GRANTING SELECT ON LARGE NUMBER OF OBJECTS TO A ROLE CAUSES MASSIVE INVALIDS”
This was an interesting intermittent bug whereby running a large quantity of GRANT statements directly on the APPS logical objects caused all the dependent stub objects to become invalid.
What’s a stub object? "stub objects" are simply pointers to an actual object definition from an ancestor edition that is still being inherited by the given edition.
As I am sure you are aware EBS 12.2 has online patching which introduced editioned objects on the DB. To ensure the correct data is reported during a patching cycle Oracle mandated that queries should be run against the APPS stub object instead of the owning Schema e.g. Select * from APPS.GL_BALANCES instead of what we previously did which was Select * from GL.GL_BALANCES.
Okay, that’s a lot to take onboard but hopefully, it’s not all new. So, we obviously don’t want invalid objects because this could have an adverse effect on production whilst the objects get recompiled.
What’s the solution? To be honest I feel for the developer and the support manager that had to deal with this issue because the solutions that I can think of and the solution provided by Oracle are less than ideal and you are likely to hit this issue even if you have the fix that’s provided in R12.AD.C.DELTA.6 because you don’t know what you don’t.
The recommended solution is to get onto Delta 6 or higher and then instead of running the GRANT DDL you should use the AD_ZD.GRANT_PRIVS API. Great, if you are still with me you now know to scrub the GRANT statement from your vocabulary and use the AD_ZD.GRANT_PRIVS API instead, and in case you were wondering there is a revoke API as well.
exec AD_ZD.grant_privs(‘SELECT’, ‘TABLE_NAME’, ‘SCHEMA_NAME’,X_GRANT_TO_TABLE=>TRUE)
exec AD_ZD.revoke_privs(‘SELECT’,’TABLE_NAME’,’ SCHEMA_NAME ‘);
Once you use the new API to grant the permissions you will notice that if you try and directly query the base table you will get a permissions error.
So now you know but if you are not on Delta 6 and above is there anything you can do that’s a bit less drastic than getting patches applied? There is a documented workaround which you might be interested in:
- Start an online patching cycle (adop phase=prepare)
- connect to the patch edition (source EBSapps.env patch)
- execute native DB grant statements in the patch edition
- then complete the patching cycle (finalize, cutover, cleanup)
Everything mentioned here is available in “Granting Privileges On An Object May Cause Invalidations (Doc ID 1987947.1)” just without my opinions. Cheers.