Grant Access to Calculation Views
- What privileges are needed for others to access your calculation view
- How to create a role in SAP Business Application Studio
- How to create a user in the SAP HANA Database Explorer and connect as this user
- Sign up for the SAP HANA Cloud trial.
- If you have a production environment of SAP HANA Cloud, SAP HANA database, you may also follow the steps described in this tutorial.
- Provision an instance of SAP HANA Cloud, SAP HANA database.
- Import the sample data needed for this mission.
- Set up a development project in SAP Business Application Studio and connect it to your database.
- Create a calculation view
Reminder: This tutorial is part of a mission, in which you will help Alex, the CEO of Best Run Travel, to answer a concrete business question with SAP HANA Cloud, SAP HANA database.
Alex needs to know the top 5 partners of their agency and wants to find out the days with maximum booking of each partner.
This mission consists of 9 modules that contain the necessary steps you need to follow in your mission to help Alex:
Start using an SAP HANA Cloud trial in SAP BTP Cockpit
Provision an instance of SAP HANA Cloud, SAP HANA database
Tools to manage and access the SAP HANA Cloud, SAP HANA Database
Create users and manage roles and privileges
Import data into SAP HANA Cloud, SAP HANA Database
Query the database using SQL statements
Create a development project in SAP Business Application Studio
Create a calculation view
You are here
In this tutorial, you will learn how to make the calculation view you created previously available to others in the organization by creating a new role and granting this role access to the view.
You can follow the steps in this tutorial also by watching this video:
About this video
This video is meant as additional support material to complete the tutorial. However, we recommend that you only use it for visual guidance but primarily focus on the written steps in this tutorial.
- Step 1
To allow others to see the results of your calculation view, you need to grant them the privilege to run
SELECTstatements on this calculation view.
SELECTstatements on calculation views in the new environment, first make sure that the you have the correct schema name. This schema is not the
SFLIGHTschema we previously created, but rather the schema automatically created for the calculation view.
To find out the schema name, open the SAP HANA Database Explorer.
Find your HDI container in the catalog.
Not sure how to find the HDI container of your project 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:
Alternatively, you can go to SAP Business Application Studio and click on the HDI container icon (), which will directly open the right container in the catalog.
Click on Column Views.
Right-click on the column view name on the bottom panel and choose to Generate a SELECT Statement.
This will open the SQL Console on the main area of the screen with the
line 7you can see a
FROMclause with two arguments separated by a
.. The first part is the schema name, the second part is the calculation view name.
Copy the name of the schema and save it for later, for example using a text editor.
Keep this SQL console open.
- Step 2
Great, now that you have the right schema name, next you will have to grant the authorization to
SELECTon the Calculation View. This is done by creating an
.hdbrolefile in your development project that grants the
Go to your project in SAP Business Application Studio and start your development space if needed.
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 on the top menu and selecting Find command or pressing
Ctrl+Shift+P. Unless you see it in your recently used commands, type
SAP HANAand select it from the list.
In the wizard, make sure the path to save the role file is in the SRC folder of your project.
Choose the database version HANA Cloud.
Select the artifact type as
Name your role
Finally, click on Create.
Your role will appear in the file explorer of your project and you can add privileges to it.
- Step 3
In this step, you have two options to add privileges to your role: You can use the Role Editor wizard or the Code Editor. Click on the option you prefer under the title of this step.
The goal in this step is to add the schema privileges
EXECUTEto the role
.hdbrolefile by clicking on the deploy icon () next to it on the SAP HANA Projects area or on the top right corner of the main panel.
After you are done, deploy the whole project again. When that is completed successfully, you can continue.
- Step 4
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
reportthat shall have read-only access to the calculation view.
Go back to your tab with the SAP HANA Database Explorer and open a SQL Console by clicking on the SQL icon () at the top left corner. Make sure that the connection is opened for an user that has system privileges
USER ADMIN, e.g., database user
Paste the following statement in the SQL Console. Change the password in the statement and then run.SQLCopy
CREATE USER report PASSWORD <your_password> NO FORCE_FIRST_PASSWORD_CHANGE set usergroup default;
Using the clause
NO FORCE_FIRST_PASSWORD_CHANGEis 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.
Now that our new user
reportis created, we need to grant the user access to the role
Use the following statement.SQLCopy
GRANT <SCHEMA_NAME>."PublicAccessSchema" to report;
<schema name>with the calculation view schema you copied in the beginning of this tutorial.
Make sure to remove the
<>characters and then run the statement.
Custom vs. default roles
You can also use the default role that has the
container name.defaultaccess. 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
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.
- Step 5
You have successfully created the new user
reportand assigned it a role to access your calculation view. With the new user credentials, anyone who has the credentials for this user can run
SELECTstatements on your calculation view.
To test this, first log in with your new user by typing the following statement:
CONNECT report PASSWORD <Your_Password>
You should now see at the top of the screen, over the SQL console the user you connected with.
Since you granted this user
SELECTprivileges, you should be able to run
SELECTstatements on the column view.
Go back to the SQL console you opened in STEP 1, when you generated a
SELECTstatement of the column view. If you have closed it in the meantime, simply right-click on the column view and click on Generate a SELECT Statement.
Copy the whole statement from this SQL console and paste it to the console that you used to connect with the user
Execute the statement using the
report-user console. When you see the results of this query you know that your test was successful, and the user can now access your view.
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 modules 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.
To quickly recap the tutorial mission, you can also watch all the mission videos in a playlist on
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!
- Step 6
Enter the SQL command used to log in as a different user in the SAP HANA Database Explorer.