Are you connected to the database as the application owner?  This only happens to me when I am connecting to our prod instance where I do not have access to all of the underlying tables connecting to some views.

Hello, 
our connection is with username:
IFSINFO
Consulting told me, that i have to put IFSAPP before Name of VIEW/TABLE

Hi Markus,

Does information display if you execute the following:

SELECT * FROM IFSAPP.PURCHASE_ORDER_TAB

Hello, 
Yes… that works fine with SELECT * FROM IFSAPP.PURCHASE_ORDER_TAB
 

But I think, the IFS-Tables could have an other Structure/more or less columns as the VIEW ?

Consulting told us, that we need to prepare the Migration Job corresponding to the VIEWS.
For Creating Queries we should use the VIEWS as well.

Is there an explainable reason why some VIEWS in SQL-Developer do not provide any data?
But in the IFS quick report they come as expected ?

I think most likely this means the IFSINFO may need additional privileges.  Either the application owner or another user with DBA privileges may have to:

GRANT SELECT on IFSAPP.PURCHASE_ORDER to IFSINFO

The below community question may also be of interest to you.

@Markus S.  In addition to what Marty has already suggested, this link detailing the special Cloud users and their intended use may help you.  https://docs.ifs.com/techdocs/24r1/030_administration/010_security/010_users/

To answer your specific question on the PURCHASE_ORDER view, check the actual SQL for the view in Oracle SQL Developer.  You’ll see at the end there is a security check invoking user_allowed_site_pub. 

If you select * from that view, you’ll notice it tells you who you are connected as and what sites you have access to.  If it comes up blank, then you know that IFSINFO has not been granted access to any sites in IFS.

You have access to view PURCHASE_ORDER otherwise you couldn’t select from it at all, and you’d get a missing privilege error message.

That being said, the view has built-in site level security :

If you’re connected to the DB with IFSINFO, then the IFSINFO user must be granted access to Sites in order to be able to return data from that view.

Whichever user you’re using to connect to the DB should definitely be granted access to any sites/companies that you are wishing to select from, when using views, as views have a lot of built in company/site level security added on top of the table layer.

Damn @Tracy Norwillo you beat me to the punch by a minute =p

@Tracy Norwillo thank you!  I never really thought of the user_allowed_site_pub… that is something I should have been using a long time ago!  Excellent suggestion.

Hello, 
just one more question regarding this pic:
 

For me… it shows only:
 

Is there something I can do to get privilege for this VIEW (and other VIEWS) ?
Or do I need our Consulting ?
Or IFS ?

​​​​​​​Thanks

You can do that through permission sets being granted to IFSINFO.

It’s possible IFSINFO already gets select permissions on all views, I’m not sure about that, but you’d be able to change that only if when you select you get missing permissions to select from that view.

If you can run a select (and get 0 or more results) it means the view is granted for SELECT to IFSINFO. If you did NOT have select privileges, you’d get an error like this:

@Markus S.  Security is a two step process.  1. Can you see the object?  2. Can you see the data?

Grants handle #1

Permissions within IFS handle #2. 

Quite a lot of views in IFS have a permissions check at the end of the select statement. 

IFSINFO needs to successfully pass the permissions check in the view to see the data.  The permissions check in the PO view is checking to see which sites IFSINFO has been granted access to. If IFSINFO has not been granted access to any sites, you will see no data.  In IFS, have someone check the setup of the IFSINFO user to make sure it has access to the sites you CAN see in the equivalent table.

I think this bit of the IFS tech docs may be confusing.  

IFSINFO Special Privilege = “SELECT on all views”.  That only covers #1 above.

Maybe a better question, is what user do customers normally use for migration purposes to get around this issue?  That I don’t know but can ask some of my colleagues for advice.

Hello, 
I've made it.
The user IFSINFO needs the membership for COMPANY and LOCATION/SITE,
It was not necessary to setup more Privileges.

Thanks for your help.