Way to success...

--"Running Away From Any PROBLEM Only Increases The DISTANCE From The SOLUTION"--.....--"Your Thoughts Create Your FUTURE"--.....--"EXCELLENCE is not ACT but a HABIT"--.....--"EXPECT nothing and APPRECIATE everything"--.....

Monday, June 19, 2017

Granting in Oracle EBS R12.2 : should be using only ad_zd.grant_privs instead of 'grant' (DDL) directly

Granting privileges on an object may cause object invalidations 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. 

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

TYPICAL Example
================

If you want to grant SELECT on FND_RESPONSIBILITY To APPSRO Schema then you must use below syntax

To Grant:

SQL> exec AD_ZD.grant_privs('SELECT', 'FND_RESPONSIBILITY', 'APPSRO');

PL/SQL procedure successfully completed.

To Revoke:

SQL> exec AD_ZD.revoke_privs('SELECT', 'FND_RESPONSIBILITY', 'APPSRO');

PL/SQL procedure successfully completed.


7 comments:

  1. Hi,

    Can we give grants to an object whose owner is 'APPLSYS' but has a synonym owned by 'apps' using AD_ZD.grant_privs ?

    ReplyDelete
    Replies
    1. Yes.
      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.

      Delete
  2. Hi,
    How can we give grants via AD_ZD.GRANT_PRIVS from other schema then APPS (e.g. XXEXT)?

    ReplyDelete
    Replies
    1. In my experience, working with Oracle Support, the solution *for*tables* is to first grant the desired permission(s) WITH GRANT OPTION *on*the*editioning*view* to APPS with the native 'grant' command, and then one can create a synonym for the user and run AD_ZD.GRANT_PRIVS as apps to grant the permission(s) to the desired schema. Thus if your table is XXEXT.XX_MY_TABLE and you have run AD_ZD_TABLE.UPGRADE to create the editioning view (XXEXT.XX_MY_TABLE#) and APPS synonym (APPS.XX_MY_TABLE), then you need to:

      connect XXEXT
      grant on XX_MY_TABLE to APPS with grant option;

      connect APPS
      create synonym USER.XX_MY_TABLE for APPS.XX_MY_TABLE;
      exec AD_ZD.GRANT_PRIVS(','XX_MY_TABLE','USER',NULL,X_GRANT_TO_TABLE=>FALSE);

      Delete
    2. This comment has been removed by the author.

      Delete