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.
Step 1Understand roles and privileges
—
SAP HANA Cloud, SAP HANA database defines user permissions and privileges using a role-based security model.
Roles and privileges can be granted to users or revoked from users. A role is a set of privileges that can, as a group, be assigned to a user. Then, as the role’s privileges change, the user’s privileges change accordingly. Roles can be broken down as follows:
User-Defined Roles are a custom collection, often created to group privileges and tasks
System Roles are built-in and automatically created with a new database
A privilege provides the ability to perform an operation on the system. A permission, on the other hand, is that ability in the given environment. A user may not have permission to perform a task if they have the privilege, but not on the currently acted on object. Privileges are broken down as follows:
System privileges give you the right to perform the action
Object-level privileges restrict your right to perform the action to the specified objects, on which the privilege is granted.
When a new object is created, the owner can be defined, otherwise, the creator becomes the owner. This gives privileges to modify the structure of the table and grant other privileges to other database users.
Ownership of a table is not sufficient to load the table with data. The user must also have INSERT permission on the table.
Step 2Create users and roles and manage privileges
+
Before you add users to an instance, you should create user roles that fit your needs. You can leverage some of the default user roles, edit them, or create completely customized ones.
There are two ways of creating roles you need to know: You can create roles using SQL, or you can use the role management app in SAP HANA Cloud Central.
In this step, you can find instructions on both of these options. Click on SQL or User, Role, and Privileges Apps under the headline of this step to select the option you prefer.
Create users and roles using the SQL console in HANA Cloud Central
Open SAP HANA Cloud Central. Then navigate to the SQL console tab for the SAP HANA Cloud, SAP HANA database instance.
SQL Console tab in HANA Cloud Central
The SQL Console will appear and you will now be able to execute the necessary SQL statements for this tutorial.
SQL console UI
Users can be created with this simplified statement. You can replace the contents inside the <> placeholders to set your desired credentials for your new user. The username must be unique in the database and the password must contain lower case, upper case, and a digit.
Code
CREATE USER <username> PASSWORD "<password>";
Please note that this statement is simplified for an easy start, and you have many more options to create the user the way you need it. For example, you can choose different authentication options, validity specifications, optional user parameter options, or specify users to be restricted.
To avoid using the super-user DBADMIN, we will now create a new user UPSGRANTOR. This user will be used in a later tutorial when you create a user-provided service in SAP HANA Business Application Studio. Paste the following query and execute the statement.
SQL
-- Create a database user that should assign these privileges
CREATEUSERUPS_GRANTORPASSWORD"Password1"NOFORCE_FIRST_PASSWORD_CHANGESETUSERGROUPDEFAULT;
To grant this user roles and privileges, you can use the GRANT statement. To use this statement to grant a certain privilege, you must have the privilege and permissions required to grant this privilege.
First create genericRoleForOO and genericRoleForAP roles. These are generic roles for an object owner (OO) and application user (AP), which will be later used in a later tutorial with SAP Business Application Studio.
When granting roles to users, the statement needs to be adjusted as follows:
Code
GRANT <role_name> TO <user_name>;
To allow a user to grant a privilege to other users themselves, you need to add WITH GRANT OPTION to the end of the statement. For roles and system privileges, you need to add WITH ADMIN OPTION to the statement.
Assign the newly created roles SELECT privileges on the SFLIGHT schema you previously imported from a previous tutorial. The object owner role genericRoleForOO will be assigned a grant option.
The generic structure to grant privileges on a certain schema or object to a user is as follows:
Code
GRANT <privilege> ON <SCHEMA_or_OBJECT> <schema_or_object_name> TO <user_name>;
Paste the following queries in a SQL console and execute the statements.
SQL
-- Assign privileges that these roles should grant
GRANTSELECTONSCHEMASFLIGHTTO"genericRoleForOO"WITHGRANTOPTION;GRANTSELECTONSCHEMASFLIGHTTO"genericRoleForAP";-- Allow UPS_GRANTOR to grant the respective roles
GRANT"genericRoleForOO"toUPS_GRANTORWITHADMINOPTION;GRANT"genericRoleForAP"toUPS_GRANTORWITHADMINOPTION;
You can find all the details about syntax elements and all available privileges to grant in the technical documentation here.
Create users and roles in the users and roles apps
Within SAP HANA Cloud Central, select your instance by clicking on it and scroll to the User & Authorization Management app.
Open cockpit from HCC
Click on Roles to get started.
You will be directed to the Role Management page, where you can see a list of all existing user roles as well as role groups. If you click on one of them, you will see the details of this role on the right-hand side of the screen. Clicking on one of the roles allows you to edit them, for example, you can assign System, Object and Analytic Privileges and more.
HANA cockpit Role Management submenu
To create a new user role, click on the Create role button.
This opens the role creation wizard on the right-hand side of the screen. First create a role named genericRoleForOO. Leave the rest of the settings as is. This role will be used in a later tutorial when you create a development project using SAP Business Application Studio.
Click on Create at the bottom right corner of the screen.
HANA cockpit security create role for object owner
Create another role named genericRoleForAP, which represents a generic role for an application user. Leave the rest of the settings as is. This role will be used in a later tutorial when you create a development project using SAP Business Application Studio.
Now that you created the necessary roles, it’s time to assign privileges to it. You have a few options here. You can add some of the existing roles into this one, combining the privileges into one single role. You can also select individual privileges, be it system, object, or analytic privileges.
For the genericRoleForAP user, go to the Object Privileges tab and select Edit Object Privileges, then Add Object at the top of the table.
HANA cockpit security add privilege
For more technical details on creating roles and deciding on privileges, please see our technical documentation here.
Under Object, search for SFLIGHT. Select the result with Object Type SCHEMA.
Select SLFIGHT object
Press Select at the bottom-right corner.
Under Select Privileges, scroll to find SELECT and click on the checkbox. This will grant SELECT privileges to your user.
Select privileges for user
Press OK when done. Then press Save to ensure that your changes are saved.
Repeat steps 8 - 9 for the genericRoleForOO role. When you reach the Add Objects with Privileges pop-up, scroll to SELECT and click the checkbox and enable the toggle under Grantable to Others.
Add select privileges with grant option
Don’t forget to press Save when finished.
Your first big step is done! Now it’s time to create individual users.
To get started, switch to the User Management app.
Select User Management
This screen works just like the Role Management page, so click on the Create User to add a new user.
HANA cockpit security user mgmt
Give the User Name UPS_GRANTOR.
Create a user in cockpit
Select Password for the authentication method.
Set the password to Password1.
After retyping the password to confirm it, select No under Force Password Change on Next Logon. Press Save when finished.
No force password change
To know more about creating user and restricted users, visit the documentation here.
Click on Role Assignment in the top-right corner.
Role assignment dropdown
Click Edit Assigned Roles, then Add.
Add role assignment
Search for generic and check the checkboxes both genericRoleForAP and genericRoleForOO. Press Select when done.
Select generic roles
Under the Grantable to Others column, enable the toggles by clicking on them.
Enable the toggles to be grantable to others
Press Save at the top of the table when finished.
You can follow the steps on our technical documentation to create each user and make decisions about the roles, privileges and authorizations to give them: Creating a Database User.
Well done!
You have completed the sixth tutorial of this mission! Now you know how you can manage access rights in your instance by creating users, and granting roles and privileges using SAP HANA Cloud Central. You also have the necessary users, roles, and privileges to proceed with the a future tutorial group on creating a calculation view.
You now know all the basics to start working with our sample data and help Alex gain business insights about their company, Best Run Travel.
Learn in the next tutorial how to query the database using SQL statements.
Share feedback on this tutorial or join the conversation in SAP Community.
Submit detailed feedbackDiscuss in Community
Steps
Step 1 of 3
1. Understand roles and privileges2. Create users and roles and manage privileges3. Knowledge Check
Joule
AI Notice
Joule is an AI assistant. Generative AI may produce inaccurate, incomplete, or biased information. Always verify important details before acting on them.
Conversations are sent to SAP-hosted large language models for processing. Do not include personal data, credentials, or confidential information in your messages.
Joule's responses are based on the SAP tutorial catalog and may not reflect the latest product changes. For authoritative guidance, consult the linked tutorials and official SAP documentation.
