Question Re: Unable to 'select' from SQL view (.hdbview) in Technology Q&A

Unable to 'select' from SQL view (.hdbview)

Former Member — Thu, 06 Sep 2018 11:01:50 GMT

Hi Colleagues,

I have 2 XSA containers, say cont1 and cont2.

cont1 has synonyms on top of classical (netweaver) database tables, and then table functions on those synonyms.

For cont2, a role has been assigned, which gives it privileges to 'select' and 'execute' from cont1. This role is granted WITHOUT GRANT OPTION.

Now, in cont2, I have created synonyms (on top of the earlier table functions), and SQL views on top of the synonyms. The runtime objects are generated.

Now, if I do a 'select * from synonym_cont2', it works and shows me the result.

But if I do a 'select * from sqlview_cont2', it says

insufficient privilege: Not authorized

What is the problem here? Should the role be granted WITH GRANT OPTION? If so, why? If the object owner of cont2 already has access to whole cont1, WITH GRANT OPTION should only be required to let there be privileges for another container on top of cont2.

Re: Unable to 'select' from SQL view (.hdbview)

thomas_jung — Thu, 06 Sep 2018 13:48:44 GMT

What's in your HDBGRANTS file? You can have separate grants for object_owner and application_user. This could explain why the synonyms create (object_owner) but you can't select from them (application_user).

https://github.com/SAP/com.sap.openSAP.hana5.example/blob/hana2_sps03/core_db/cfg/central.hdbgrants

Re: Unable to 'select' from SQL view (.hdbview)

Former Member — Fri, 07 Sep 2018 05:50:10 GMT

I am able to create as well as select from the synonyms. Problem is with SQL views.

I am able to create SQL Views. But not able to select from them.

If the application user is able to select from synonyms, that implies the application user should be able to select from SQL views as well right?

Re: Unable to 'select' from SQL view (.hdbview)

thomas_jung — Fri, 07 Sep 2018 12:25:45 GMT

Did you alter the default_access_role in this container by any chance?

Re: Unable to 'select' from SQL view (.hdbview)

Former Member — Tue, 11 Sep 2018 06:18:39 GMT

No. No role was altered.

Re: Unable to 'select' from SQL view (.hdbview)

Former Member — Wed, 10 Oct 2018 06:17:45 GMT

Since views are definer mode objects, i.e., the #OO of the container, and not the DB user, reads from them, you always need WITH GRANT OPTION in this kind of scenarios, to select from SQL views (so that the #OO grants to the DB user, i.e. you).