10.5 Managing Dynamic Analytic Privileges
You can create either XML-based or SQL-based dynamic analytic privileges. The options are similar but have a few major differences. Note that SAP documentation indicates XML-based analytic privileges will be replaced in the future by SQL-based analytic privileges, so we recommend that all new developments be based on the SQL analytic privilege workflows. In this section, we’ll first provide a brief overview of XML-based analytic privileges. Then, because SQL-based analytic privileges are the recommended approach going forward, we’ll provide a more through discussion covering their end-to-end creation process.
10.5.1 Dynamic XML-Based Analytic Privileges
Dynamic XML-based analytic privileges are set up within the same interface used to define static XML-based analytic privileges (Section 10.4.1). However, to configure the analytic privileges as dynamic, you must select different options within the Assign Restrictions section of the management interface. Within this section, you must change the value of the Restriction Type column from Fixed to Catalog Procedure or to Repository Procedure. The Operator column should contain an operator that facilitates the results of the stored procedure selected in the Value column. In most cases, you’ll choose the IN operator because the procedure will return an array of values.
Before we discuss the configuration of the stored procedure, let’s review how XML-based dynamic privileges are structured. These analytic privileges are essentially configured to perform lookups in catalog tables to retrieve an array of filter values for a given user. The lookup is based on user name, which is derived from the SAP HANA user account querying a referenced information view. The table that stores the mapping data requires a minimum of two columns: The first column will contain the user name, and the second column will contain the filter value. Table 10.1 shows an example of how this table is structured and includes some example data.
|
USER_NAME |
FILTER_VALUE |
|---|---|
|
SHODGE01 |
North Central |
|
CBESSLER04 |
South West |
|
WTRAN05 |
North Central |
|
WTRAN05 |
South Central |
Table 10.1 Mapping to Facilitate XML-Based Dynamic Analytic Privileges
The stored procedure selected for use with the analytic privilege must be configured to return a single value or an array of values from this table. Therefore, the stored procedure’s output variable must be configured as a table type. Table types are catalog objects that act as a kind of virtual table and are most often used to temporarily define the tabular output of a stored procedure. For example, you can use the following SQL code to define the table type required to accommodate the output of this stored procedure:
CREATE TYPE "SECURITY"."CUSTOMER_ACCESS" AS TABLE ( "CUSTOMERKEY" int);
The procedure itself is defined to output one or more records based on the table containing the user-to-value mappings. The SAP HANA-specific SQLScript shown in Listing 10.1 will create a procedure that accommodates these requirements.
CREATE PROCEDURE "SECURITY"."SP_RLS_CUSTOMER" ( OUT VAL "SECURITY"."CUSTOMER_ACCESS" )
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
READS SQL DATA AS
BEGIN
VAL = SELECT FILTER_VALUE FROM "SECURITY"."RLS_CUSTOMER_ACCESS”
WHERE USER_NAME = SESSION_USER;
END;
Listing 10.1 Stored Procedure Used with XML-Based Dynamic Analytic Privileges
In the SQLScript code, note the line that begins VAL = SELECT FILTER_VALUE FROM. The SELECT statement in this line queries the table and uses the WHERE clause of USER_NAME = SESSION_USER to refine the values returned. The SESSION_USER variable is known to the SAP HANA system and is replaced at runtime with the user name assigned this analytic privilege.
For example, assume that the user account WTRAN05 is used to authenticate with SAP HANA when querying an information view. This user account is granted an XML-based dynamic analytic privilege. When WTRAN05 queries an information view referenced by the dynamic analytic privilege, the filter condition within the stored procedure is changed from USER_NAME = SESSION_USER to USER_NAME = WTRAN05. All values in the table associated with WTRAN05 are returned by the table type output variable. The array of values is then passed to the execution engine and used to filter the information view. Based on Table 10.1, the values North Central and South Central will be used as the filter condition.
You should now have a basic understanding of the structure of an XML-based dynamic analytic privilege. Remember that this type of analytic privilege will be replaced by SQL-based dynamic analytic privileges. In the next section, we’ll review the process for creating SQL-based dynamic analytic privileges in detail.
10.5.2 Dynamic SQL-Based Analytic Privileges
Dynamic SQL-based analytic privileges offer the ability to perform programmatic lookups of filter conditions based on a mapping between user names and filter conditions. XML-based dynamic analytic privileges require individual data values, but SQL-based dynamic analytic privileges require filter conditions, similar to a filter within a SQL WHERE clause. The use of filter conditions supports complex and diverse filters that are dynamically generated for a given user. For example, you can reference multiple attribute columns and filter operator types in the same condition clause. In contrast, each XML-based dynamic analytic privilege restriction is limited to a single attribute column and array of filter values.
To accommodate the lookups, a table must exist that maps each user account to one or more filter conditions. Table 10.2 shows a basic example of how this table and its data should be structured. The table can be more complex but, at a minimum, must adhere to this structure.
|
User Name |
Filter Condition |
|---|---|
|
JHARRIS01 |
("REGION" = ' North Central') |
|
TTIM03 |
("REGION" = ' South West') |
|
WTRAN05 |
("REGION" = ' North Central') |
|
TIMG05 |
("REGION" = ' South Central') |
Table 10.2 Example Mapping Used with SQL-Based Dynamic Analytic Privileges
In the table, the left column contains the user name. User name values should exactly match configured SAP HANA user account names. The column on the right provides the filter conditions, which will be retrieved and added to the execution of the information view. These conditions should be constructed in such a way that matches an attribute column to a data value. Again, this construction is similar to a filter within a SQL WHERE clause. The following syntax represents the construction of a basic single column filter condition:
( <"COLUMN"> operator <'VALUE'> )
Parentheses are recommended around the entire condition statement. Attribute columns should use double quotes. The supported operators are =, <=, <, >=, >, LIKE, BETWEEN, and IN. The value should use single quotes if a string value. Single quotes are not required for number type values. Note that subquery filter conditions aren’t supported in this mode.
Unlike XML-based analytic privileges, the stored procedure that returns these conditions must only support a single-valued string output variable. If multiple rows for a given user are stored in the table, the stored procedure code will need to condense these rows into a single value. Alternatively, the condition you store in the table can be quite complex, using a mixture of columns and operators and eliminating the need to store multiple values for a given user. For example, the following condition is also supported for a given user:
((REGION IN ('NE','NW','SW','NC') ) or (PLANT IN ('1001','1005','1006')))Again, to accommodate the lookup of the values from within the table, you’ll need to create a stored procedure. Ideally, you’d create a repository-based stored procedure, but catalog stored procedures are supported as well. Keep in mind that the _SYS_REPO system account will require access to the table and stored procedure. Therefore, creating these objects within the repository for which _SYS_REPO will be the owner with full access is best. The objects will also be transportable within a delivery unit when they’re created within the repository.
The structure of the stored procedure must comply with a few conditions. First, the stored procedure must be configured as a DEFINER-based procedure. In other words, the creator of the procedure will be used for validation when executed. Second, the procedure must be considered read-only and should only return values without executing any Data Manipulation Language (DML) statements. Third, the stored procedure can’t contain any input parameters. Fourth, the procedure can only contain a single-value output parameter with a data type of VARCHAR or NVARCHAR. Finally, the _SYS_REPO must have full access to the procedure and any tables it references.
To better understand the process, let’s walk through the three main steps required to implement a dynamic analytic privilege. First, you must create a table to host the user and data element mappings. Second, you must create a stored procedure to perform the lookups of the values. Finally, you’ll construct a SQL-based dynamic analytic privilege.
Creating a Repository-Based Security Table
To simplify the design, you’ll create a repository-based table. You can create this table using the SAP HANA Web-Based Development Workbench editor. Locate a secure package location to host the repository-based table. Right-click the desired package node and choose New • File. A window titled Create File will appear. In the File Name field, enter the name of the repository-based table. For example, enter “RLS_CUSTOMERKEY_ACCESS.hdbtable.” Click Create to define the table. Figure 10.9 shows an example of the script you can use to define this table.
Figure 10.9 Example Repository-Based Table Definition Script
The example table is configured with two columns. The first column contains the filter conditions; in our example, the CUSTOMER_FILTERS column was used, which contains the conditions for filtering the CUSTOMER_REGION in the data. The second column is USER_NAME and will be filtered based on the SESSION_USER variable.
With the table created, you must now populate the table with the mapping data. Multiple options exist for populating this table. You can import data from a file, insert records manually using SQL, or use supported tools to extract the data from other sources and load it into the security table. Figure 10.10 shows sample data within the table.
Listing 10.2 shows an example of how the data can be populated in the example table using SQL insert statements. Notice that you must use two consecutive single quotes around the CUSTOMER_REGION data values, not double quotes. In SAP HANA, a single quote is an escape character. When inserting static values into a table, you have to “escape” the inserted single quote with another single quote so that the table values only store a single quote, as shown in Figure 10.10.
INSERT INTO "security_tables"."Security.Tables::RLS_CUSTOMERKEY_ACCESS"
VALUES ('JHARRIS01', '("CUSTOMER_REGION" = ''North Central'')');
INSERT INTO "security_tables"."Security.Tables::RLS_CUSTOMERKEY_ACCESS"
VALUES ('TTIM03', '("CUSTOMER_REGION" = ''South West'')');
INSERT INTO "security_tables"."Security.Tables::RLS_CUSTOMERKEY_ACCESS"
VALUES ('WTRAN05', '("CUSTOMER_REGION" in (''South West'',''North Central''))');
INSERT INTO "security_tables"."Security.Tables::RLS_CUSTOMERKEY_ACCESS"
VALUES ('TIMG05', '("CUSTOMER_REGION" in (''South Central'',''North Central''))');
INSERT INTO "security_tables"."Security.Tables::RLS_CUSTOMERKEY_ACCESS"
VALUES ('TIMG05', '("CUSTOMER_REGION" = (''South Central''))');
INSERT INTO "security_tables"."Security.Tables::RLS_CUSTOMERKEY_ACCESS"
VALUES ('SYSTEM', '_ALL');
Listing 10.2 A Series of Six SQL INSERT Statements Used to Populate the Example Table
Figure 10.10 Example Filter Conditions in Security Table
Creating a Repository-Based Stored Procedure
To simplify the design, we’ll create a repository-based stored procedure. You can create this table using the SAP HANA Web-Based Development Workbench editor. Locate a secure package location to host the repository-based stored procedure. Right-click the desired package node and choose New • HDB Procedure. A window titled New HDB Procedure will appear. In that window, type the name of the stored procedure in the File Name field. In the Schema field, choose an existing schema where the procedure will reside. Click Create to continue.
A table titled with the name of your procedure will appear. Within the tab, you’ll see a preconfigured, text-based template of the stored procedure script. You can modify the script to suit your requirements. Figure 10.11 shows an example of the complete stored procedure code required to facilitate a dynamic SQL-based analytic privilege security value lookup.
Figure 10.11 Example Stored Procedure Used with SQL-Based Dynamic Analytic Privileges
This example is programmed to retrieve the filter conditions from the security mapping table. If no records are found for a given user, a filter condition of CUSTOMER_REGION = ’’ is returned. Because all customer regions are populated with values, this condition simply returns no data. If the value _ALL is found in the table, the condition (1=1) is returned, resulting in all records being returned. For users with a single condition or multiple conditions in the table, the procedure uses a cursor loop to concatenate multiple condition lines together, using an OR operator to divide them. Based on the example data, Figure 10.12 shows the output when the example user TIMG05 executes the stored procedure. The output is on line 1 under the Resutls1 tab.
Figure 10.12 Example Stored Procedure Output When Executed by User TIMG05
Notice that, in the results, the two different filter conditions are concatenated together using an OR operator. This design is an optional feature for this example. You could have simply stored this exact condition in the table and avoided the need to construct a stored procedure. However, this example illustrates the need to return only a single string for each user.
Also, within the example, notice that the cursor v_Cursor is based on a SQL SELECT statement that uses the SESSION_USER system variable in the WHERE clause to dynamically filter the security table based on the user name querying the information view. At runtime, SESSION_USER is replaced with the user accessing the information view. This step is critical to the functionality of the dynamic analytic privileges.
When you’ve completed programming the stored procedure, save and activate the stored procedure.
Defining a SQL-Based Dynamic Analytic Privilege
To create a SQL-based dynamic analytic privilege, you can use the SAP HANA Web-Based Development Workbench editor. Within the editor, locate a secure package location to host the analytic privileges. Within the repository, locate a package in a secure location. Right-click the package and choose New • Analytic Privilege…. A popup window will appear labeled New Analytic Privilege, as shown in Figure 10.13.
In this window, specify the technical name of the analytic privilege in the field labeled Name. The name can only contain the characters A–Z, a–z, and the underscore special character. In the Label field, specify a description for the analytic privilege. The Type dropdown list must have the SQL option selected because the information views are configured to use SQL analytic privileges. Click Create to continue.
Figure 10.13 New SQL-Based Analytic Privilege Window
A new tab should appear where you can configure the analytic privilege. Just above the Privilege Validity section, you’ll see three radio buttons, Attributes, SQL Editor, and Dynamic, as shown in Figure 10.14. Choose Dynamic to configure a stored procedure-based dynamic SQL-based analytic privilege.
As shown in Figure 10.14, the SQL-based dynamic analytic privileges management window is divided into three main sections: General, Dynamic, and Secured Models.
Figure 10.14 SQL-Based Dynamic Analytic Privileges Management Window
The first section, General, located in the top left corner, contains fields for the Name, Label, and Type of analytic privileges. The Label field can be updated at any time; Name and Type filed are read-only at this point in the process.
The Secured Models section, located in the bottom left corner, provides an interface to add or remove additional referenced information views. To add information views, click the Add… button and search for the desired information views. To remove an information view, highlight it in the list and click the Remove button.
The large section on the top right titled Dynamic is where you’ll configure the procedure that will drive the dynamic filtering of conditions. The dropdown list next to the Procedure field contains two options: Repository Procedure and Catalog Procedure. Select Repository Procedure to leverage a design-time stored procedure. Select the option Catalog Procedure to use a runtime procedure. Click the stacked squares icon in the right corner of the empty field below the procedure type to search for a stored procedure by name. The assumption at this point is that the stored procedure will return a string value containing an information view column, operator, and filter condition. The information view column in the string must match a column name in the output of the referenced information view in the Secured Models section.
Once the analytic privileges are activated, their restrictions won’t apply until the analytic privileges are granted to an existing role or user. If no competing analytic privileges are also applied to the grantee, queries to the referenced information views will be limited based on the defined filter conditions or restrictions.
SQL-based dynamic analytic privileges can accommodate a variety of complex filter conditions and filter condition operators and thus offer more flexibility than the legacy XML-based options. In the next section, we’ll review options that enable you to construct even more complex dynamic filter conditions through the use of SQL expressions, including the use of subqueries.