Analytic privileges are used to enable data access in calculation views by filtering the data based on the values of one or more attributes.
The rationale for analytic privileges is to allow the use of calculation views by different users who might not be allowed to see the same data. This approach means we don't have to create copies of the same calculation views with different filters defined.
For example, different regional sales managers who are only allowed to see sales data for their regions could use the same calculation view, but they would have different analytic privileges assigned to their user ID that specify the regions they're supposed to see.
An analytic privilege can be assigned to multiple users who have the same data access requirements.
Analytic Privileges — The End-to-End Scenario
To secure a calculation view with analytic privilege, the main steps are as follows:
To Create and Assign an Analytic Privilege
Create a source file with the extension .hdbanalyticprivilege.
Assign the calculation views that you want to secure with this analytic privilege.
Choose the type of restrictions that you want to use and define the restrictions.
Set the secured calculation views to check analytic privileges.
Deploy the analytic privilege.
Assign the analytic privilege to a role.
Assign the role to a user.
The next section breaks down the steps in detail:
1. Create the Source File
Select a folder in your project.
Choose New File.
Provide a name and be sure to use the extension .hdbanalyticprivilege.
Select Rename, and enter a label.
2. Select Calculation Views
An analytic privilege definition contains the list of calculation views to which it will apply. You can include any type of calculation view.
3. Define the Restrictions
There are three methods available to define the restrictions in an analytic privilege. These methods are exclusive; that is, in one analytic privilege, you use only one of these methods.
Restriction Types in Analytic Privileges
| Restriction type | How to Use | Restriction Example |
|---|---|---|
| Attribute | With the restriction editor, select one or several attributes from the secured views. For each of them, define restrictions. |
|
| SQL Expression | Create a valid static SQL expression that refers to the attributes and the authorized values. This is useful when the Attribute restriction type does not fulfill the requirement. | ("REGION"=’EMEA’ AND "YEAR"=’2015’) (valid SQL expression) |
| Dynamic | Use a procedure to derive a dynamic SQL expression to restrict the data set. This expression must be similar to a WHERE clause in a select statement. | P_DYNAMIC_AP_FOR_REGION (name of the procedure) |
Defining a Restriction of Type Attribute
Defining Values in the Restriction Filter
To define values in the restriction filter, you can use the following operators:
Between <scalar_value_1> <scalar_value_2>
ContainsPattern <pattern with *>
Comparison operators: =, <=, <, >, >= with <scalar value>
IsNull and Is Not Null
Note
All filter operators, except IsNull and Is Not Null, accept empty strings (" ") as filter operands. For example:
In (" ", "A", "B")
Between (" ", "XYZ") (as lower limit in comparison operators)
Only columns of type Attribute (NOT Measure) can be specified in dimension restrictions.
If a DIMENSION calculation view contains a parent-child hierarchy and the hierarchy is enabled for SQL access, it's also possible to define the restriction on a hierarchy node.
Combining Several Attribute Restrictions
Several restriction filters within an analytic privilege are combined in the following way:
Within one attribute column, several restrictions are combined with a logical OR.
Within one analytic privilege, all dimension restrictions are combined with a logical AND.
For example, if an analytic privilege includes two restrictions on two different attributes (YEAR=2017, COUNTRY=US), the user is allowed to see only data fulfilling the compound condition YEAR=2017 AND COUNTRY=US.
Several Analytic Privileges Applying to the Same View
If two analytic privileges (or more) are defined to apply to the same view, SAP HANA combines the corresponding conditions with a logical OR.
Caution
If the two restrictions from the previous example were defined in two different analytic privileges applying to this user and this view, the user would see more data. Namely all the rows for which YEAR=2017 OR COUNTRY=US (that is, any year for COUNTRY=US and any country/region for YEAR=2017).
Defining a Validity Period
You can decide to make an analytic privilege of the type Attribute valid for only a certain period of time. This restriction applies to the date when users are querying the secured view (CURRENT_DATE).
Caution
This isn't related to the time attributes (year, month, date, and so on) defined in your views. To limit the access to the data based on such attributes, you use a classic attribute-based restriction, for example on a DATE or YEAR column.
Restricting Value with a SQL Expression
This second type of restriction allows you to define a filtering expression that can't be obtained with restrictions of type Attribute. For example, when the precedence of OR and AND logical operators doesn't correspond to what you want to define.
Suppose you want to allow a user to view all the data from the country/region he’s responsible for (US), but also all the historical data for any country/region. You could create a restriction of type SQL Expression as follows:
1("COUNTRY" = 'US' OR "YEAR" <= '2016')Hint
Instead of writing your SQL expression from a blank page, you can start defining a restriction of the type Attribute, and then convert the restriction type to SQL expression. The corresponding SQL code will already be generated for you, and you'll just need to adjust it to your requirements.Defining a Validity Period
Restrictions of the type SQL Expression also support a validity period, by adding to the SQL expression the relevant SQL code to filter the CURRENT_DATE.
For example:
12(CURRENT_DATE BETWEEN '2017-07-28 00:00:00.000' AND '2017-07-28 23:59:59.999')
AND < SQL expression to define the restriction >Switching Restriction Type
Note
A restriction of type Attribute can be automatically converted into a SQL Expression restriction, but the other way round is NOT possible.
To define a restriction of type Attribute, you can select among the list of shared and private attributes from the secured calculation views. You can define as many restrictions as needed to define the correct data set.
Creating a Dynamic Restriction Type
In an analytic privilege, in addition to static values filtering conditions, it's possible to determine the filtering conditions in a dynamic way, which means that the filtering condition isn't defined once for all, but is evaluated dynamically when the view is executed. This is called Dynamic Analytic Privilege.
With a dynamic restriction, the filtering conditions that apply for a specific user are determined at runtime. This allows a more scalable approach, where the same analytic privilege can be granted to several users who have different authorization requirements.
For example, the COUNTRY attribute in one or several calculation views can be filtered dynamically based on the actual list of countries/regions that each is allowed to access, depending on their position in the geographical organizational structure.
Technically, to implement a dynamic restriction, you assign to the analytic privilege one procedure, which returns a SQLScript expression to filter data, like in the WHERE clause of a SQL statement.
For example, a procedure could return ("COUNTRY"='US') for User1 and ("COUNTRY"='UK' OR "COUNTRY"='FR') for User2.
This procedure must have the following properties:
Dynamic Restriction – Procedure Properties
Procedure must be read-only
Security mode must be DEFINER
No input parameters
Only one scalar output parameter of type VARCHAR(256) or NVARCHAR(256)
4. Set the Secured Calculation Views to Check SQL Analytic Privileges
To actually secure a calculation view with an analytic privilege, you must set the Apply Privileges property of the calculation view to SQL Analytic Privileges.
5. Deploy the Analytic Privilege
During the deployment of calculation views and analytic privileges, a specific dependency check is triggered to avoid errors that would lead to unsecured calculation views. You get a deployment error in the following cases:
If you try to deploy a calculation view after activating the check for SQL analytic privileges but NO analytic privilege has this view in its Secured Models list.
If you try to deploy an analytic privilege but some of the (runtime) calculation views it secures do NOT have the property Apply Privileges set to SQL Analytic Privileges.
If you try to deploy a calculation view after deactivating the check for SQL analytic privileges but there's still one or several (runtime) analytic privileges that have this view in their Secured Models list.
Note
The best approach is to build in parallel analytic privileges and the calculation views that they secure.6. Assign the Analytic Privilege to a Role
Once an analytic privilege is deployed, the calculation views it applies to can't be viewed until the privilege is granted to the end user.
To do so, in your project, you create a design-time role and grant the new analytic privilege to this role.
7. Assign the Role to a User
The last step is to grant the role to the end user.
