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:

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

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.

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:

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.