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