Skip to Content

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.

To report this post you need to login first.

27 Comments

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

  1. 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

    (0) 
    1. Former Member

      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

      (0) 
          1. 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

            (0) 
    1. Raj Kumar Salla 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

      (0) 

Leave a Reply