Skip to Content
SAP HANA

Dynamic SQL Analytic Privilege in SAP HANA

Tags:

I would to like to take you through this new feature ‘Dynamic SQL analytic privilege in SAP HANA’ which is available from SAP HANA SPS10

Before going into details a quick introduction to ‘what is ANALYTIC PRIVILEGE?’

     Analytic privileges grant different users access to different portions of data in the same view (can also be termed as row level authorizations) based on their business role.


We can implement analytic privilege in two ways

  • Classical XML- based Analytic Privileges.
  • SQL Analytic Privileges.


Here we would be focusing on ‘How You Can Create Dynamic SQL Analytic Privilege in SAP HANA ’, which will provide you the flexibility to create analytic privilege within the familiar SQL environment.

To get a detailed explanation on ‘Classical XML- based Analytic Privilege’, please check out this blog by Former Member


OK let’s start!


Here is my Analytical view on which I want to apply SQL Analytic Privilege, as you can see below you will have to Select ‘SQL Analytical Privileges’ in the view properties when you create the view.




The output of the view looks like this


I would want this view to be accessed by another user PRI_TEST_USER,but only those rows where PUBLISHER name starts with letter ‘S’ ,should be visible to this user.


So the steps to achieve this use case is as follows,


1: Create an authorization table to store the USERNAME and the CONDITION to restrict the data

2: Create a procedure which will provides the matching condition based on user login

3: Create Analytical privilege of SQL type.

4: Assign the created Analytical privilege to the USER.


1: Create an Authorization table to store the Uername and the Condition to restrict the data


CREATE COLUMN TABLE "PRIYANKA"."PRODUCT_AP_DYNAMIC_AUTH"

("USER_NAME" NVARCHAR(120),"CONDITION" NVARCHAR(300));

insert into "PRIYANKA"."PRODUCT_AP_DYNAMIC_AUTH" values('PRI_TEST_USER','(PUBLISHER LIKE ''s%'')');

insert into "PRIYANKA"."PRODUCT_AP_DYNAMIC_AUTH" values('HE2E_USER','(PUBLISHER IN (''barnes & noble'',''ballantine'')')



2: Create a procedure which will provides the matching filter condition based on user login.

(There are few rules to followed while creating procedure, which is well explained in the HANA Modelling Guide : Section 9)


create procedure "PRIYANKA".DYNAMIC_AP_DEMO(out OUT_FILTER VARCHAR(500))

language sqlscript sql security definer reads sql data as

v_Filter VARCHAR(500);  

CURSOR v_Cursor

FOR SELECT "CONDITION" FROM "PRIYANKA"."PRODUCT_AP_DYNAMIC_AUTH"

WHERE "USER_NAME" = SESSION_USER;

BEGIN

OPEN v_Cursor;  

FETCH v_Cursor INTO v_Filter;

OUT_FILTER := v_Filter;

CLOSE v_Cursor; 

END;



3: Create Analytical privilege of SQL type.

  • Right click on your package under Content folder->new->Analytic privilege
  • Provide details as required

  • Click on Next
  • Select the view which you have created for applying Analytic Privilege

  • Click on Finish
  • In the editor select 'Dynamic' radio button.
  • Type: Catalog procedure
  • Select the procedure which you have created in the previous step

  • Activate your Analytic Privilege.



4: Assign the created Analytical privilege to the USER.


  • Expand security node, expand 'Users' node, double click on the user for which you want to apply the restriction
  • Click on Analytic Privilege.
  • Click on the green plus icon and add the Analytic Privilege which we have created.
  • Click on execute button.



That's it ,We are done with the creation steps, shall we see the Result!!!

  • Login as PRI_TEST_USER
  • Open the Analytical view
  • Click on data preview


There it is 

Its my first document here on SCN, Hope it was helpful to you, any suggestion for improvement would be much appreciated ,Thank you

Former Member