Problem granting priveleges to a role

We are having problems creating stored procedures (packages) that reference objects in other schemas when the object priveleges are granted to a role.

PROBLEM STATEMENT
If I grant object (table) priveleges to a user directly, they can create stored procedures that reference these tables in other schemas.

If I grant a Role (i.e. developer role) the same priveleges to the same tables, those users that have been granted that role cannot create stored procedures that reference tables in another schema (ie declarations of the type schema.table.column%TYPEfail)

There is an obvious work around - grant priveleges directly to the developers, but we would really like to use roles.

SAMPLE CODE and ERROR

PROCEDURE AssignmentTabGeneral

 (i_termcode  IN cm_admin.tassignment_category.class_term_cd%TYPE,
  i_classid  IN cm_admin.tassignment_category.class_id%TYPE,
  i_categoryid       IN cm_admin.tassignment_category.category_id%TYPE,
  i_assignmentid  IN cm_admin.tassignment.assignment_id%TYPE,
  io_resultset       IN OUT resultset_cur);

17/1     PL/SQL: Declaration ignored
18/21    PLS-00201: identifier 'CM_ADMIN.TASSIGNMENT_CATEGORY' must be
         declared

If I compile as the object owner (cm_admin) the procedure compiles with no errors.

If I compile as myself (jallen who has been granted the developer role. The developer role has access to these tables) it fails as shown.

If I grant access to the tables directly to jallen it will compile with no errors.

Has anyone seen this before? Any suggestions?

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jul 13 1999 - 13:28:27 CDT