Granting Object Privilege of Other Schemas in PL/SQL via ROLE in Oracle? Answer is NO.

Yesterday, one of our developers asked me to help in following error message while he tried to create package.

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY WSTUMGR.BYLKFROV:

LINE/COL ERROR
--------------------------------------------------
60/13    PL/SQL: Statement ignored
60/13    PLS-00904: insufficient privilege to access object PUBLIC.BWLKOIDS

69/10    PL/SQL: Statement ignored
69/10    PLS-00904: insufficient privilege to access object PUBLIC.BWLKOIDS

81/10    PL/SQL: Statement ignored
81/10    PLS-00904: insufficient privilege to access object PUBLIC.BWLKOIDS

Granting required privileges for specific objects is quite straightforward. However, granting it via role doesn’t work in this case. To make it working, explicit issuing of grant command to specific users is necessary. The reason of doing that is because that oracle role has limitation on usage of object privileges.

“Oracle roles have some limitations. In particular object privileges are granted through Oracle roles can not be used when writing PL/SQL code. When writing PL/SQL code, you must have direct grants to the objects in the database that your code is accessing.”  Burleson Consulting

Oracle Metalink Document “Roles and Creating Stored Objects / Views [ID 1011899.6]” describes common problem of that “all roles are disabled in any *named* PL/SQL block that is created in a user schema that does not own the object being referenced in the PL/SQL block.” Further, this document explains as below.

• Do not confuse roles with GRANTs and REVOKEs — Roles are meant to be toggled 
  on and off on a per session basis, whereas GRANTs and REVOKEs (as well as any 
  associated dependencies) updates the data dictionary because they are DDL 
  operations.  This is why it becomes an issue of gross ambiguity:  A user can 
  potentially log on in the form of two sessions, one with the role enabled, the 
  other one with the role disabled, and the outcome of both sessions will not be 
  the same.  
• Hence, the restriction that stored object creation cannot depend on privileges 
  granted through a role is added in the Oracle7 to avoid ambiguous cases of this

  nature.  Notice that anonymous PL/SQL blocks are not bound to this restriction, therefore they are executed based on privileges granted through enabled roles.