Performing Practical Exercises

You will first import a Calculation View and check that you can display its data.

1. In SAP Business Application Studio, import the CVCS_PO2.hdbcalculationview file from your course files folder HC300 into your HC300_A#### project, in the view tree folder src→models→exercises.

   1. Expand your HC300_A#### project to open the db→src→models folder.

   2. In the Explorer view, right-click your exercises folder and choose Upload.

   3. In the dialog box, select the HC300→Calculation Views Templates→CVCS_PO2.hdbcalculationview file and choose Open.

      Result

      The new Calculation View CVCS_PO2 is created in your project.

2. Deploy the CVCS_PO2 view.

   1. In the SAP Hana Projects sub-pane, expand the foldersrc→models→exercises.

   2. Hover your mouse on the new (imported) view and choose Deploy.

   3. In the terminal, check that the build was successful.

3. Preview the calculation view data.

   It should retrieve data for several countries.

   1. In the Explorer view tree, right-click the new (imported) view and choose Data Preview.

   2. Check that the view retrieves data.

From now on, and during the remainder of the exercise, you will preview the data with your A#### user, using the Data Preview with Other Database User, so that you can correctly test the behavior of the view while you’re defining data access security for this view.

1. If needed, open the calculation view CVCS_PO2.

   1. In the Explorer view, double-click the view CVCS_PO2.hdbcalculationview.

2. Execute the data preview from the calculation view editor, with your A#### user.

   You actually choose a connection (HC300_DB) and the associated user (A####) or (SSO enabled) from the connections list of Database Explorer.

   1. Right-click the Star Join node and choose Data Preview with Other Database User.

   2. In the Select Database With User, choose the connection HC300_DB (A####) (SSO enabled).

      Result

      The calculation view opens in data preview mode in a new tab.

   3. In the Analysis tab, assign the column COUNTRY_NAME to the Label axis and the column GROSS_AMOUNT to the Value axis.

3. Leave the data preview ta open. This will allow you to refresh the data preview while setting up your analytic privileges.

You want to limit the visibility of data for this calculation view to the purchase orders from customers located in the US.

1. Create a new analytic privilege in the src/models/exercises folder, with the following properties:

   Analytic Privilege Properties

   Field	Value
   Name	AP_COUNTRY_US
   Label	US Partners

   1. Right-click your exercises folder and choose New File.

   2. Enter the name AP_COUNTRY_US.hdbanalyticprivilege.

      Result

      The analytic privilege opens in a new tab.

   3. To rename the description, top-right of the analytic privilege editor, choose Rename.

   4. Enter the Label as per the table and choose OK.

2. Reference the view CVCS_PO2 so that it is secured by this analytic privilege.

   1. In the Secured Models area, choose the +(add) icon.

   2. In the Find Data Sources field, enter PO2.

   3. Select the HC::CVCS_PO2 view and choose Finish.

3. Define the analytic privilege restriction.

   Add a restriction to limit the data for business partners located in the US.

   1. In the Associated Attribute Restrictions area, choose +(add).

   2. In the Attribute window, choose the COUNTRY attribute from the shared dimension view HC::CVD_BP2 and choose OK.

   3. In the Restriction Type column, click the + icon.

   4. In the Operator field, choose =.

   5. In the Value, select US and choose OK.

4. Save and deploy the new analytic privilege.

   Check the build status.

   1. Choose Save if needed.

   2. In the SAP HANA Projects window, click the AP_COUNTRY_US analytic privilege and choose Deploy.

      Result

      The build fails.

You must modify the calculation view secured by the AP_COUNTRY_US privilege before you include this view in the list of models this analytic privilege secures.

1. Modify the calculation view CVCS_PO2 so that it triggers an analytic privilege check when it is queried.

   Hint

   This is done in the View Properties tab of Semantics.

   1. If needed, open the exercises→CVCS_PO2 calculation view.

   2. In Semantics, display the View Properties tab.

   3. In the Apply Privileges drop-down list, choose SQL Analytic Privileges.

2. Save and deploy the CVCS_PO2 calculation view.

   Check the build status.

   1. Choose Save if needed.

   2. In the SAP HANA Projects window, click the CVCS_PO2 calculation view and choose Deploy.

3. Deploy again the AP_COUNTRY_US analytic privilege.

   1. In the SAP HANA Projects window, click the AP_COUNTRY_US analytic privilege and choose Deploy..

   2. Check the build status.

      Result

      This time, the build is successful. As a conclusion, all the views that an analytic privilege secures must have the analytic privilege check enabled, and they must be built (runtime object must exist) before you can actually build this analytic privilege.

You will now refresh the data preview tab to see if your analytic privilege definition has been applied.

1. Refresh the data preview and analyze the results.

   1. In the former data preview tab, to refresh the data, choose Execute.

3. Get ready for the next exercise where you create and assign a new role, including the new analytic privilege, to your user, so that he can access the authorized data of the calculation view CVCS_PO2.

To grant the new privilege to external users, you will create a design-time role DATA_VIEWER that references this analytic privilege.

1. In the db→src→exercises folder, create a new role DATA_VIEWER.hdbrole.

   This role will reference only privileges located in your HDI container, so you do not need a Role configuration file.

   Hint

   In this scenario,we recommend you use the command palette to create SAP HANA Database artifact.

   1. Choose View→Command Palette and enter artifact.

   2. Choose Create SAP HANA Database Artifact.

   3. In the field Specify where you want to create the new artifact, select your folder projects/HC300_A####/db/src/models/exercises.

   4. In the Choose the artifact type field, choose Role.

   5. In the Specify the artifact name field, enter DATA_VIEWER.

   6. Choose Create.

      Result

      The new role DATA_VIEWER is created and it opens in the Role editor window.

2. Add the Analytic Privilege HC::AP_COUNTRY_US to the role definition.

   1. Display the Analytic Privileges tab.

   2. In the empty line, choose the button to the right of the Privilege Name dropdown list

   3. In the Search field, enter AP_COUNTRY and choose your HC::AP_COUNTRY_US Analytic Privilege.

   4. Remove the name of your container schema from the corresponding Schema Reference column.

3. If the wizard created automatically a DATA_VIEWER.hdbroleconfig file, which is not needed in our scenario, delete this file from the workspace and remove the reference to it from the DATA_VIEWER.hdbrole artifact.

   1. In the role editor, display the Schema References tab.

   2. Next to the Role config file field, choose the X(Remove Config File) icon.

   3. In the Explorer view, right-click the unnecessary DATA_VIEWER.hdbroleconfig file and choose Delete Permanently. In the confirmation dialog box, choose Delete.

4. Save, and then deploy the DATA_VIEWER role.

   1. Choose Save if needed.

   2. In the SAP HANA Projects sub-pane, select the HC300_A####/db/src/models/exercises/DATA_VIEWER.hdbrole object and, on the right, click the Deploy icon.

   3. In the Console, check that the build was successful.

Now you are ready to grant the runtime role DATA_VIEWER, containing your analytic privilege, to your A#### user.

1. In the SAP HANA Database Explorer view, open an SQL console connected to the HC300_DB (A####) (SSO enabled) (not your container).

   Caution

   Make sure you do NOT open the Console for your container. If you are connected to your container, the SQL query will NOT be authorized.

   1. In the Activity bar, choose SAP HANA Database Explorer.

   2. In the Database list view, select your HC300_DB (A####) (SSO enabled) entry and, on the right, choose Open SAP HANA SQL Console.

2. In the SQL console, import the following file containing prepared SQL statements:

   HC300→Prepared Code→SQL to Assign Data Viewer Role.sql. Then replace A#### with your assigned student number.

   1. On your File Explorer, open the file specified above.

   2. To select all the content, enter Ctrl+A, and to copy it to the clipboard, enter Ctrl+V.

   3. Back to the SQL Console tab in SAP Business Application Studio, to paste the SQL statements, enter Ctrl+V.

   4. To replace A#### with you student number everywhere in the SQL statements, press Ctrl+H and use the Search and Replace feature.

3. Execute the first statement to assign the DATA_VIEWER role to your user.

   Code Snippet

   Copy codeSwitch to dark mode

   1
   CALL TRAIN_ADMIN.P_HC300_MANAGE_ROLE('GRANT','HC300_A####_HDI_DB_1','HC::DATA_VIEWER', ?);

   1. Highlight the SQL statement in section #1 and choose Run (F8).

      Alternatively, you can put your cursor anywhere within the statement and press F9.

   2. Check that the grant statement was successful.

4. Leave the SQL console open, as you will use the other SQL statements from the imported file later on.

You can now test whether the data is available (and restricted) in the data preview of CVCS_PO2 with user A####.

1. Display or refresh the data preview of the calculation view CVCS_PO2.

   1. If needed, reopen the calculation view CVCS_PO2 and preview its data with user A####, as you did in a previous step.

   2. If the data preview was already open from previous steps, from the data preview tab, choose the Execute icon.

   3. Check the displayed data in the pivot table.

   Result

   You can now see some of the CVCS_PO2 data, restricted to the Country US.

2. Keep the SQL Console tab open and close all other tabs.

   1. Right-click the SQL Console tab and choose Close Others.

You will now have a look at another calculation view CVCS_PO3_AP which is secured by different analytic privileges, and observe how these analytic privileges impact the returned data set.

1. Open the following Analytic Privileges located in folder db→src→resources and check their definition:

   • AP_COUNTRY_DE
   • AP_COUNTRY_DE_PROD_NOTEBOOK
   • AP_PROD_NOTEBOOKS

   1. In the workspace, locate each file and double-click it.

   2. Check the Secured Models and Associated Attribute Restrictions panes.

2. Open the following roles located in the folder db→src→resources and check their definition:

   • DATA_VIEWER2
   • DATA_VIEWER3

   1. In the workspace, locate each file and double-click it.

   2. Check the assigned Analytic Privileges in the corresponding tab.

3. Back to the SQL console, execute the statements in section #2. They grant the DATA_VIEWER2 role to your user and query the calculation view CVCS_PO3_AP.

   Code Snippet

   Copy codeSwitch to dark mode

   12
   CALL TRAIN_ADMIN.P_HC300_MANAGE_ROLE('GRANT','HC300_A####_HDI_DB_1','HC::DATA_VIEWER2', ?);
   SELECT DISTINCT COUNTRY, CATEGORY FROM "HC300_A####_HDI_DB_1"."HC::CVCS_PO3_AP";

   Note

   The SELECT statement is designed to retrieve only the authorized country/product category pairs without showing the entire result set. Here, this is enough to check the behavior of the analytic privileges.

   1. Highlight the statements in section #4.3 and choose Run (F8).

4. Execute the statements in section #4.4. This will revoke the previous DATA_VIEWER2 role and grant the other one DATA_VIEWER3 to your user, and query the same calculation view again.

   Code Snippet

   Copy codeSwitch to dark mode

   123
   CALL TRAIN_ADMIN.P_HC300_MANAGE_ROLE('REVOKE','HC300_A####_HDI_DB_1','HC::DATA_VIEWER2', ?);
   CALL TRAIN_ADMIN.P_HC300_MANAGE_ROLE('GRANT','HC300_A####_HDI_DB_1','HC::DATA_VIEWER3', ?);
   SELECT DISTINCT COUNTRY, CATEGORY FROM "HC300_A####_HDI_DB_1"."HC::CVCS_PO3_AP";

   1. Highlight the statements in section #4.4 and choose Run (F8).

5. Close all open tabs in SAP Business Application Studio

   1. Right-click any open tab and choose Close All.

1. If needed, launch SAP Business Application Studio and start your HCMOD development space.

2. Import the calculation view CVD_EMPLOYEES.hdbcalculationview.

   It is located in the folder HC300→Calculation Views Templates.

   1. Right-click the exercises folder and choose Upload.

   2. Select the file HC300→Calculation Views Templates→CVD_EMPLOYEES.hdbcalculationview, and choose Open.

3. Deploy the imported calculation view.

   Check the deployment status.

   1. Choose the Deploy icon for your CVD_EMPLOYEES calculation view.

   2. In the Console pane, check that the deployment completes successfully.

4. Preview the data.

   1. Right-click the CVD_EMPLOYEES view and choose Data Preview.

   2. Select the Raw Data tab.

You will now modify the design of the calculation view so that the bank account number is partially masked. You would like to replace every figure with an X, except the last 5 figures that can be displayed as is. For the sake of clarity, you will also rename the view to show that it uses data masking.

1. Rename the calculation view by adding _WITH_MASK to its name.

   Note

   Make sure you also rename the runtime object name by choosing Yes in the Confirmation dialog box.

   1. In the exercises folder, right-click the CVD_EMPLOYEES calculation view and choose Rename.

   2. Adjust the new name to CVD_EMPLOYEES_WITH_MASK.hdbcalculationview and press Enter.

   3. In the Confirmation dialog box, choose Yes.

2. Define the mask expression as follows:

   Code Snippet

   Copy codeSwitch to dark mode

   1
   'XXXXXXXXXX'||RIGHT("BankAccountNumber", 5)

   1. In the Explorer view, select the CVD_EMPLOYEES_WITH_MASK calculation view.

   2. Double-click the Semantics node.

   3. Click the Data Masking property of the column BankAccountNumber.

      Note

      Only the left part of the Data Masking property cell is sensitive. Make sure you click there.

   4. In the Data Masking Expression window, enter the SQL expression above.

   5. Choose OK.

   6. Make sure that the Data Masking property now shows up.

3. Deploy the calculation view.

   Check the deployment status.

   1. Choose the Deploy icon for your CVD_EMPLOYEES_WITH_MASK calculation view.

   2. In the Console pane, check that the deployment completes successfully.

4. Preview the data

   1. Right-click the CVD_EMPLOYEES_WITH_MASK view and choose Data Preview.

   2. Select the Raw Data tab.

You want to know how to grant access to the actual data ("unmasked") for some users. To test the overall process, you will use the classic database connection HC300_DB (A####) (SSO enabled), which is defined with your user A####. You will create a role for this, and then assign it to your A#### user via a dedicated procedure.

1. Switch to the SAP HANA Database Explorer perspective and open an SQL console connected to your HC300_DB (A####) (SSO enabled) connection.

   Caution

   Make sure you do NOT open a console connected to your HDI container. If you are connected to your HDI container, the SQL query will NOT be authorized.

   1. Select the SAP HANA Database Explorer tool.

   2. Select the connection HC300_DB (A####) (SSO enabled) and choose Open SAP HANA SQL Console.

2. In the SQL console, paste the code from the following file containing prepared SQL statements:

   HC300→Prepared Code→SQL for Data Masking.txt. Then replace A#### with your assigned user number.

   Code Snippet

   Copy codeSwitch to dark mode

   12345
   
   SELECT * FROM "HC300_A####_HDI_DB_1"."HC::CVD_EMPLOYEES_WITH_MASK";
   
   CALL TRAIN_ADMIN.P_HC300_MANAGE_ROLE('GRANT','HC300_A####_HDI_DB_1','HC::UNMASK_ALL', ?);
   

   1. Double-click the file HC300→Prepared Code→SQL for Data Masking.txt.

   2. Select all the content of the file (press Ctrl+A) and copy it to the clipboard (press Ctrl+C).

   3. To paste the content in the SQL Console, press Ctrl+V or right-click anywhere inside the SQL Console and choose Paste.

   4. To replace A#### with you group number in the SQL statements, press Ctrl+H.

3. Execute query #3.3 to display the calculation view data.

   Code Snippet

   Copy codeSwitch to dark mode

   1
   SELECT * FROM "HC300_A####_HDI_DB_1"."HC::CVD_EMPLOYEES_WITH_MASK";

   Note that, compared with the previous data preview which was executed by the technical user of the container, we are now executing a query with user A####.

   1. Highlight the specified statement and choose Run (F8).

      Alternatively, you can place the cursor anywhere within the statement and choose Run Statement (F9).

   2. Review the output in the Result tab.

4. Back to the EXPLORER tool, in your exercises folder, upload the role design-time file UNMASK_ALL.hdbrole available in your course files folder HC300.

   1. To switch perspective, choose the EXPLORER tool.

   2. Right-click the exercises folder and choose Upload.

   3. Select the file HC300→UNMASK_ALL.hdbrole and choose Open.

5. Review the role definition.

   1. Review the different tabs in the role definition to find which privileges are granted to (that is, included in) the role.

6. Deploy the HC::UNMASK_ALL role.

   Check the deployment status.

   1. Choose the Deploy icon for your UNMASK_ALL role.

   2. In the Console pane, check that the deployment completes successfully.

7. Grant the HC::UNMASK_ALL role to your TRAIN_A#### user.

   This can be done by executing query #3.7 inside your SQL console.

   Code Snippet

   Copy codeSwitch to dark mode

   1
   CALL TRAIN_ADMIN.P_HC300_MANAGE_ROLE('GRANT','HC300_A####_HDI_DB_1','HC::UNMASK_ALL', ?);

   1. Select the tab SQL Console ...

   2. Make sure A#### has been replaced with your assigned user number.

   3. Highlight the specified statement and choose Run (F8).

   4. Review the output in the Message tab.

8. Execute again query #3.3 to display the calculation view data.

   Code Snippet

   Copy codeSwitch to dark mode

   1
   SELECT * FROM "HC300_A####_HDI_DB_1"."HC::CVD_EMPLOYEES_WITH_MASK";

   1. Highlight the specified statement and choose Run (F8).

   2. Review the output in the Result tab.

9. Close all the open tabs in Business Application Studio.

   1. Right-click any open tab and choose Close All.