Battling with Privileges
As the Beasty Boys once pointed out “You gotta fight for your right (to party)”. Well if you want to party with HANA views and tables it helps to know how to handle privileges.
Problem 1: Data Preview Error after successful activation
Assume that you are a user that imported some data into some schema (e.g. SFLIGHT). You can create (attribute, analytic, calculation) views for this schema. The activation of these views work fine. When trying to access the data (e.g. Data Preview), you get the following error:
Error Executing Query: SAP DBTech JDBC:  (at 106): invalidated view: <YourSchema>/<YourView>: line 1 col 107 (at pos 106)
The user owning the schema must give the _SYS_REPO user the privileges WITH GRANT OPTION. Thus as the respective schema owner you must execute
GRANT SELECT ON SCHEMA <YourSchema> TO _SYS_REPO WITH GRANT OPTION
All views in the repository belong to _SYS_REPO by definition. Thus even if you own the schema, the user _SYS_REPO will own the view. In order to process the underlying objects, the user _SYS_REPO needs to pass the privileges onto the respective user.
Problem 2: Repository content does not show up after successful activation
Once you activate checked-in content this content should show up in the catalog. However you may notice that you can push F5 (refresh) forever and nothing shows up.
This is because activation is processed by the _SYS_REPO user. Consequently the resulting catalog entities will belong to _SYS_REPO and not to your user. Thus you need to grant yourself the privileges to see your content. You can do this by calling stored procedures that will grant you the desired privileges.
call _SYS_REPO.GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT(‘create any’,'”your_schema”‘,’your_username’);
call _SYS_REPO.GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT(‘select’, ‘”your_table”‘, ‘your_user’);
Please note the sequence of the quoation marks. First a single quotation mark ( ‘ ), then a double quotation mark ( ” ). This allows for mixed case schema and table names. If you only use single quotation marks, your schema and table name must be in upper case only.
You have to execute these calls from the SQL Editor. For all the details you may want to consult the hana security guide.