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

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

   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.

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

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.

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.

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

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

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

Using Catalog Procedure

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

Now view the data using TEST User.

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.