In 12.2 When grants are given to "Stub objects" it invalidates all dependent stub objects. "stub objects" are simply pointers to an actual object definition from an ancestor edition that is still being inherited by the given edition.
One of the most important new features of Oracle 11 (11gR2) release 2 is the ability to create object versions, making it easy to fix bugs in applications, allowing you to change procedures, triggers, views and packages without affecting application usage.
SQL> Select sys_context('userenv','current_edition_name') "Current_Edition" from dual;
Current_Edition
V_20170414_1016
SQL> select object_name, object_type, edition_name from user_objects_ae where object_name ='PER_ALL_PEOPLE_F';
OBJECT_NAME OBJECT_TYPE EDITION_NAME
PER_ALL_PEOPLE_F SYNONYM V_20170213_2045
PER_ALL_PEOPLE_F NON-EXISTENT ORA$BASE
PER_ALL_PEOPLE_F NON-EXISTENT V_20170213_0218
ad_zd.grant_privs (created by patch 19674458:R12.AD.C) brings a new API for providing grants to objects with minimal invalidation. Henceforth granting in 12.2.x, should be using only ad_zd.grant_privs instead of 'grant' (DDL) directly
SQL> grant select on FND_CONCURRENT_REQUESTS TO apps;
Syntax:
ad_zd.grant_privs(
X_PERMISSIONS in varchar2, /* comma-seperated permission list */
X_OBJECT_NAME in varchar2, /* name of APPS object or APPS synonym */
X_GRANTEE in varchar2, /* grantee schema or role */
X_OPTIONS in varchar2 default NULL); /* grant options, example: 'WITH GRANT OPTION' */
Example
SQL> exec AD_ZD.grant_privs('SELECT', 'FND_RESPONSIBILITY', 'MSC',X_GRANT_TO_TABLE=>TRUE)PL/SQL procedure successfully completed.
SQL> exec AD_ZD.revoke_privs('SELECT','FND_RESPONSIBILITY','MSC');
The concept here is to execute the GRANT statement for a given editioned object in the edition containing the actual copy of that editioned object. This is an unfortunate consequence of Edition-based Redefinition. This is fixed in Delta 6 with a new API: AD_ZD.GRANT_PRIVS. The AD_ZD.GRANT_PRIVS procedure is meant to operate on EBS APPS "logical" objects, meaning the object present or exposed in the APPS schema and identified by its name or synonym in the APPS schema. Such an object could be:
1. An editioned code object, actually present in the APPS schema.
2. An effectively-editioned table (APPS Synonym -> Editioning View -> Table).
The GRANT_PRIVS API is specialized to the task of granting permissions on EBS objects (exposed in APPS) to other users. For grants on editioned custom objects, you could make synonym to the custom object in APPS, and then run GRANT_PRIVS on that synonym name. We could look at extending the support in GRANT_PRIVS to directly handle custom objects not exposed to APPS.
Workaround 1: Move to the Delta 6 code level (or higher) and grant privileges using AD_ZD.GRANT_PRIVS procedure instead of direct DDL
Workaround 2: 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).
Note:
Patch 21841299 R12.AD.C.delta.8 is now available, and recommended to uptake the latest changes,
Patch 19674458:R12.AD.C is available.
This patch creates a new API for providing grants to objects with minimal invalidation.
This patch prereqs R12.AD.C.DELTA5. In addition, it is recommended that customer also apply 19581770:R12.AD.C, BUNDLE FIXES FOR R12.AD.C.DELTA.5 (18283295), before applying this patch.
References:
Granting Privileges On An Object May Cause Invalidations (Doc ID 1987947.1)
http://nuijten.blogspot.ae/2010/11/edition-based-redefinition-and.html
For any queries, kindly email me at samiappsdba@gmail.com
