HANA Calculation View and Package Visibility

[aleksandrs_antonuks2]

Hi experts,

I am currently working with HANA authorizations and am currently frustrated. My views are organized in several packages representing different stages of data aggregation and preparation. The last package is containing views that are supposed to be consumed by analysis for excel.

Whenever analysis for excel is opened I want to see the folder that is containing the views that are supposed to be exposed for consumption. The problem here is that in order to be able to execute these views I have to give SELECT on either _SYS_BIC schema or on all views that are used in order to create data model for the last view. That is leading to the fact that analysis for excel is seeing much more packages and views than I want it to.

I browsed through different topics and could not find any solution to that. Would really appreciate any advise or any hint on how to organize security (roles, privileges) model in HANA.

Br,
Aleks.

[Cocquerel · Accepted solution]

Hello,

With HANA on-premise, you have 3 type of roles: repository role, catalog role, hdi role.
See comparaison here https://help.sap.com/docs/SAP_HANA_PLATFORM/b3ee5778bc2e4a089d3299b82ec762a7/3360ac839b844171837dce4...
Using repository role, you are right, you can only grant either access to the full _SYS_BIC schema or you would need to manually assign each single views one by one.
Using catalog role, there would be the workaround to grant all the views from one package via script (instead of doing it manually). Here is a script example:

DO BEGIN
DECLARE CURSOR c_objects FOR SELECT
"SCHEMA_NAME",
"OBJECT_NAME"
FROM "SYS"."OBJECTS"
WHERE "SCHEMA_NAME" = '_SYS_BIC'
AND "OBJECT_TYPE" = 'VIEW'
AND "OBJECT_NAME" NOT LIKE '/TAB/EXAMPLE%'
AND "OBJECT_NAME" NOT LIKE '%/dp/%'
AND "OBJECT_NAME" NOT LIKE '%/hier/%'
AND "OBJECT_NAME" NOT LIKE '%/proc/%'
AND "OBJECT_NAME" LIKE 'MyPackagePath%' ;

DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

FOR object AS c_objects
DO
EXEC 'GRANT SELECT ON "'|| object.SCHEMA_NAME || '"."'|| object.OBJECT_NAME || '" TO 'MY_CATALOG_ROLE'";
END FOR;
END;

Using hdi role, it supports <wildcard_object_identifier> syntax (see https://help.sap.com/docs/SAP_HANA_PLATFORM/3823b0f33420468ba5f1cf7f59bd6bd9/625d7733c30b4666b4a522d... )
Here is an example:
{
"role": {
"name": "MyHDIRole",
"object_privileges": [{
"name": "MyFolder%",
"type": "VIEW",
"privileges": ["SELECT"],
"pattern_mode": "include"
}]
}
}

[aleksandrs_antonuks2 · Accepted solution]

Hi Cocquerel,

Thank you for the reply!

My actual problem is that I want to hide views that are required to execute consumption view and are currently visible since users have to have SELECT privilege on them. If this privilege is revoked, they wont see them and wont be able to execute the consumption view. 

Current:

Current

Desired

Desired

Is that something that is achievable?

Br,
Aleks.

[Cocquerel · Accepted solution]

Hi Alexs,
If your calculation views are using security mode "definer" that is the default. The users only require SELECT privilege on the top views and not the underlying views.

[aleksandrs_antonuks2 · Accepted solution]

Hi Cocquerel,

This property was changed to invoker's for some reason. Once changed back to definer's, it worked like a charm!

Thank you very much for your advise!

Br,
Aleks.