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

[#1]

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.

[Former Member · Accepted solution]

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).

[thomas_jung]

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

[Former Member]

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?

[thomas_jung]

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

[Former Member]

No. No role was altered.