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

  /wp-content/uploads/2014/02/1_382223.jpg

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


/wp-content/uploads/2014/02/2_382224.jpg

   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.

/wp-content/uploads/2014/02/3_382233.jpg

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


/wp-content/uploads/2014/02/4_382234.jpg

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.


/wp-content/uploads/2014/02/5_382247.jpg

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.

/wp-content/uploads/2014/02/6_382248.jpg

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

/wp-content/uploads/2014/02/7_382249.jpg

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

/wp-content/uploads/2014/02/8_382250.jpg

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

/wp-content/uploads/2014/02/9_382251.jpg

Using Catalog Procedure


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

/wp-content/uploads/2014/02/10_382252.jpg

Now view the data using TEST User.

/wp-content/uploads/2014/02/11_382253.jpg

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.

/wp-content/uploads/2014/02/12_382254.jpg

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Madalina Trifan

    Hello,

    I am trying to implement the dynamic privilege using a Catalog procedure build in the repository with the .hdbprocedure syntax.

    When trying to access the analytic view with test user, I receive the same error with “user is not authorized”.

    I try to GRANT EXECUTE on procedure to _SYS_REPO using SYSTEM user and I get the lack of privileges error. What rights do I need to be able to execute the grant on SQL?

    Thanks,
    MadalinaError1.PNG

    (0) 
  2. Srinivas kobbari

    Hi Jagan

    Is there a way in HANA Analytical privilege to exclude particular value…

    Scenario…..

    we have a region specific analytical privilege like REGION1 which has around 10 company codes…Right now the setup in AP is if we grant access to REGION1,user will get access to All company codes.

    But we want to exclude one company code out of 10.

    Do we have any option to exclude a particular value in Analytical privilege?

    I see that we have below operators available but no Exclude operator

    =

    <

    >

    <>

    Null

    Not Null

    Pattern

    Please advise.

    Thank you

    Srinivas

    (0) 
  3. Kevin Geiger

    Does this work if more than one value is returned by the SQL request?  I’m attempting to implement this as a catalog procedure in SPS09 (rev. 97)

    (0) 
  4. Anujit Ghosh

    Hi Jagan,

    In place of actually creating the AUTH_INFO_FILTER table, I think just creating a Table Type with the same name will do as you will just be using the structure for output and not for any storage. So the following should work.

    Replace

    CREATE COLUMN TABLE “SCHEMA_TEST”.“AUTH_INFO_FILTER” (“REGION” VARCHAR(20))


    with


    CREATE TYPE “SCHEMA_TEST”.“AUTH_INFO_FILTER” AS TABLE (“REGION” VARCHAR(20))


    Regards

    Anujit Ghosh

    (0) 

Leave a Reply