With the Internet of Things (IoT) slowly turning into reality, there is explosion of data from varied sources. Organizations are striving to harness the abundance for information lying in this data. However, this also poses a challenge to prevent an unauthorized access to this information. Ensuring that only authorized users can access the information is imperative.
Typically, in any business intelligence (BI) system, data is extracted from varied data sources and is stored in a central repository usually known as a data warehouse. Business users access the data stored in the data warehouse through the BI reports built on it. Figure 1 shows the high-level architecture of a typical BI system.
High-level architecture of a typical business intelligence system
There are two types of information security in BI reporting:
- Row-level security – This enables filtering of rows of data from a query output based on user authorization
- Column-level security – This enables the hiding or masking of fields or columns from query output based on user authorizations
Information security can be implemented in Big Data technologies in a variety of ways. SAP HANA exposes stored information in the form of information models also known as calculation views. Row-level security is offered by SAP HANA by applying analytic authorization on calculation views. Analytic authorizations define what data can be seen by a user or user groups. This feature implements row-level security ensuring the unauthorized rows of data are filtered automatically. However, SAP HANA does not offer column-level security as a standard out-of-the-box feature. The process described in the next section enables you to mask the information in a column of the output of calculation view based on the user’s authorization, thus implementing column-level security.
(Note: HR reporting is one of the areas in which the need to hide sensitive information is quite common—for example, hiding the salaries of employees from unauthorized access. The approach described in this article is useful for masking the information in a column based on user authorization.)
Steps for Implementing Column-Level Security
I now walk you through a step-by-step guide for implementing column-level security in SAP HANA. For this specific example, sales information is stored in a table in SAP HANA. Access to certain columns in the sales table for business users is managed through column-level security.
In the following example, sales data having the fields shown in Figure 2 is stored in SAP HANA in a table named SALES_DATA in the ADMINISTRATOR schema (Figure 2).
Data from a SALES_DATA table
The business user, USER_1, should have access to only the Sales_revenue column and not to Quantity_sold, whereas another business user, USER_2, should have access to only Quantity_sold and not to Sales_revenue. This typical scenario of column-level security is implemented using calculation views, stored procedures, and analytic authorizations using the SAP HANA studio client tool. This tool is used for modelling in SAP HANA.
Environment details are as follows:
- SAP HANA Support Package 12
- SAP HANA studio 2.2
1. Start SAP HANA studio by double-clicking it from the Windows start menu under SAP HANA as shown in Figure 3.
Start SAP HANA studio
2. This action opens the SAP HANA studio client tool, which lists all the SAP HANA systems that have been added to it as shown in Figure 4.
SAP HANA studio listing SAP HANA systems
(Note: SAP HANA studio has different layouts known as perspectives. For the example described in this article, the SAP HANA Development perspective is used. You can select the SAP HANA Development perspective by following SAP HANA studio menu path Window > Perspective > Other.)
3. Select the system for which you want to implement column-level security. Right-click it and select Log On as shown in Figure 5.
Log on to the SAP HANA system
4. Select the same system and click the SQL button on the top to open the SQL console for the system as shown in Figure 6. Figure 7 shows the SQL console.
Open the SQL console
5. In the SQL console, type the SQL script to create a table to store user access information as shown in Figure 7. Execute the script by clicking the execute icon that is highlighted in Figure 7.
Create a table to store user access information
6. This action creates the SALES_USER_SECURITY table in the ADMINISTRATOR schema with the data as shown in Figure 8.
Data from the SALES_USER_SECURITY table
7. In SAP HANA, all the modelling contents (i.e., calculation views, stored procedures, and analytic authorizations) are stored in packages. Packages serve as containers for all SAP HANA modelling artifacts. These packages are created under the Contents folder in an SAP HANA system. Expand the Contents folder, right-click it, and select the New > Package option as shown in Figure 9 to go to Figure 10.
Create a new package
8. Enter the name of the package and click the OK button as shown in Figure 10 to finish the process of creating a new package.
New package details screen
9. The newly created package can be seen in the Content folder under the SAP HANA system as shown in Figure 11.
A newly created package under the Content folder
10. Once the package is ready, a calculation view should be created to expose the data from the user access table (i.e., SALES_USER_ACCESS) that was created earlier. To create the calculation view under the newly created package, right-click the package and select the New > Calculation View option as shown in Figure 12 to go to Figure 13.
Create a calculation view in a package
11. Enter the name of the calculation view. Select the data category of the view as DIMENSION and click the Finish button as shown in Figure 13 to go to Figure 14.
Enter the details of the calculation view
(Note: As the SALES_USER_ACCESS table does not contain any key figure or measure field, the type of the calculation view should be DIMENSION, indicating that it would not have any key figures in it.)
12. Clicking the Finish button in Figure 13 opens the screen shown in Figure 14 with the Projection and Semantic nodes in the calculation view. To add the table to the calculation view, right-click the Projection node and select the Add Objects option to go to Figure 15.
Add the objects to the Projection node of the calculation view
13. In Figure 15, enter the name of the table to be added to the projection node in the Type the object name to search for field. The system automatically displays all the matching results in the pane below. Select the desired table and click the OK button to go to Figure 16.
Search for the table to be added to the projection node
14. Figure 16 shows the table in the Projection node with all the fields in the Details section.
View after adding the SALES_USER_ACCESS table to the Projection node
15. To add the fields to the output of the calculation view, right-click the “ADMINISTRATOR”.SALES_USER_SECURITY table in the Details section. Select the Add All To Output option, as shown in Figure 17, to go to Figure 18.
Add the fields to the output of the calculation view
16. Figure 18 shows all the fields in the table highlighted in bold indicating that they have been added to the output of the calculation view.
The fields are added to the output of the calculation view
17. A SQL Analytic Privilege check should be enabled for this view so that the rows for the logged-in user are filtered based on the SQL Analytic Privilege that is defined later in the process. To enable the SQL Analytic Privilege check, select the Semantics node and go to View Properties tab. Select the option SQL Analytic Privileges for the Apply Privileges menu. Click the save and activate icon as shown in Figure 19.
Enable the SQL Analytic Privileges check for the calculation view
(Note: CV_COLUMN_SECURITY has been enabled to check the SQL Analytic Privileges. This privilege is defined later in the process.)
This completes the process of creating a calculation view to expose data from the SALES_USER_ACCESS table. The following steps describe the process to create a calculation view to expose data from the SALES_DATA table along with column-level security, with the help of the CV_COLUMN_SECURITY view.
18. To create the calculation view to expose data from the SALES_DATA table, right-click the package and select the New > Calculation View… option (Figure 20) to go to Figure 21.
Create a new calculation view for the SALES_DATA table
19. Enter the name of the calculation view. The default value for the Data Category of the view is CUBE. Retain it and click the Finish button (Figure 21) to go to Figure 22.
Enter the details for the calculation view
20. Clicking the Finish button opens the screen shown in Figure 22. Note that the nodes available are Semantics and Aggregation.
The new calculation view screen
21. To add the SALES_DATA table and CV_COLUMN_SECURITY view, drag and drop the Projection nodes from the list of available nodes on the left as shown in Figure 23. Projection nodes help in exposing the required fields from the table and skipping the unwanted ones.
Add the Projection nodes to the calculation view
22. As a best practice, the projection nodes should be renamed to indicate what type of information they are exposing from the included tables or calculation views. In the example, the names are SALES_DATA and CV_COLUMN_SECURITY VIEW. To rename the projection node, right-click the projection node and select the Rename option. This action opens a pop-up window. Enter the name of the projection node and click the OK button as shown in Figure 24.
Rename the projection node
23. Follow the same process described in step 22 to rename the other projection node. After you rename both projection nodes, they should look like the ones shown in Figure 25.
Renamed projection nodes
24. The next step is to add the relevant tables or calculation views to these projection nodes as per their names. To add the SALES_DATA table to the SALES projection node, right-click the SALES projection node and select the Add Objects option (Figure 26) to go to Figure 27.
Add new objects to the SALES projection node
25. In Figure 27, enter the name of the object to be added to the projection node in the Type the object name to search for field. The system automatically populates the closest matches to the entered string. Select the appropriate object from the Matching items: list. Click the OK button to close the dialogue and go to Figure 28.
Search for the object to be added to the projection node
26. In Figure 28 all the fields from the SALES_DATA table are added in the Details section of the screen. The fields need to be added to the output of the projection node explicitly.
To add the fields to the output of the projection node, select the first field (i.e., Category in the example). Press the Shift key and hold it. Then select the last field (i.e., Sales_revenue in the example). Right-click it and select the Add To Output option as shown in Figure 28 to see the screen shown in Figure 29.
Add the fields from SALES_DATA to the output of the calculation view
27. Figure 29 shows that all the fields from the SALES_DATA table in the Details section of the screen have been highlighted in bold, indicating that they have been added to the output of the projection node.
The fields that have been added to the output of the projection node
28. Follow the instructions described in steps 24 to 27 to add the CV_COLUMN_SECURITY view to the SECURITY projection node and add the fields from it to the output.
29. After you add SALES_DATA and CV_COLUMN_SECURITY to the SALES and SECURITY projection nodes, respectively, the next step is to add the Join node from the list of available nodes on the left side. To add the Join node to the calculation view, select the Join node from the list of nodes on the left and drag and drop it on top of the projection nodes as shown in Figure 30.
Add the Join node to the calculation view
30. Connect the SALES and SECURITY projection nodes to the Join node by dragging and dropping as shown in Figure 31.
Connect the SALES and SECURITY projection nodes to the Join node
31. To define the join the between SALES and SECURITY nodes, a common field is required. It is not readily available in the SALES_DATA table and the CV_COLUMN_SECURITY view. Therefore, a calculated field needs to be created in both projection nodes (i.e., SALES and SECURITY).
To create a calculated field in the SALES projection node, select the SALES projection node. In the Output view on the right side of the screen, right-click the Calculated Columns folder and select the New… option as shown in Figure 32 to go to Figure 33.
Create a calculated column in the SALES projection node
32. In Figure 33, enter the name of calculated column as DUMMY_SALES. Select the data type as Integer. Enter the expression 1 in the Expression Editor and click the OK button. This column is used to join the data from SALES and SECURITY projection nodes so that user access information is tagged with each sales record in the output of the Join node.
Enter details of the calculated column for the SALES projection node
33. Follow the instructions described in steps 31 and 32 to create a calculated column called DUMMY_SECURITY for the SECURITY projection node.
34. As both the projection nodes now have a column with the same value as 1, they can be joined to each other using those columns in the Join node. To join the projection nodes in the Join node, select the Join node and connect SALES and SECURITY with each other using the DUMMY_SALES and DUMMY_SECURITY columns by dragging and dropping as shown in Figure 34.
Join the SALES and SECURITY in the Join node
35. Add all the fields except DUMMY_SALES and DUMMY_SECURITY from the SALES and SECURITY nodes, respectively, to the output of the Join node. To add the fields from SALES to the output of the Join node, select the first field (i.e., Category). Press the Shift key and hold it. Right-click the last field Sales_revenue, and then select the Add To Output option as shown in Figure 35.
Add fields to the output of the Join node
36. Similarly add all the fields from the SECURITY node except DUMMY_SECURITY to the output of the Join node.
37. Now the new calculated columns SALES_REVENUE and SALES_QTY should be added to the Join node. These calculated columns check the respective flag columns from the SECURITY node and accordingly populate the value. To insert the new calculated column in the Join node, select the Join node, right-click the Calculated Column folder on the right, and select the New option as shown in Figure 36 to go to Figure 37.
Add calculated columns to the Join node
38. Enter the name of the calculated column. Select DECIMAL as the data type with a length equal to 9 and a scale equal to 2. Enter the following expression in the Expression Editor and click the OK button as shown in Figure 37: IF(“FLAG_REVENUE” = ‘Y’,”Sales_revenue”,NULL).
Enter the details of the SALES_REVENUE calculated column
(Note: Expressions in SAP HANA are case sensitive, so field names should be spelled in the same case as they appear in the list of Elements in Figure 37.)
39. Add another calculated column SALES_QTY by following the instructions described in step 37. Figure 38 shows the data type and expression for the calculated column. Click the OK button to complete creating the calculated column.
Add the SALES_QTY calculated column
40. Connect the Join node to the Aggregation node on top by dragging and dropping as shown in Figure 39.
Connect the Join node to the Aggregation node
41. The Aggregation node shows the fields from SALES and SECURITY: both of the nodes that have been added to the output. From the available fields in the Aggregation node, select the fields that are required in the output of the aggregation node.
To add the fields to the output of the Aggregation node, select the top field (i.e., Category). Press the Shift key and hold it. Right-click the last field (i.e., Year) and select the Add To Output option as shown in Figure 40.
Add fields to the output of the Aggregation node
42. The calculated columns added in steps 38 and 39 should be exposed as aggregated columns in the Aggregation node. This step ensures that the appropriate aggregate function (e.g., SUM) is applied to it when the view is executed. To add the fields as aggregated columns, select the two fields, right-click them, and select the Add As Aggregated Column option as shown in Figure 41.
Add the aggregated columns
43. The calculation view is almost ready to be activated. Before it is activated, the fields in the output of the calculation view need to be designated as characteristics or key figures. The aggregated columns are automatically identified as key figures.
To designate the columns as characteristics or key figures, click the Semantics node, go to the Columns tab, and select the Auto Assign option as shown in Figure 42.
Assign a type to the output fields of the calculation view
44. The Auto Assign option automatically identifies key figures and characteristics and assigns the appropriate type to the fields in the output of the calculation view as shown in Figure 43.
Assigned type to the output fields in the calculation view
45. Click the View Properties tab of the calculation view and ensure that the Apply Privileges option is left blank for the view as shown in Figure 44. Click the save and activate icon highlighted in Figure 44 to activate the view.
Assign Apply Privileges and activate the view
This completes the process for creating calculation view to expose data from the SALES_DATA table. As mentioned in step 17, now the SQL Analytic Privilege needs to be defined to dynamically filter the rows from the output of the CV_COLUMN_SECURITY view. For this requirement, a dynamic SQL Analytic Privilege needs to be implemented. In this type of analytic privilege, a stored procedure is used to provide a dynamic filter clause to filter the rows from the output of the calculation view based on the user who is running a query on the view. Therefore, a stored procedure needs to be defined first, which would then be used in the SQL Analytic Privilege.
46. To create a stored procedure, click the repositories tab, expand the repository and package as shown in Figure 45.
Expand the repository
47. As shown in Figure 46, right-click the package, select the New option, and click Other… to go to Figure 47.
Create a stored procedure
48. In Figure 47, enter the text Stored Procedure in the Wizards text box. The system automatically searches for the closest match and shows the Stored Procedure object in the search result pane. Select the object Stored Procedure from the search results and click the Next button to go to Figure 48.
Search for the Stored Procedure object
49. The next screen (Figure 48) lists the available repositories and packages. Select the package in which the stored procedure is to be created. Enter the name of the stored procedure. For my example, it is SP_COLUMN_SECURITY. Select the appropriate Target schema. In this case, Target schema is optional as the stored procedure is read only. For the example, ADMINISTRATOR is set as Target schema. Click the Finish button to go to Figure 49.
Enter details of the stored procedure
50. Figure 49 shows the stored procedure editor console. Enter the OUT parameter for the stored procedure. It holds the value returned by the stored procedure. Ensure the SQL SECURITY parameter of the stored procedure is set to DEFINER. Enter the code in the body of the stored procedure as shown in Figure 49. Click the save and activate icon on the top to activate the stored procedure.
Stored procedure code
This action creates a stored procedure SP_COLUMN_SECURITY under package PKG_DEMO_COL_SECURITY.
(Note: The stored procedure code uses a SESSION_USER standard variable in SAP HANA. This variable automatically captures the ID of the user who is trying to execute the stored procedure.)
51. The next step is to create a SQL Analytic Privilege that would make use of the stored procedure created in step 50. To create a SQL Analytic Privilege, move back to the Systems tab and right-click the package. Select the New option and then the Analytic Privilege… option as shown in Figure 50 to go to Figure 51.
Create a SQL Analytic Privilege
52. In Figure 51, enter the name for the SQL Analytic Privilege. For my example, it is AP_COLUMN_SECURITY. Click the Next button to go to Figure 52.
Enter the details of the SQL Analytic Privilege
53. In Figure 52, navigate in the left side pane to select the calculation view to which the SQL Analytic Privilege should be applied. For my example, it is CV_COLUMN_SECURITY. Click the Add button in the center to move it to the selected pane on the right as shown. Click the Finish button to go to Figure 53.
Add the calculation view to the SQL Analytic Privilege
54. Figure 53 shows the SQL Analytic Privilege editor screen. To create the dynamic SQL Analytic Privilege, select the Dynamic option on the top. Click the explore icon against the Name property highlighted in Figure 53 to open the screen shown in Figure 54.
The SQL Analytic Privilege editor screen
55. In Figure 54, enter the name of the stored procedure in the Type the object name to search for field. The system then automatically populates the closest matches in the search result area. Select the appropriate stored procedure. For example, it is SP_COLUMN_SECURITY. Click the OK button to go to Figure 55.
Search for the stored procedure to add to SQL Analytic Privilege
56. Clicking OK in Figure 54 shows the screen shown in Figure 55 indicating that the stored procedure selection is complete. Click the save and activate icon on top right corner to activate the SQL Analytic privilege.
Complete the stored procedure selection
57. After you create the SQL Analytic Privilege, the next step is to assign this to a user who would be querying the sales data using the CV_SALES_INFORMATION_VIEW. To assign the SQL Analytic Privilege to a user, USER_1, expand Security folder and then the Users folder. Double-click user USER_1 as shown in Figure 56 to go to Figure 57.
Search for a business user
58. In Figure 57, go to the Analytic Privilege tab. Click the + icon. Search for the SQL Analytic Privilege by its name and add it to the user. Click the save and activate icon to save the changes.
Assign the analytic privilege to a user
This completes the process of implementing column-level security for sales information. Business user, USER_1, can now query the SAP HANA view to see SALES_QTY masked as per the set authorization.
59. Log in to SAP HANA system with USER_1 credentials and navigate to CV_SALES_INFORMATION_VIEW by expanding Content > PKG_DEMO_COL_SECURITY > Calculation views. Right-click the view and select the Data Preview option as shown in Figure 58.
Data preview of the calculation view
60. Figure 59 shows the output of the Data preview for the CV_SALES_INFORMATION_VIEW. It shows that the SALES_QTY field is masked as USER_1 is not authorized to access the same as shown in Figure 8.
Output of the calculation view
The approach described above helps in implementing column-level security in SAP HANA while reporting the data using SAP BusinessObjects tools (such as SAP Design Studio, SAP Lumira, SAP BusinessObjects Web Intelligence, and SAP Analysis Office) that support Single Sign-On (SSO) to the SAP HANA database.