Roles are useful, though there are a few limitations. This is one limitation of Oracle roles.
Table owned by userA. UserB has "select any table" and creates SQL that sucessfully runs in sql*plus. When userB tries to put the SQL in a package, there is ORA-00942 compile error.
The solution is to directly grant the userA.table_name SELECT privilege to userB. It's not enough to have a role that allows the SELECT permission.
Example:
Create package header with no problems, then try to create package body:
Warning: Package Body created with compilation errors.
SQL> show err
Errors for PACKAGE BODY xPKG:
LINE/COL ERROR
--------
-----------------------------------------------------------------
27/7 PL/SQL: SQL Statement ignored
72/29 PL/SQL: ORA-00942: table or view does not exist
-- In another session, directly grant SELECT on table_name to this user.
-- In this session, re-submit package body.
SQL> /
Package body created.
No comments:
Post a Comment