How to Grant Privileges in Oracle EBS R12.2 Using AD_ZD.GRANT_PRIVS

When working with Oracle E-Business Suite (EBS) R12.2, granting privileges to applicatation objects requires special care to avoid invalidating objects in the current edition. 

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

A direct GRANT (DDL) command can potentially cause object invalidation when the application is actively being used. To handle this in a more controlled manner, Oracle introduced the AD_ZD.GRANT_PRIVS utility.

Why Use AD_ZD.GRANT_PRIVS?

In Oracle EBS R12.2, all grants should be performed using the AD_ZD.GRANT_PRIVS procedure rather than the traditional GRANT statement. 

The reason is simple: the GRANT statement directly modifies objects in the database, and this can invalidate them in the current edition. Invalidations during runtime can cause service interruptions, which is unacceptable in a live system.

Syntax for Granting and Revoking Privileges

The AD_ZD package provides two primary procedures:

Granting Privileges:

exec AD_ZD.grant_privs('privilege', 'object_name', 'grantee');

Revoking Privileges:

  exec AD_ZD.revoke_privs('privilege', 'object_name', 'grantee');
    

Granting Specific Roles:

 grant APPS_READ_ROLE to <SSO_ID>;
 

Example Use Case

Let’s walk through a practical example. Suppose you want to grant a SELECT privilege on the SABRIX_INVOICE table to a read-only user (RO515121110). You can use the following commands:

Grant SELECT privilege:

  exec AD_ZD.grant_privs('SELECT', 'SABRIX_INVOICE', 'RO515121110');
         

Revoke SELECT privilege:

   exec AD_ZD.revoke_privs('SELECT', 'SABRIX_INVOICE', 'RO515121110');
           

Grant APPS_READ_ROLE to the user:

    grant APPS_READ_ROLE to RO515121110;
    

These steps ensure that privileges are managed safely without risking any disruptions to the application.