Grant Access to Calculation Views

To allow others to see the results of your calculation view, you need to grant them the privilege to run SELECT statements on this calculation view.

To run SELECT statements on calculation views in the new environment, first make sure that the you have the correct schema name. This schema is not the SFLIGHT schema we previously created, but rather the schema automatically created for the calculation view.

1. To find out the schema name, open the SAP HANA database explorer.

2. Find your HDI container in the catalog.

> Unless you specified a name for your container manually upon creation of your project, the default name will look something like this:
>
> `SharedDevKey@[name of your dev project]-hdidb-[sequence of characters] ([name of your space])`
>
> In our example, it is listed as follows:
>
> `SharedDevKey@BestRunTravel-hdidb-ws-g6mq2 (dev)`
>
> Alternatively, you can go to SAP Business Application Studio and click on the HDI container icon ![Container](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-mission-trial-10/icon-container.png), which will directly open the right container in the catalog.

3. Click on Column Views.

4. Right-click on the column view name on the bottom panel and choose to Generate a SELECT Statement.

   

5. This will open the SQL Console on the main area of the screen with the SELECT statement. On line 7 you can see a FROM clause with two arguments separated by a .. The first part is the schema name, the second part is the calculation view name.

6. Copy the name of the schema and save it for later, for example, using a text editor.

7. Keep this SQL console open.

   

Great, now that you have the right schema name, next you will have to grant the authorization to SELECT on the Calculation View. This is done by creating an .hdbrole file in your development project that grants the SELECT privilege.

1. Go to your project in SAP Business Application Studio and start your development space if needed.

2. You need the command SAP HANA: Create HANA database artifact that you got to know in the previous tutorial. Access it by clicking on View menu and selecting Command Palette or press Ctrl+Shift+P. Unless you see it in your recently used commands, type SAP HANA and select SAP HANA: Create HANA database artifact from the list.

3. In the wizard, make sure the path to save the role file is in the src folder of your project.

4. Choose the database version HANA Cloud.

5. Select the artifact type as Role (hdbrole).

6. Name your role PublicAccessSchema.

7. Finally, click on Create.

   

Your role will appear in the file explorer of your project and you can add privileges to it.

In this step, you have two options to add privileges to your role: You can use the Role Editor wizard or the Text Editor. Click on the option you prefer under the title of this step.

The goal in this step is to add the schema privileges SELECT and EXECUTE to the role PublicAccessSchema

Deploy the .hdbrole file by clicking on the deploy icon (

After you are done, deploy the whole project again. When that is completed successfully, you may continue.

Now that you have the role created and granted privileges to this role, it’s time to grant this role to a user. We will create the public user report that shall have read-only access to the calculation view.

1. Open the SQL Console in SAP HANA Cloud Central. Make sure that the connection is opened for an user that has system privileges ROLE ADMIN and USER ADMIN, e.g., database user DBADMIN.

2. Paste the following statement in the SQL Console. Change the password in the statement and then run.

   SQL Copy

   CREATE USER report PASSWORD Password1 NO FORCE_FIRST_PASSWORD_CHANGE set usergroup default;

   Using the clause NO FORCE_FIRST_PASSWORD_CHANGE is not considered a security best practice! We will only use this option for the purpose of this tutorial, in our example to make a user available to multiple individuals. If you create users in your productive environment, please consider forcing a password change for the first log in and giving individuals different users.

   

3. Now that our new user report is created, we need to grant the user access to the role PublicAccessSchema.

4. Use the following statement.

   SQL Copy

   GRANT HDI_TUTORIAL."PublicAccessSchema" to report;

   Notice that the schema name is the value you copied in the beginning of this tutorial.

You can also use the default role that has the container name.default access. As soon as an HDI container is created, the default access role is created. You can find the name of the role by using the statement SELECT * FROM ROLES

The role you are looking for should consist of the schema name and the role name access_role.

If you don’t need a customized role, you can use this one. In a productive system, we recommend creating your own roles with just the privileges needed.

You have successfully created the new user report and assigned it a role to access your calculation view. With the new user credentials, anyone who has the credentials for this user can run SELECT statements on your calculation view.

1. To test this, first log in with your new user by typing the following statement:

   SQL Copy

   CONNECT report PASSWORD Password1;

2. You should now see at the top of the screen, over the SQL console the user you connected with.

3. Since you granted this user SELECT privileges, you should be able to now run the statement below with the user report.

   SQL Copy

   SELECT TOP 1000 
       "AGENCYNUM",
       "NAME",
       "ORDERDAY",
       "NUMBOOKINGS",
       "DAYCOUNT"
   FROM "HDI_TUTORIAL"."calculationView";

   

   And with that last step, you have completed the last tutorial of this mission! You learned how to create a new user role and how to grant access to your calculation view using SAP Business Application Studio and SAP HANA Database Explorer.

Congratulations! You successfully completed this mission!

You achieved your goal of helping Alex find the business insights they were looking for and share these insights with others!

Throughout the tutorials of this mission, you have learned how to provision your first SAP HANA Cloud, SAP HANA database instance, how to use the different SAP HANA Cloud tools, how to import and query data, how set up a development project, create and calculation view and how to make it available to others within your organization.

Make sure to get the badge for this mission and share it on your profile in the SAP Community. Click here for more learning materials on SAP HANA Cloud.

Follow our tag in the SAP Community to stay up-to-date on the latest updates and newest content!