NaturalOracle DataGaurd tutorial Series Coming Soon!! Natural
For Any queries, please mail us at support@funoracleapps.com

Granting privileges on an object may cause object invalidation's in the current edition(Use AD_ZD.GRANT_PRIVS Only)

No comments

Granting privileges on an object may cause object invalidation in the current edition.

Grants cannot be performed in the run edition when the application is being used.

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.

Henceforth granting in 12.2 ; should be using only ad_zd.grant_privs instead of 'grant' (DDL) directly

There are occasions where you might want to grant privileges to objects in the APPS schema; for example, you might want to create a database user with read-only privileges. However, due to the way that editioned objects are treated, granting a privilege on an editioned object directly may cause temporary invalidation of any dependent objects. 



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).

To avoid such invalidation, use the procedure AD_ZD.GRANT_PRIVS:


procedure  GRANT_PRIVS( X_PERMISSIONS in VARCHAR2,
                        X_OBJECT_NAME in  VARCHAR2,
                        X_GRANTEE     in VARCHAR2,
                        X_OPTIONS     in VARCHAR2 default NULL);
end;


Parameters: 

•X_PERMISSIONS: Permissions to be granted to the grantee.  This should be in upper case.
•X_OBJECT_NAME: Object to which the permission(s) apply. This should be in the exact case as defined and must exist in the database.  By default, objects will have uppercase names.
•X_GRANTEE: Other schemas and roles receiving the grant.
•X_OPTIONS: Grant options, for example: ‘WITH GRANT OPTION’.


Example
================

If you want to grant SELECT on FND_USER To XCUST Schema then you must use below syntax

To Grant:

SQL> exec AD_ZD.grant_privs('SELECT', 'FND_USER', 'XCUST');

PL/SQL procedure successfully completed.

To Revoke:

SQL> exec AD_ZD.revoke_privs('SELECT', 'FND_USER', 'XCUST');

PL/SQL procedure successfully completed.

No comments :

Post a Comment