resource privilege through another user

[leonard905]

Hi all,

I created a user who has resource privilege with admin_option. This user granted resource privilege to another developer who wants to create a function. The developer has access to all synonyms and tables in the database; however, when the developer tries to create a particular function he gets PLS-00201 error message. what's interesing is that he can select from the table that the error message is pointing to.

Does know why this is happening. And what must I do to resovle this issue.

Thanks.

[stecal]

PLS-00201 is a generic PL/SQL error code - has many causes. Check the privileges for the developer. He/she needs direct grants to create a function.

Explanation:
~~~~~~~~~~~~
If you're accessing tables/views in PL/SQL procedure or package and getting either ORA-1031 or ORA-942 (or PLS-201), but the same select/update/insert/delete works ok on SQL*Plus, then you need to check if the privileges have been granted to the user creating the procedure via a role.
***Privileges granted via role are disabled inside stored procedures.***

This means that the user executing the procedure or package should be granted the privileges nequired to perform the actions against objects directly.

A user cannot acquire a privilege via a role if he needs that privilege when executing a stored procedure or function or package. If the user issues the same statements in SQL, it works as the user can use the
privileges granted via a role.