Skip to Content
Author's profile photo Raj Kumar S

Dynamic filter – Advanced

This documents is prepared based on version HANA 1.0 SPS 05 revision 46.

Analytic privilege is used for restricting data access based on

  • View
  • Activity
  • Validity
  • Attribute

Attribute restrictions can be either static or dynamic. Static value filter consists of operator and either a single value or a list of values (for example STATE = ‘CA’). Dynamic value filter consists of operator and a stored procedure call that determines the value at the run time.

In this document we are going to create dynamic value filter in attribute restriction of Analytic privileges.

Procedure used to define filter conditions :

  • must have the security mode “Definer”
  • must be read-only procedure
  • can return valid values or NULL

Analytic privileges can be created either through HANA modeler or using SQL. HANA modeler is the recommended method for creating and managing Analytical privileges. However  in some cases it is necessary to use SQL to implement those features of analytic privileges not available in HANA modeler, such as this one (dynamic value filter).

Analytical privileges created in HANA modeler are owned by technical user _SYS_REPO and in order to available in database they must be activated, and to grant and revoke the privilege the user needs EXECUTE on the procedures GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE and REVOKE_ACTIVATED_ANALYTICAL_PRIVILEGE .

Analytical privileges created using SQL statements are owned by the database user who executes the SQL statements and are activated immediately and only the creator can grant and revoke it.

Disadvantages:

  • If the database user who created the analytic privilege is deleted, all objects owned by the user will also be deleted. Hence a dedicated database user (i.e. technical user) to be used for such purposes.
  • These privileges will not be in repository and hence they cannot be transported between different systems. Hence they have to be executed by technical user in each system.

Run time authorization:

When an user requests access to data stored in Attribute/Analytic/Calculation view, an authorization check is performed based on analytic privileges and the filtered data is returned to the user accordingly.


Scenario:

There is a table (BU_DETAILS) in a schema “SRK” which contains data as shown below:

  Table_Details.JPG

An Analytic view (AN_DYNAMIC) is built based on the above table in a package “srk” as shown below:

AN_DYNAMIC.JPG

An Analytic privilege is created dynamically (using SQL) based on the above view by the dedicated database user based on the attribute (BU_ID) and the access is restricted to user by stored procedure call that determines the value at the run time where the user names will be stored in separate table along with the BU_ID to which (s)he has the access.

I am creating the procedure, table, table type with the user “SYSTEM”.


Steps:


1. Create a table (row table, as we not do any calculations on it) with two columns USER_NAME and BUS_UNIT and has only 1 record for USER1 who has access to BUS_UNIT = 1 as shown below:

     CREATE TABLE “SRK”.”BU_AUTH” (“USER_NAME” NVARCHAR(128), “BUS_UNIT” int);

Note:The same user can have access to multiple BUS_UNIT and multiple users can have access to the same BUS_UNIT.

     User_Table.JPG

2. Create a procedure which determines whether the session user has access to BUS_UNIT based on the table created in Step 1 and returns the list of BUS_UNIT to which (s)he has the access. To store multiple BUS_UNIT, first we will create a table type for this. This will be created by the user whose ID will never be deleted.

   CREATE TYPE “SRK”.”BUSINESS_UNIT” AS TABLE(“BUS_UNIT” int);

   CREATE PROCEDURE “SRK”.”USER_AUTH_BU” (OUT VAL “SRK”.”BUSINESS_UNIT”)

      LANGUAGE SQLSCRIPT SQL SECURITY DEFINER READS SQL DATA AS

   BEGIN

       VAL = SELECT BUS_UNIT FROM “SRK”.”BU_AUTH”

                  WHERE USER_NAME = SESSION_USER;

   END;

3. Now we will create privilege using SQL in SQL Editor as

CREATE STRUCTURED PRIVILEGE ‘<?xml version=”1.0″ encoding=”utf-8″?><analyticPrivilegeSchema version=”1″>

<analyticPrivilege name=”srk/STRUCT_PRIV_AN_DYNAMIC“>

<cubes>

      <cube name=”_SYS_BIC:srk/AN_DYNAMIC” />

      <cube name=”_SYS_BIC:srk/AN_DYNAMIC/olap” />

</cubes>

<validity> <anyTime/> </validity>

<activities> <activity activity=”read” /> </activities>

<dimensionAttributes>

  <dimensionAttribute name=” srk/AN_DYNAMIC$BU_ID “>

   <restrictions>

     <valueFilter operator=”EQ”> <value value=”1″></value></valueFilter>                 

     <valueFilter operator=”IN”>

       <procedureCall schema=”SRK” procedure=”USER_AUTH_BU“/>                        

     </valueFilter>  

   </restrictions>

  </dimensionAttribute>

</dimensionAttributes>

</analyticPrivilege></analyticPrivilegeSchema>’;

After executing the above statement, the structured privilege will not be created in the package “srk” as analytic privilege does but will be created in system table called “STRUCTURED_PRIVILEGES” which can be found in schema “SYS”.

4. Let us check the above privilege using below query:

Select * from STRUCTURED_PRIVILEGES

where STRUCTURED_PRIVILEGE_NAME like ‘srk/STRUCT_PRIV_AN_DYNAMIC

order by STRUCTURED_PRIVILEGE_NAME

;

The result is:

       Struct_Priv.JPG

5. Now this privilege can be assigned manually by the database user (SYSTEM) to the authorized user (say USER1) using SQL statement 

    GRANT STRUCTURED PRIVILEGE “srk/STRUCT_PRIV_AN_DYNAMIC” TO USER1;

Now any user whenever access the analytic view AN_DYNAMIC, it executes the procedure USER_AUTH_BU and checks whether the session user is authorized or not and also the BUS_UNIT associated with the user as contained in the table BU_AUTH (of Step 1).


6. The USER1 have the access to view the data on Analytic view (but restricted to view data for BUS_UNIT = 1 only ).

If other users (say USER2) who do not have access to the view, when tries to access the view, then error message “Not Authorized” will be shown.

7. To cancel the privilege already given to the user, then execute the below SQL:

         REVOKE STRUCTURED PRIVILEGE “srk/STRUCT_PRIV_AN_DYNAMIC” FROM USER1;

That’s it we are done with creating dynamic filters .

Thank you.

Assigned Tags

      27 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Azeem Quadri Mohammed Abdul
      Azeem Quadri Mohammed Abdul

      Very informative. Thank you.

      Author's profile photo Rama Shankar
      Rama Shankar

      Good step-by-step - thanks1

      Author's profile photo Former Member
      Former Member

      Good one!!!!!!!!! thank you

      Author's profile photo Former Member
      Former Member

      Good Information and very useful. Thanks Raj

      Author's profile photo Raj Kumar S
      Raj Kumar S
      Blog Post Author

      Thanks Azeem and Rama

      Author's profile photo Patrick Bachmann
      Patrick Bachmann

      Very cool post Raj!  I had no idea that you could do a dynamic filter like this. Definitely good to know.

      -Patrick

      Author's profile photo Raj Kumar S
      Raj Kumar S
      Blog Post Author

      Thank You Patrick

      Author's profile photo Ravindra Channe
      Ravindra Channe

      Nicely documented. Really good blog.

      Regards,

      Ravi

      Author's profile photo Raj Kumar S
      Raj Kumar S
      Blog Post Author

      Thank you Ravi 🙂

      Author's profile photo Former Member
      Former Member

      Good & useful.,

      Author's profile photo Former Member
      Former Member

      I did not see the people in scn like you(raj) this much clear information on topics posted by you...it's really good..thaks raj.

      Author's profile photo Former Member
      Former Member

      Hi Raj,

      Good Docuement, Niecly presented with screen shots.

      Regards,
      Giri

      Author's profile photo Former Member
      Former Member

      Good one!!!! Thank You Raj..

      Author's profile photo Former Member
      Former Member

      Thanks for sharing the info.

      Regards,

      Chandrakanth.

      Author's profile photo Former Member
      Former Member

      Really useful. Good job.

      Author's profile photo Raman Korrapati
      Raman Korrapati

      Thanks for sharing raj.

      Author's profile photo Former Member
      Former Member

      Hi Raj,

      What do you mean by :

      "

      • These privileges will not be in repository and hence they cannot be transported between different systems. Hence they have to be executed by technical user in each system.

      "

      Does that mean these analytic privileges needs to be created manually by SYSTEM user in each system ?

      Can you please elaborate ?

      Thanks & regards,

      Jomy

      Author's profile photo Justin Molenaur
      Justin Molenaur

      Jomy, in this example he is only creating these analytic privileges in the CATALOG (under a schema) as opposed to the CONTENT (which is attached to a package).

      Using stored procedures is supported in AP's that are created in content (at least in revision 62), so I would highly suggest going this route as its more flexible.

      To answer your question, if you created these in CONTENT, you could simply migrate the AP's (as packages attached to delivery units) as you normally would (either import or CTS+) as opposed to having a SYSTEM user execute scripts to generate in each required system.

      Hope this makes sense.

      Regards,

      Justin

      Author's profile photo Former Member
      Former Member

      Hi Justin,

      I understand but you can also migrate the procedures created in Catalog and that's the reason I got confused.

      Regards,

      Jomy

      Author's profile photo Justin Molenaur
      Justin Molenaur

      Hi Jomy - just out of curiosity - how are you migrating the catalog procedures?

      Regards,

      Justin

      Author's profile photo Former Member
      Former Member

      Hi Justin,

      Catalog procedure can be exported via

      FILE->EXPORT->SAP HANA Studio->Catalog Objects. Now type in yor Procedure Name and export it to a folder.

      In the same way, import it as well.

      I hope this makes sense.

      Thanks & regards,

      Jomy

      Author's profile photo Dietmar Kaiser
      Dietmar Kaiser

      I understand that if you want to change such a privilege you have to delete and re-create it. How can I delete such a privilege?

      Author's profile photo Raj Kumar S
      Raj Kumar S
      Blog Post Author

      To drop it, the system privilege STRUCTUREDPRIVILEGE ADMIN is required:

      Syntax:

      DROP STRUCTURED <privilegename>; or

      DROP STRUCTURED PRIVILEGE <privilegename>;

      Also you can alter structured privilege using ALTER STRUCTURED PRIVILEGE. This I not tried.

      Regards

      Raj

      Author's profile photo Former Member
      Former Member

      This document was really helpful in understanding dynamic analytic privileges - thanks a lot, Raj!

      Author's profile photo Former Member
      Former Member

      Hi Rajkumar , 

      Thank you for sharing valuable information . 

      Very clear presentation 🙂

      -Rajesh N

      Author's profile photo Former Member
      Former Member

      A little late though, but thanks a lot for the useful piece of information.

      Author's profile photo Edson Rocha Loureiro Junior
      Edson Rocha Loureiro Junior

      This info was really helpfully to me too. 😉

      thanks