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