Thursday, 4 February 2016

Dynamic Analytic Privileges Using Procedures in SAP HANA

The analysis authorizations in SAP HANA give control on how you can control the users while viewing the data in HANA Artifacts. SAP HANA provides several different layers of security from which you can benefit, and in this article we'll be looking at Analytics privileges. We'll examine how we can create dynamic Analytics privileges on HANA Artifacts and how they control the data for different users while they are accessing HANA Models from Studio or from Third party reporting tools.

Here we have analytic view AN_EMPLOYEE which contains Employee salary data based on region and Country wise
SAP HANA Certifications

We will restrict the user XXXX_TEST for APAC Region.

Here is the sequence of steps to achieve this

1) Create Procedure in Repository level or Catalog level which will give dynamic Output to Analytic Privileges.
2) Create Analytic Privilege based on Repository Procedure or Catalog Procedure 
3) Assign the Analytic Privilege to User to restrict the data on HANA Views.

1) Create Procedure in Repository level or Catalog level which will give dynamic Output to Analytic Privileges

Procedure Rules:
  • DEFINER procedures
  • Read-only procedures
  • Procedures must have a predefined signature as follows:
    • No input parameter/s
    • For the “IN” Operator, only 1 output parameter defined as a Table Type with a single column
    • For all Unary Type Operators (EQ, CP, LT, GT, LE, GE), only 1 output parameter defined as a Scalar Type
    • For the Binary Type Operator (BT), only 2 output parameters defined as Scalar Types
      • CAUTION
      • This means you cannot use multiple BT ranges or multiple CP patterns in the same procedure. This can have an impact on the design of your solution, specifically when translating the Authorization Mappings in BW to appropriate filter conditions in HANA
      • Only the following data types are supported for output parameters:
      • Datetime types DATE, TIME, SECONDDATE, TIMESTAMP
      • Numeric types TINYINT, SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE
      • Character string types VARCHAR, NVARCHAR
      • Binary types VARBINARY
In this section we need to create the SAP HANA Stored Procedures which will select the attribute filter values from the Mapping table. The Stored Procedures will return values for an attribute which a HANA User should have authorization for.

1) Creation of mapping Table in Catalog where it will hold the user name and Authorization values for Region

SAP HANA Analytic Privileges

2) Create a table for Output of filter values for store procedure .we will use this when we creating the catalog procedure.

          CREATE COLUMN TABLE "SCHEMA_TEST"."AUTH_INFO_FILTER" ("REGION" VARCHAR(20))

Repository Procedure:

Create a Procedure with following properties.

Dynamic Analytic Privileges Using Procedures in SAP HANA

In the output we need to define the Output filters structure. In here we are defining the REGION as Output.

SAP HANA Central

In above procedure SESSION_USER is the users who are accessing the views.

Catalog Procedure

Now we will create a catalog procedure where the Output values are displayed using structure AUTH_INFO_FILTER .The Output values are worked as a filter in here.

CREATE PROCEDURE REPO_CATALOG_PROCEDURE (
OUT VALTAB "SCHEMA"."AUTH_INFO_FILTER" )
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
READS SQL DATA AS

BEGIN
VALTAB = SELECT "REGION"
FROM "SCHEMA"."AUTH_INFO" 
WHERE "USER" = SESSION_USER;
END;

Not it is visible in Catalog under your schema.

SAP HANA Central, SAP HANA Certifications

2) Create Analytic Privilege based on Repository Procedure or Catalog Procedure 

Using Repository Procedure
Create analytic privilege using repository procedure which we have created earlier.

Dynamic Analytic Privileges Using Procedures in SAP HANA

Assign the analytic privilege to user to view the restricted data on information model AV_EMPLOYEE

SAP HANA, SAP HANA Material

After assigning the analytic privilege now user having reading access to the analytic views.

SAP HANA Dynamic

Now if User views the data it showing only related to APAC Region.

SAP HANA Analytic

Using Catalog Procedure

Assign the Catalog Procedure to Analytic Procedure by removing the existing repository procedure ( for testing I am doing this )

SAP HANA Dynamic Analytic Privileges

Now view the data using TEST User.

SAP HANA Dynamic Analytic

User is not able to see that data in that Analytic view as the procedure we have is not assigned to “_SYS-REPO”
Execute the below SQL to assign the Procedure to user _SYS_REPO.

Grant execute on "SCHEMA"."REPO_CATALOG_PROCEDURE" to "_SYS_REPO" with grant option

After that user will able to see the data in analytic view.

SAP HANA Certifications

Source: scn.sap.com