I need to do a select statement that calls a number of API’s that are not available under the schema that I am logged in under. Basically I have a select that selects from tables and views that I have access to under the login I am using (IFSINFO). But there are some lines that call API’s that are only available under a different login (IFSAPP).
If I comment out the lines that call these API’s the select works fine. But if I leave one of them uncommented the select brings back nothing. I have tried logging in as IFSAPP and granting Execute on user IFSINFO to those packages but when I log back in as IFSINFO the select comes back with no rows. It doesn’t give an insufficient privilege error.
I am thinking that the API selects from tables/views that IFSINFO does not have the select rights on. Is there a way that execute can be granted to IFSINFO on an API (and at the same time grant privileges' on whatever views or tables that are referenced in that API)?
Best answer by NickPorter
View original