Re: grants and all_tab_columns

joemellors_at_aol.com (Joe Mellors) wrote in news:53566469.0205101030.25387100_at_posting.google.com:

> I hope someone has the answer to this cause I'm stumped :
> I've created a package and within a procedure in it am doing a select
> statement on all_tab_columns to get data_type and data_length for a
> column on a table owned by someone else. I realise all_tab_columns
> only shows you the tables you have access to so select is granted to
> the owner of my package on the other owner's table and yet when I run
> the procedure from SQL plus it returns a no_data_found exception. When
> I do the same select statement from SQL plus logged on as the same
> user I get the row. Can anyone shed any light ?
>
> Regards,
>
> Joe Mellors
>

When in SQL*Plus I suspect that you are "allowed" access to certain tables via a ROLE; which is why you can "see" them.

When attempting to access these same tables via PL/SQL, the procedure can't/doesn't see the tables because the procedure does NOT "inherit" access via any ROLE.

Simply put, the owner of the procedure must be directly GRANTed access to the table(s) in order for his procedure(s) to access the table(s).

HTH & YMMV! HAND! Received on Sat May 11 2002 - 21:17:33 CEST