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.
Very informative. Thank you.
Good step-by-step - thanks1
Good one!!!!!!!!! thank you
Good Information and very useful. Thanks Raj
Thanks Azeem and Rama
Very cool post Raj! I had no idea that you could do a dynamic filter like this. Definitely good to know.
-Patrick
Thank You Patrick
Nicely documented. Really good blog.
Regards,
Ravi
Thank you Ravi 🙂
Good & useful.,
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.
Hi Raj,
Good Docuement, Niecly presented with screen shots.
Regards,
Giri
Good one!!!! Thank You Raj..
Thanks for sharing the info.
Regards,
Chandrakanth.
Really useful. Good job.
Thanks for sharing raj.
Hi Raj,
What do you mean by :
"
"
Does that mean these analytic privileges needs to be created manually by SYSTEM user in each system ?
Can you please elaborate ?
Thanks & regards,
Jomy
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
Hi Justin,
I understand but you can also migrate the procedures created in Catalog and that's the reason I got confused.
Regards,
Jomy
Hi Jomy - just out of curiosity - how are you migrating the catalog procedures?
Regards,
Justin
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
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?
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
This document was really helpful in understanding dynamic analytic privileges - thanks a lot, Raj!
Hi Rajkumar ,
Thank you for sharing valuable information .
Very clear presentation 🙂
-Rajesh N
A little late though, but thanks a lot for the useful piece of information.
This info was really helpfully to me too. 😉
thanks