Restricting _SYS_BIC views using stored-procedures

former_member197922 · ‎05-02-2016

Introduction

The activated models in HANA results in Column Views under _SYS_BIC schema. The end users using front end tool like SAP BusinessObjects (IDT), SAP Lumira, SAP Predictive Analytics, etc. need to have SELECT rights on the views in _SYS_BIC to consume the activated HANA models. Current design in HANA is such that the end user has access to all of the activated views under _SYS_BIC or none. This raises a challenge of how to give access only to a specific view or set of views to a user/role and restrict the user/role from accessing other views which are irrelevant to him/her. In this article I have made an attempt using stored procedure approach to allow and restrict access to activated views in _SYS_BIC.

Create A Role

Using Studio : Login to HANA system using SAP HANA Studio. Navigate to Security-Roles. Right click on Roles and select New Role. Enter the Role name, say SYS_BIC_PACKAGE_A_READ and activate it.

Using scripts:

role demoroles.roles::SYS_BIC_PACKAGE_A_READ{

}

NOTE: No body inside the script.

Refer to A step-by-step guide to create design-time (script based) Roles in SAP HANA for creating script based roles.

Stored Procedure to assign _SYS_BIC views to a Role

The source code for the procedure - SPROC_GRANT_SELECT_ON_SYS_BIC_VIEWS , to assign _SYS_BIC views to a role is given below. This procedure takes 2 parameters – viewName - name of the view that is to be assigned to the role and the roleName. The viewName parameter can take wild card (explained in Assign Role to user/role section below).

The system view VIEWS contains list of all the views in HANA including the developed and activated SAP HANA model views – Attribute, Analytic and Calculated Views. The GRANTED_PRIVILEGE view contains all the privileges assigned to a Role/User. The procedure uses these two views and defines a cursor (line 9) to retrieve only the activated views (parameter1) that are not assigned to the role/user (parameter 2). If the passed view (parameter1) is already assigned to the passed role (parameter2), the procedure simply exists without any action on the parameters. This cursor selects only views of type JOIN, CALC or OLAP. The cursor declared in line 9 is modified in line 14 if there is no wild card in parameter viewName

The code at line 18

dynSQL:= 'delete from YOURSCHEMA.DUMPTABLE'.

is optional. I used DUMPTABLE with 1 character column of size 5000 to capture the GRANT statement generated and to verify the views that are selected by the procedure. Kind of debugging the procedure. Line 19 empties the table and line 25 inserts the generated GRANT statement.

Lines 21 to the end– For loop, to process the cursor result set, generates the GRANT statement and executes it for each record in the cursor result set.

CREATEPROCEDURE "YOURSCHEMA"."SPROC_GRANT_SELECT_ON_SYS_BIC_VIEWS" (in viewName varchar(100), in roleName varchar(100) )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
DECLARE dynSQL VARCHAR(500) :='';
DECLARE CURSOR SYS_BIC_VIEWS for SELECT view_name FROM VIEWS WHERE SCHEMA_NAME = '_SYS_BIC' AND VIEW_NAME LIKE :viewName and view_type in ('CALC','JOIN','OLAP') and is_valid='TRUE'
and view_name not in (select object_name from public.granted_privileges where object_name like :viewName and grantee=:roleName and is_valid='TRUE');
if LOCATE(trim(viewName),'%',1,1)=0 --if there is no wildcard in the view name, remove "like" clause from cursor definition.
then
DECLARE CURSOR SYS_BIC_VIEWS for SELECT view_name FROM VIEWS WHERE SCHEMA_NAME = '_SYS_BIC' AND VIEW_NAME=:viewName and view_type in ('CALC','JOIN','OLAP') and is_valid='TRUE'
and view_name not in (select object_name from public.granted_privileges where object_name = :viewName and grantee=:roleName and is_valid='TRUE');
end if;
dynSQL := 'delete from YOURSCHEMA.DUMPTABLE';
exec dynSQL;
FOR rs_SYS_BIC_VIEWS as SYS_BIC_VIEWS DO
if LOCATE(trim(rs_SYS_BIC_VIEWS.view_name),'/olap',1,1)=0 --remove /olap view components if they present in result set.
then
dynSQL:='GRANT SELECT ON "_SYS_BIC"."'||rs_SYS_BIC_VIEWS.view_name||'" to "'||roleName||'"';
insert into"YOURSCHEMA"."DUMPTABLE"values(dynSQL);
exec dynSQL;
end if;
END FOR;
END;

Stored Procedure to revoke _SYS_BIC views from a Role

This procedure is used to revoke the assigned views from a role/user. The functional structure of this procedure is same as the previous procedure.

CREATE PROCEDURE"YOURSCHEMA"."SPROC_REVOKE_SELECT_FROM_SYS_BIC_VIEWS" (in viewName varchar(100), in roleName varchar(100) )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
DECLARE vname VARCHAR(500) := :viewName;
DECLARE CURSOR grantedPrivileges for select OBJECT_NAME from PUBLIC.GRANTED_PRIVILEGES where object_name like :viewName and GRANTEE=:roleName and schema_name='_SYS_BIC';
DECLARE dynSQL VARCHAR(500) :='';
DECLARE grantString VARCHAR(50) :='GRANT SELECT ON';
if LOCATE(trim(viewName),'%',1,1)=0 --if there is no wildcard in the view name, remove "like" clause from cursor definition.
then
DECLARE CURSOR grantedPrivileges for select OBJECT_NAME from PUBLIC.GRANTED_PRIVILEGES where object_name = :viewName and GRANTEE=:roleName and schema_name='_SYS_BIC';
end if;
dynSQL := 'delete from YOURSCHEMA.DUMPTABLE';
exec dynSQL;
FOR grantedPrivilegeRS as grantedPrivileges DO
dynSQL:='REVOKE SELECT ON "_SYS_BIC"."'||grantedPrivilegeRS.object_name||'" from "'||roleName||'"';
insert into"YOURSCHEMA"."DUMPTABLE" values(dynSQL);
exec dynSQL;;
END FOR;
END;

Assign/Revoke privilege/rights to the user/role

In the SAP HANA open the above created role SYS_BIC_PACKAGE_A_READ and make sure there are no entries under Granted Roles, Part of Roles, System Privileges, Object Privileges, Analytic Privileges, Package Privileges, Application Privileges and Privileges on Users.

Let us assume that your HANA views have the following structure

Root package

a

b

c

Attribute Views

AT_MY_ATVIEW_DATE_DIM

AT_MY_ATVIEW_REGION_DIM

Analytic Views

AV_MY_AVVIEW_SALES

AV_MY_AVVIEW_APPLICATIONS

Calculation Views

CL_MY_CLVIEW_ONE

CL_MY_CLVIEW_TWO

Usage scenarios

To assign all views under the package a.b.c to SYS_BIC_PACKAGE_A_READ role, use wild card in the parameter 1 as given below.

CALL “YOURSCHEMA"."SPROC_GRANT_SELECT_ON_SYS_BIC_VIEWS"(‘a.b.c%’,’ SYS_BIC_PACKAGE_A_READ’);

Now verify that the role has the views added to it under Object Privileges tab.

2. To assign a single view say AV_MY_AVVIEW_APPLICATIONS to say roleB, give the absolute path of the view for the parameter1 as given below.

CALL “YOURSCHEMA"."SPROC_GRANT_SELECT_ON_SYS_BIC_VIEWS"(‘a.b.c.AV_MY_AVVIEW_APPLICATIONS’,’roleB);

3. To revoke all the assigned views under the package a.b.c from SYS_BIC_PACKAGE_A_READ role, use wild card in the parameter 1 as given below

CALL YOURSCHEMA.SPROC_REVOKE_SELECT_FROM_SYS_BIC_VIEWS (‘a.b.c%’,SYS_BIC_PACKAGE_A_READ);

Now verify that the role has no views listed under Object Privileges tab.

4. To revoke a single view from a role, pass the absolute path of the view in parameter 1 as given below.

CALL YOURSCHEMA.SPROC_REVOKE_SELECT_FROM_SYS_BIC_VIEWS (‘a.b.c.AV_MY_AVVIEW_APPLICATIONS,SYS_BIC_PACKAGE_A_READ);

5. If there are new model developed and activated during the development process and all the new _SYS_BIC column views of the new models need to be assigned to the role – call revoke procedure to revoke all the granted views and call assign procedure to grant all the views, including the new models.

Revoke all views from the role:

CALL YOURSCHEMA.SPROC_REVOKE_SELECT_FROM_SYS_BIC_VIEWS(‘a.b.c%’,SYS_BIC_PACKAGE_A_READ);

Assign all views to the role (including new views):

CALL “YOURSCHEMA"."SPROC_GRANT_SELECT_ON_SYS_BIC_VIEWS"(‘a.b.c%’,’ SYS_BIC_PACKAGE_A_READ’);

References

SAP HANA Administration Guide
SAP HANA Developer Guide
SAP HANA System Views Reference

Author

Pals Nagaraj, PMP, CMC is a Technology/Management consultant with extensive experience in providing Business Analytics Solutions using SAP BI, SAP Data Services, SAP HANA and Analytics platforms to federal, state and commercial clients. He is certified in SAP BI and SPA HANA. He be reached at pals@strategicitech.com.