Security challenge: separate access to views sitting on _SYS_BIC

[Former Member]

Hi All,

We are currently facing a challenge in our project around security. We are working on HANA 1.0 SP07 rev73 and using tableau for visualisation.

There is content that need to be secured and at a package and schema level is fine (we've created a separate schema for this data). The roles we've created do their job with some gaps. The users that don't have these roles can't query the tables in the secure schema and they can't see the information models created in the secure package. however, the challenge is when building information models around this data, all these views get activated under the _SYS_BIC schema and users that don't hold the role for this secure data can still execute them and see them because of their select privilege on _SYS_BIC. Tableau's connector is done by selecting the column view from the _SYS_BIC schema which makes these views visible.

I understand that this happens because of having SELECT access on _SYS_BIC for all users. As far as I know there is no star (*) functionality to segregate access per models in each package and that we'll need to give select access to each separate model to do so. This, as you can see, is not very efficient from a maintenance point of view because for every model that developers build security will have to add it to their role for them to test it.

Other alternative we've thought of was to write a stored procedure to manage this granular granting every 5 minutes which will assign the latest models created to the corresponding role.

So, has anyone out there had a similar scenario and worked out an effective solution to it?

all suggestions are welcome.

Best regards,

Christian.

[justin_molenaur2 · Accepted solution]

Hi Christian, there was an interesting series of events that led to this issue.

Originally, analytic privileges were considered as well when authorizations for SELECT access were returned (definition of view BIMC_CUBES). This was accomplished with a join to ACCESSIBLE_VIEWS, which uses the analytic privileges to drive the SELECT access.

Only in HANA revisions 60-63 in place, therefore you need to manually define the SELECT access for models AND maintain the analytic privileges for any revision outside of this.

You could take the approach as you mention OR you can modify the BIMC_CUBES definition according to OSS note 1907697, which will determine authorization at runtime and only display the authorized views WITHOUT having to manually define the SYS_BIC select access.

Happy HANA,

Justin

Reason and Prerequisites

The clients do the browing by accessing the view _SYS_BI.BIMC_CUBES. The returned views are not

filtered to only return the authorized views.

Solution

SP07: With SP07 the definition of the view BIMC_CUBES is changed in a way that only those

analytic/calculation views are returned for which the user has SQL privileges.

Attention: We are not checking the analytical privileges here because this would lead to

inacceptable performance when querying BIMC_CUBES. Therefore to protect the views you have to

maintain SQL privileges in addition to analytic privileges.

Revision 60 to 63: The definition of _SYS_BI.BIMC_CUBES included an SQL part of select from

SYS.ACCESSIBLE_VIEWS which was showing only the views/cubes for which the user has permissions

(analytical privileges) to select from. This had the drawback of slowing down the performance.

With revision HDB 64 until the last SP06 revision: The _SYS_BI.BIMC_CUBES view is reverted

to its original definition, excluding the feature for selecting only the user authorized views.

In case selecting only the user authorized views/cubes is required. The _SYS_BI.BIMC_CUBES view

may be manually changed with the risk of performance issues on that system. The performance

degradation on multiple selects on _SYS_BI.BIMC_CUBES view ist most likely to occur on a large

HDB instance with over 1000 views in the _BIC schema.

SQL definition of the _SYS_BI.BIMC_CUBES

view using SYS.ACCESSIBLE_VIEWS is:

DROP VIEW "_SYS_BI"."BIMC_CUBES";CREATE VIEW "_SYS_BI"."BIMC_CUBES" ( "CUBE_ID",

"CATALOG_NAME",

"SCHEMA_NAME",

"CUBE_NAME",

"CUBE_TYPE",

"CUBE_GUID",

"CREATED_ON",

"LAST_SCHEMA_UPDATE",

"SCHEMA_UPDATED_BY",

"LAST_DATA_UPDATE",

"DATA_UPDATED_BY",

"DESCRIPTION",

"ANNOTATIONS",

"COLUMN_OBJECT",

"DEFAULT_SCHEMA" ) AS SELECT

T1.CUBE_ID,

T1.CATALOG_NAME,

T1.SCHEMA_NAME,

T1.CUBE_NAME,

T1.CUBE_TYPE,

T1.CUBE_GUID,

'T1.CREATED_ON,

'T1.LAST_SCHEMA_UPDATE,

T1.SCHEMA_UPDATED_BY,

T1.LAST_DATA_UPDATE,

T1.DATA_UPDATED_BY,

T1.DESCRIPTION,

T1.ANNOTATIONS,

T1.COLUMN_OBJECT,

T1.DEFAULT_SCHEMA

from "_SYS_BI"."BIMC_ALL_CUBES" T1,

ACCESSIBLE_VIEWS T2

where INTERNAL_CUBE = 0 and T2.VIEW_NAME = concat(concat(T1.CATALOG_NAME,'/'),T1.CUBE_NAME)

and USER_NAME = CURRENT_USER;

[Former Member · Accepted solution]

Thank you

I appreciate your time put in replying to my question and also to validate my option on handling this with a SP.

According to the note you mentioned there is a risk of performance issues when having more than 1000 views and we have a lot more than 1000 views across the whole business so for the moment we'll stay with the SP.

Do you know if there is any improvements coming up on SP09 around this?

I know there is a new web interface for implementing roles.

Regards,

Christian

[justin_molenaur2 · Accepted solution]

I don't know - it might be worth a second look to see how performance REALLY is impacted. That's a fairly vague statement in the OSS note, so it might be worthwhile to create a view with that above definition and run side by side vs. the standard. Just querying it for a single user should give you enough information to make an informed decision.

It might come back that its 40% slower, but we talking 100ms vs. 140ms, know what I mean?

If you are considering a workaround with a SP handling security running every 5 minutes, then it's probably worth considering this as a more "standard" approach with a few lines of code one time only

Regards,

Justin

[Former Member]

Thanks Christian,

Your information is very helpful.

We are still in POC concept for HANA and we have planned to have three packages like: Common, query and restricted.  We have not yet created any reports for restricted package yet where we need to have this secure restriction. However I am wondering as how we can restrict the access with out giving select privilege on " SYS_BIC".

Our end users access these reports through BOBJ.

Your procedure looks good to start with. Let me take this to my architect and let me know if I can contact you over email for any further queries in this regard.

Thanks,

Kalpana.

[Former Member]

Thanks Christian, for posting this query. I am also looking for a solution for this situation.

If anyone has effective solution for this, please let us know.

Thanks,

Kalpana.

[Former Member]

Kalpana,

Many of the constraints come from the specific scenario you're working on, like visualization tool in use, BU doing modeling or IT, etc.

So, are you also using Tableau as visualization tool? if not, what are you using?, who is developing the models? are they all supposed to see the models? How are the consumer accessing the data?

I could set this up with a stored procedure that updates a secured role with the latest information models developed. Here is so you can see if it works for you.

This is my workaround for now until I find a better way (hope to find more insights here with suggestions and comments from anyone else):

/********* Begin Procedure Script ************/

--Scalar variables

v_total INTEGER;

i INTEGER;

v_view_temp NVARCHAR(100);

v_role_validation INTEGER;

BEGIN

   /* Selection of all olap and calc views (old and new ones) from a given package */

   SELECT DISTINCT count(*)

   INTO v_total

   FROM "SYS"."VIEWS"

   WHERE ("SCHEMA_NAME" = '_SYS_BIC' AND "VIEW_TYPE" IN ('OLAP', 'CALC') AND "VIEW_NAME" LIKE '%'||:IP_PACKAGE||'%');

   SELECT count("ROLE_NAME")

   INTO v_role_validation

   FROM "SYS"."ROLES" where ROLE_NAME = :IP_ROLE;

   /* If rol doesn't exist do nothing */

   IF :v_role_validation = 0 THEN

    SELECT 'INVALID ROLE NAME' AS PRINT FROM DUMMY;

   ELSE

  FOR i IN 0 .. :v_total - 1 DO

  SELECT DISTINCT "VIEW_NAME"

  INTO v_view_temp

  FROM "SYS"."VIEWS"

  WHERE ("SCHEMA_NAME" = '_SYS_BIC' AND "VIEW_TYPE" IN ('OLAP', 'CALC') AND "VIEW_NAME" LIKE '%'||:IP_PACKAGE||'%')

  LIMIT 1 OFFSET :i;

  exec 'grant select on "_SYS_BIC"."'||:v_view_temp||'" to '||:IP_ROLE;

  END FOR;

  END IF;

END;

/********* End Procedure Script ************/