Re: Problem granting priveleges to a role

All roles are disabled inside a subprogram and hence you will have to grant privileges to objects directly ie via grant command.

Its not a bug or something like that its one of the feature of oracle that inside a procedure or functions roles are always disabled hence if u give users permissions via roles they never obtain object privileges.

bye...
jewels04_at_my-deja.com wrote in message <7mg0fo$qkf$1_at_nnrp1.deja.com>...
>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 - 19:32:55 CDT