4.4    Managing User Role Assignments

Roles are a special type of database object that define a specific set or collection of privileges. These reusable objects can be granted to multiple users or even to other roles. Roles are typically named and configured to support specific functions or tasks within the SAP HANA platform. In Chapter 5 and Chapter 6, we’ll discuss the processes and workflows necessary for creating roles, but for now, let’s discuss how roles can be granted to user accounts.

When granting a role, you’re essentially assigning all the privileges within the role to a specified user. Any future updates to the role will automatically be granted to all users that are assigned the role. To grant a role to a user account or another role, the user performing the action must have the ROLE ADMIN system privilege. Alternatively, a user can also grant access to a specific role if the user was granted that same role with the WITH ADMIN OPTION or the Grantable to Other Users and Roles option.

Three basic interfaces can be used to both grant and revoke roles: the SQL console to issue SQL statements, the SAP HANA cockpit, and the SAP HANA Web-Based Development Workbench. We’ll discuss each of these approaches in the following sections, along with a brief look at how you can view the roles assigned to a specific user.

4.4.1    Granting and Revoking Roles with SQL

When granting roles to a user, you must first determine the role type. If created by a standard user account or provisioned by the system during installation, we refer to this role as a standard role. These roles often are created using the CREATE ROLE SQL syntax. Standard roles can be granted and revoked with the GRANT and REVOKE SQL statements.

Alternatively, if created as a development artifact and owned by the system account _SYS_REPO, we refer to this role as a repository role. Repository roles can only be granted or revoked using a special stored procedure and by users with the object privilege EXECUTE on these procedures. Let’s look at the two different options in more detail.

Standard Roles

Granting standard roles to a user account with SQL is performed using a syntax similar to the syntax used when granting privileges, as described earlier in Section 4.3.1. To grant a role to a user, you must use a GRANT SQL statement. The following three examples SQL statements demonstrate the syntax necessary for granting roles to a user, the syntax including an additional option, and an example:

GRANT <ROLE_NAME> TO <USER>;    
GRANT <ROLE_NAME> TO <USER> WITH ADMIN OPTION;    
GRANT MODELING, DBA_COCKPIT, CONTENT_ADMIN TO JONATHAN WITH ADMIN OPTION;

The variable <ROLE_NAME> can be replaced with a comma-separated list of roles. The <USER> variable can be replaced with either a user account or another role name. The WITH ADMIN OPTION can be added to allow a user account the ability to grant the specified role to another user or role even if that user doesn’t have the ROLE ADMIN system privilege.

To revoke a role using SQL, you must issue a REVOKE SQL statement. The following two example SQL statements demonstrate the syntax for removing or revoking a role from a user account:

REVOKE <ROLE_NAME> FROM <USER>;    
REVOKE ROLE ADMIN FROM JONATHAN;

Repository Roles

When a role is created in the SAP HANA development repository, that role is owned by the system user _SYS_REPO. These roles are repository roles. You can’t use the standard GRANT and REVOKE SQL syntax to assign repository roles to a user; instead, you must use a specific stored procedure to grant (and another specific stored procedure to revoke) repository schema privileges.

The first stored procedure is stored in the schema _SYS_REPO and is named GRANT_ACTIVATED_ROLE. To use this procedure, the security administrator must have the EXECUTE object privilege for the stored procedure. The procedure has two input parameters: The first input parameter requires the name of one repository role and doesn’t support multiple repository roles; the second input parameter requires the name of a single user account. The following SQL statement uses variables for the role name and user name parameters:

CALL 
"_SYS_REPO"."GRANT_ACTIVATED_ROLE"('<REPOSITORY_ROLE_NAME>', '<USER_NAME>');

The following example demonstrates the execution of the procedure with sample parameters:

CALL 
_SYS_REPO"."GRANT_ACTIVATED_ROLE"('sap.hana.ide.roles::Developer',
'TEST_USER');

The second stored procedure is stored in the schema _SYS_REPO and is named REVOKE_ACTIVATED_ROLE. To use this procedure, the security administrator must have the EXECUTE object privilege for the stored procedure. The procedure has two input parameters: The first input parameter requires the name of one repository role and doesn’t support multiple repository roles; the second input parameter requires the name of a single user account. The following SQL statement uses variables for the role name and user name parameters:

CALL 
"_SYS_REPO"."REVOKE_ACTIVATED_ROLE"('<REPOSITORY_ROLE_NAME>', '<USER_NAME>');

The following statement demonstrates the execution of the procedure with sample parameters:

CALL "_SYS_REPO"."REVOKE_ACTIVATED_ROLE"('sap.hana.ide.roles::Developer','TEST_USER');

Full SQL Syntax

The complete available syntax necessary for granting and revoking privileges and roles and for creating and managing user accounts is beyond the scope of this book. To review the full syntax and all available options, refer to the SAP HANA SQL and System Views Reference Guide, available at https://help.sap.com/hana_platform.

Search for sections pertaining to GRANT, REVOKE, CREATE USER, ALTER USER, and DROP USER statements.

Let’s now look at how to use the SAP HANA cockpit to grant a role to a user account.

4.4.2    Granting and Revoking Roles with the SAP HANA Cockpit

As an alternative to using SQL statements, you can use the SAP HANA cockpit GUI action called Assign role to Users. This interface allows you to both grant and revoke roles for a given user account. Most users will find using the SAP HANA cockpit easier than memorizing and entering in SQL statements.

To access the Assign role to Users area, start at the SAP HANA cockpit Home screen and locate an SAP HANA system in the Resource Directory. Connect to the desired tenant database or SYSTEMDB, which will take you to the System Overview window. Using the Filter by Area dropdown list, choose the Security option. Click on Assign role to Users in the User & Role Management tile. The Assign Roles window will be launched, where your first and only option is to search for a user account. Enter the name of a user account into the User field. The field supports search ahead capabilities, meaning that the system will start displaying one or more user accounts that match the entered text as you start typing. Choose or enter the requested user to reveal a new GUI where you can assign individual privileges to the user account.

Figure 4.16 shows the Assign Roles interface within the SAP HANA cockpit for the TEST_01 user account. You’ll see existing roles that have been granted to the user account. On the right side of the window, you’ll see an Edit button, which you’ll use to grant or revoke roles from the user account.

Figure 4.16    Assign Roles GUI Interface in the SAP HANA Cockpit

Once the Edit button is click, four new buttons will appear named Save, Cancel, Add, and Remove. To grant a role, click the Add button. A new Select Roles search popup window will appear. Use the search bar to filter the list of roles in the popup window. To select a role, select the checkbox next to each role you want to grant and then click OK once you’ve completed your selections. You’ll now return to the Assign Roles window, and your selected roles should now appear in the list.

Notice that, next to each role in the list, is a checkbox. Assuming you have activated the edit mode, you can use these checkboxes to revoke any unsaved or currently granted roles. To revoke the role, select the checkbox for each role you want to revoke and then click the Remove button. Once you click the Remove button, the roles will be removed from the list.

Once you have the role list configured, lick the Save button to grant the roles in the list or to revoke any existing roles that are no longer in the list. If you do not want to save your changes, click the Cancel button to discard these changes.

4.4.3    Granting and Revoking Roles with the SAP HANA Web-Based Development Workbench

As mentioned earlier in Section 4.2.3 and Section 4.3.3, the SAP HANA Web-Based Development Workbench security manager, hosted within the XS engine, provides a GUI that you can use to create users, create roles, or manage security. You can access the SAP HANA Web-Based Development Workbench security manager by filling in the following two URLs with the details of your own environment:

• http://<sap_hana_host>:80<instance_number>/sap/hana/ide/security

• https://<sap_hana_host>:43<instance_number>/sap/hana/ide/security

Once you have access, expand the Security node on the right side of the window, then expand Users to reveal a list of user accounts. Double-click a user to open the management interface for that user. With the selected User Management window open, you can begin granting and revoking roles using the Granted Roles tab.

Figure 4.17    Managing a User’s Roles in the SAP HANA Web-Based Development Workbench: Security

Click the add icon (green plus sign) to add new roles to the user account. Once the add icon is clicked, the Find Role search dialog will appear allowing you to filter the list of roles. By default, this search interface lists all roles in the system. Within the search dialog, select the roles you want to grant and then click OK to return to the Granted Roles tab. To revoke a role, select them one at a time in the Granted Roles tab and then click the red X to remove the role from the list. Figure 4.17 shows an overview of a user’s Granted Roles tab and the various buttons used to grant and revoke roles.

Once you’ve made all the desired role changes, click the save icon (floppy disk) to save and activate the changes. If any issues arise when saving these changes, locate a description of the issue in red text in the section just below the user management tab. If the action is successful, white text will appear in this section confirming that the changes were successful.

4.4.4    Effective Roles System View

Periodically, you’ll need to query the system to view the roles that have been granted to a specific user. This database view is included in the SYS schema and is called EFFECTIVE_ROLES. This view lists all the roles currently granted directly and indirectly to a specified user account. The view must be filtered to a single user account before it can execute. Listing 4.8 shows an example of the SQL required to query this view.

SELECT
"USER_NAME", "GRANTEE", "GRANTEE_TYPE", "GRANTOR", "ROLE_NAME", "IS_GRANTABLE"
FROM "SYS"."EFFECTIVE_ROLES" 
WHERE ("USER_NAME" = '<USER_NAME>');

Listing 4.8    SQL Code Executed against the EFECTIVE_ROLES View

The variable <USER_NAME> should be replaced with an active user account name. The view won’t execute unless the WHERE clause contains a filter on USER_NAME column. For a complete list of additional filters, refer to the SAP HANA Reference Guide available at https://help.sap.com/hana/SAP_HANA_SQL_and_System_Views_Reference_en.pdf.

In conclusion, the workflows and steps necessary to grant and revoke roles using the SAP HANA Web-Based Development Workbench security manager is quite similar to the processes used in the SAP HANA cockpit. One advantage of using the SAP HANA Web-Based Development Workbench is that administrators won’t need to install the SAP HANA cockpit to grant or revoke roles. In the next section, we’ll explore a case study that demonstrates the process for provisioning users programmatically.