4.3    Granting and Revoking Privileges

Although we recommend that you never maintain privileges on a user-by-user basis, in some instances, you’ll need to assign privileges to individual user accounts. For example, you might need to assign privileges to temporarily test a scenario, or you might need to change a service account’s privileges. Three different interfaces can be used for granting and revoking privileges to a user account: SQL statements, SAP HANA cockpit user management, or the SAP HANA Web-Based Development Workbench security manager. Let’s look at all three options and describe how to use each. Then, we’ll discuss how to use system database views to list all the privileges assigned to a given user account.

4.3.1    Granting and Revoking Privileges with SQL

Using the SQL console and SQL statements, you can both grant and revoke privileges for user accounts. When granting privileges to some objects, you must be aware of the type of object. Specifically, you’ll need to use a different syntax for repository-based objects because they’re owned by the _SYS_REPO system account. With this account, you can’t log on as _SYS_REPO, so you have no way to delegate grant and revoke privileges to other users in the system. For example, you can’t log on as _SYS_REPO and grant SELECT to another user while including the WITH ADMIN OPTION statement or the WITH GRANT OPTION statement. Because of this limitation, you must use a series of system-delivered stored procedures to grant privileges for repository objects owned by _SYS_REPO. These stored procedures are activated in the _SYS_REPO schema and can be called directly, assuming you have the EXECUTE privilege for the procedures. Let’s look at some example SQL statements that illustrate how you can grant privileges based on the privilege type.

System Privileges

You can grant system privileges using the GRANT SQL syntax. The following three SQL statements are separated by a semicolon after each:

GRANT <SYSTEM_PRIVILEGE> TO <USER>;    
GRANT <SYSTEM_PRIVILEGE> TO <USER> WITH ADMIN OPTION;    
GRANT DATA ADMIN, EXPORT, IMPORT TO JONATHAN WITH ADMIN OPTION;

The first statement provides a generic template with variables. The variables are indicated by text within angle brackets (e.g., <USER>). The variables will need to be replaced with items unique to your environment. The <SYSTEM_PRIVILEGES> variable can be replaced with a comma-separated list of system privileges, as indicated in the third example. The second SQL statement includes the WITH ADMIN OPTION at the end of the statement. This option allows the grantee to subsequently grant the same privilege to another user and is typically reserved for user accounts or roles that manage security models. The final statement provides a full example in which you’ll grant three different system privileges to the user JONATHAN. This statement includes the WITH ADMIN OPTION, which allows the account JONATHAN to grant the same privileges to other users or roles.

You can also revoke system privileges using the REVOKE SQL syntax. The following statements demonstrate the REVOKE syntax:

REVOKE <SYSTEM_PRIVILEGE> FROM <USER>;    
REVOKE DATA ADMIN, EXPORT, IMPORT FROM JONATHAN;

The first statement provides a generic template with variables. The variables are indicated by text within angle brackets (e.g., <USER>). The variables will need to be replaced with items unique to your environment. The <SYSTEM_PRIVILEGES> variable can be replaced with a comma-separated list of system privileges, as indicated in the second example. The second statement provides a full example in which the listed system privileges are removed from the user account JONATHAN.

Catalog Schema Privileges

If a schema is owned by a standard database user, you can use one of the following SQL statements to grant one or more privileges to a user account:

GRANT <SCHEMA_PRIVILEGE> ON SCHEMA <SCHEMA_NAME> TO <USER>;    
GRANT <SCHEMA_PRIVILEGE> ON SCHEMA <SCHEMA_NAME> TO <USER> WITH GRANT OPTION;    
GRANT SELECT, EXECUTE, DROP ON SCHEMA "MySchema" TO JONATHAN;

The <SCHEMA_PRIVILEGES> variable can be replaced with a comma-separated list of schema object privileges, as indicated in the third statement. When included, the WITH GRANT OPTION clause at the end of the statement allows the user to subsequently grant the same privilege to another user.

To remove the privileges, use the following syntax:

REVOKE <SCHEMA_PRIVILEGE> ON SCHEMA <SCHEMA_NAME> FROM <USER>;
REVOKE SELECT, EXECUTE, DROP ON SCHEMA "MySchema" FROM JONATHAN;

The <SCHEMA_PRIVILEGES> variable can be replaced with a comma-separated list of schema object privileges, as indicated in the second statement.

Note that GRANT and REVOKE statements won’t work on repository-based schemas. Such schemas are owned by the system account _SYS_REPO and must be granted using a special stored procedure. In the next section, we’ll provide example SQL statements for such cases.

Repository Schema Privileges

When a schema is created in the SAP HANA development repository, that schema will be owned by the system user _SYS_REPO. We call these types of schemas repository schemas. In such cases, you can’t use the standard GRANT and REVOKE SQL syntax to manage permissions for a given user account. Instead, you must use a specific stored procedure to grant and another to revoke repository schema privileges.

To grant repository schema privileges, you’ll need to execute a stored procedure in the _SYS_REPO schema named GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT. To use this procedure, the security administrator must have the object privilege EXECUTE on the stored procedure. This procedure has three input parameters. The first parameter can accommodate a comma-separated array of schema privileges. The second parameter can accommodate a single schema name. The third parameter can accommodate a user name or role name.

In the following example, you’ll need to replace the variable <OBJECT_PRIVILEGES> with one or more schema privileges; replace the variable <SCHEMA_NAME> with the case-sensitive name of our schema (if the name is lowercase or mixed-case, use double quotes around the name); and replace <USER_NAME> with the name of a user defined in the SAP HANA system:

CALL "_SYS_REPO"."GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT"
('<OBJECT_PRIVILEGES>','<SCHEMA_NAME>','<USER_NAME>');

The following SQL statement represents a complete example based on objects in a demo environment; multiple schema privileges are granted to the user JONATHAN against the schema "MySchema":

CALL "_SYS_REPO"."GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT"
('SELECT, EXECUTE, UPDATE, CREATE ANY' ,'"MySchema"', 'JONATHAN');

To revoke repository schema privileges, you’ll need to execute a stored procedure in the _SYS_REPO schema named REVOKE_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT. This procedure has three input parameters, which are identical to those the preceding procedure used to grant privileges. The following SQL statements can be used to call the stored procedure equipped to revoke repository schema privileges; the second statement provides an example revoking privileges previously granted to the user account JONATHAN:

CALL "_SYS_REPO"."REVOKE_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT"
('<OBJECT_PRIVILEGES>','<SCHEMA_NAME>','<USER_NAME>');
CALL "_SYS_REPO"."REVOKE_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT"
('SELECT, EXECUTE', '"MySchema"', 'JONATHAN');

Catalog Object Privileges

If a catalog object is owned by a standard database user, you can use one of the following SQL statements to grant catalog object privileges to a user or role. The <OBJECT_PRIVILEGE> variable can be replaced with a comma-separated array of privileges. The WITH GRANT OPTION, when specified, allows the grantee the ability to subsequently grant the same privileges to other users in the system, as shown in Listing 4.3.

GRANT <OBJECT_PRIVILEGE> ON <OBJECT_NAME> TO <USER>;    
GRANT <OBJECT_PRIVILEGE> ON <OBJECT_NAME> TO <USER> WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE ON “MySchema”.”MyTable” TO JONATHAN WITH GRANT OPTION;

Listing 4.3    Three Example SQL Statements Granting Object Privileges to a Grantee

To revoke these privileges, you can use one of the following statements:

REVOKE <OBJECT_PRIVILEGE> ON <OBJECT_NAME> FROM <USER>;    
REVOKE SELECT, INSERT, UPDATE, DELETE ON “MySchema”.”MyTable” FROM JONATHAN;

Repository Object Privileges

When a catalog object is created in the SAP HANA development repository, that catalog object will be owned by the system user _SYS_REPO. We call these types of objects repository catalog objects. In these cases, you can’t use the standard GRANT and REVOKE SQL syntax to manage permissions for a given user account. Instead, you must use a specific stored procedure to grant (and another specific stored procedure to revoke) repository catalog object privileges.

To grant repository catalog object privileges, you’ll need to execute a stored procedure in the _SYS_REPO schema named GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT. To use this procedure, the security administrator must have the EXECUTE object privilege for the stored procedure. This procedure has three input parameters: The first parameter can accommodate a comma-separated array of schema privileges, the second parameter can accommodate a single schema name, and the third parameter can accommodate a user name or role name. As shown in Listing 4.4, you’ll need to replace the <OBJECT_PRIVILEGES> variable with one or more schema privileges and replace the <OBJECT_NAME> variable with the case-sensitive and fully qualified name of our catalog object. Additionally, if the name is lowercase or mixed case, include double quotes around the schema and catalog object name and replace <USER_NAME> with the name of a user defined in the SAP HANA system.

CALL "_SYS_REPO"."GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT"('<OBJECT_PRIVILEGES>',
'<OBJECT_NAME>','<USER_NAME>');
CALL
"_SYS_REPO"."GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT "('SELECT, EXECUTE', 
'"StagingMart"."e-corp.sales.tables::STG_CUSTOMERS"', 'JONATHAN');

Listing 4.4    Two Example SQL Statements Granting a Repository-Based Object’s Object Privileges to a Grantee

To revoke repository catalog object privileges, you’ll need to execute a stored procedure in the _SYS_REPO schema named REVOKE_PRIVILEGE_ON_ACTIVATED_CONTENT. This procedure has three input parameters, which are the same as those used in the previous procedure to grant the privileges. The SQL statements shown in Listing 4.5 call the stored procedure equipped to revoke catalog object privileges; the second statement provides an example specifically revoking catalog object privileges previously granted to the user account JONATHAN.

CALL 
"_SYS_REPO"."REVOKE_PRIVILEGE_ON_ACTIVATED_CONTENT "('<OBJECT_PRIVILEGES>',
'<SCHEMA_NAME>','<USER_NAME>');
CALL "_SYS_REPO"."REVOKE_PRIVILEGE_ON_ACTIVATED_CONTENT"('SELECT, EXECUTE' , 
'"StagingMart"."e-corp.sales.tables::STG_CUSTOMERS"' , 'JONATHAN');

Listing 4.5    Two Example SQL Statements Revoking a Repository-Based Object’s Object Privileges from a Grantee

Structured Privileges

Structured privileges are a special type of object that forces the system to provide additional access restrictions against a column view or catalog view. In Chapter 3, we introduced analytic privileges, which are used with SAP HANA information views to provide data access to an activated information view. We’ll discuss analytic privileges in more detail in and Chapter 10. Technically, analytic privileges are a type of structured privilege, but most GUIs refer to structured privileges as analytic privileges. Repository-based analytic privileges are owned and managed by the system account _SYS_REPO. However, structured privileges can be owned by any user with the required system privileges.

If a structured privilege is owned by a standard database user, then you can use the GRANT STRUCTURED PRIVILEGE SQL syntax to grant such privileges to a user account. To grant a structured privilege, you must be its owner because there is no WITH ADMIN OPTION to allow another user to grant this privilege. Note that this this syntax will not work on repository-based analytic privileges. In the following example, replace the <STRUCTURED_PRIVILEGE_NAME> variable with the name of the structured privilege:

GRANT STRUCTURED PRIVILEGE <STRUCTURED_PRIVILEGE_NAME> TO <USER_NAME>;

As an example, we’ll walk through the process of creating a catalog view and a structured privilege and the process for granting the privilege to a user. Start by using the SQL statement shown in Listing 4.6 to create a standard catalog view.

CREATE VIEW "MySchema"."MYVIEW" ( "MYKEYCOLUMN",
     "STATE",
     "CITY",
     "POSTALCODE" ) AS select
     T0."MYKEYCOLUMN",
     T0."STATE",
     T0."CITY",
     T0."POSTALCODE" 
FROM "SYSTEM"."MYTABLENAME" T0
WITH STRUCTURED PRIVILEGE CHECK;

Listing 4.6    SQL Code to Create the MYTABLE View

You’ll then create a structured privilege referencing the view, which will apply a filter on the STATE column. The following SQL statements will create a structured privilege that will filter the STATE column to TN, then, after the privilege name, the FOR SELECT syntax is used to indicate that the privilege should be applied when querying the view:

CREATE STRUCTURED PRIVILEGE "MyStructuredPriv"     
FOR SELECT ON "MySchema"."MYVIEW" WHERE "STATE" = 'TN';

You’ll then grant the structured privilege to the user JONATHAN. Once granted, the user will only see value from the view where "STATE" = 'TN':

GRANT STRUCTURED PRIVILEGE "MyStructuredPriv" TO JONATHAN;

To revoke the structured privilege, you’ll use the REVOKE STRUCTURED PRIVILEGE SQL syntax. The following SQL statements provide first a generic and then a specific example of the SQL syntax:

REVOKE STRUCTURED PRIVILEGE <STRUCTURED_PRIVILEGE_NAME> FROM <USER>;
REVOKE STRUCTURED PRIVILEGE "MyStructuredPriv" FROM JONATHAN;

Remote Sources

To grant access to a remote source or a smart data access federated database connection, issue a SQL statement based on the following example:

GRANT <SOURCE_PRIVILEGE> ON REMOTE SOURCE <SOURCE_NAME> TO <USER>;

The variable <SOURCE_PRIVILEGE> can be replaced by a comma-separated array of privileges specific to remote-source objects. The <SOURCE_NAME> variable can be replaced by the name of the remote source.

To remove remote-source privileges, use the REVOKE SQL statement. The following SQL statement will remove the privileges specified in the <SOURCE_PRIVILEGE> variable array:

REVOKE <SOURCE_PRIVILEGE> ON REMOTE SOURCE <SOURCE_NAME> FROM <USER>;

Repository Analytic Privileges

When you create a standard analytic privilege, that privilege is owned by the system user _SYS_REPO. Analytic privileges are a type of structured privilege, but you can’t use the standard GRANT and REVOKE SQL syntax to assign analytic privileges to a user. Instead, you must use a specific stored procedure to grant (and another specific stored procedure to revoke) analytic privileges for users.

To grant an analytic privilege to a user, you must execute the stored procedure GRANT_ACTIVATED_ANALYTIC_PRIVILEGE stored in the _SYS_REPO schema. To use this procedure, the security administrator must have the EXECUTE object privilege for the stored procedure. The procedure provides two input parameters: The first parameter contains the name of the analytic privilege, and the second input parameter contains the name of the user. The following SQL statements provide the syntax used to execute the stored procedure, and then a specific example:

CALL "_SYS_REPO"."GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE"
('<ANALLYTIC_PRIVILEGE_NAME>','<USER_NAME>');
CALL "_SYS_REPO"."GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE"
('"e-corp.sales.security/ap_sales_reporting_region"','JONATHAN');

To revoke an analytic privilege from a user, you must execute the stored procedure REVOKE_ACTIVATED_ANALYTIC_PRIVILEGE stored in the _SYS_REPO schema. This stored procedure uses the same two input parameters. The following SQL statements provide the syntax used to execute the stored procedure and a specific example:

CALL "_SYS_REPO"."REVOKE_ACTIVATED_ANALYTICAL_PRIVILEGE"
('<ANALLYTIC_PRIVILEGE_NAME>','<USER_NAME>');
CALL "_SYS_REPO"."REVOKE_ACTIVATED_ANALYTICAL_PRIVILEGE"
('"e-corp.sales.security/ap_sales_reporting_region"','JONATHAN');

Application Privileges

When you create an application privilege, that application privilege is owned by the system user _SYS_REPO. To grant or revoke application privileges, you must use a specific stored procedure. The following SQL statements demonstrate the syntax used to grant an application privilege and a specific example:

CALL "_SYS_REPO"."GRANT_APPLICATION_PRIVILEGE"
('<APPLICATION_PRIVILEGE_NAME>','<USER_NAME>');
CALL "_SYS_REPO"."GRANT_APPLICATION_PRIVILEGE"('"sap.hana.admin::Administrator"','JONATHAN');

The next statements provide the syntax used to revoke an application privilege and a specific example:

CALL "_SYS_REPO"."REVOKE_APPLICATION_PRIVILEGE"
('<APPLICATION_PRIVILEGE_NAME>','<USER_NAME>');
CALL "_SYS_REPO"."REVOKE_APPLICATION_PRIVILEGE"('"sap.hana.admin::Administrator"','DFT');

Package Privileges

To grant access to a package in the SAP HANA repository, issue a SQL statement based on the following syntax; the second statement provides a specific example:

GRANT <PACKAGE_PRIVILEGE> ON <"package.subpackage"> TO <USER> WITH GRANT OPTION;
GRANT REPO.READ, REPO.EDIT_NATIVE_OBJECTS ON "e-corp" TO JONATHAN WITH GRANT OPTION;

The variable <PACKAGE_PRIVILEGE> can be replaced with a comma-separated array of package privileges, and the <"package.subpackage"> variable can be replaced with the case-sensitive name of the package and subpackage hierarchy. Each node of the package hierarchy should be separated by a period. The WITH GRANT OPTION allows the grantee to grant the same privileges to other users.

To remove remote source privileges, use the REVOKE SQL statement. The following SQL statement will remove the privileges specified in the <PACKAGE_PRIVILEGE> variable array; the second statement provides a specific example:

REVOKE <PACKAGE_PRIVILEGE> ON <"package.subpackage"> FROM <USER>;
REVOKE REPO.EDIT_NATIVE_OBJECTS ON "e-corp" FROM JONATHAN;

4.3.2    Granting and Revoking Privileges with the SAP HANA Cockpit

As an alternative to using SQL statements, you can use the SAP HANA cockpit GUI action called Assign Privileges to Users. This interface allows you to both grant and revoke privileges for a given user account. Most users will find it easier to use the SAP HANA cockpit than to memorize and type SQL statements.

To access the Assign Privileges to Users area, start at the SAP HANA cockpit Home screen, 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 option Security. Click on Assign Privileges to Users in the User & Role Management tile, which will launch the Assign Privileges window 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, which means that the system will begin to display one or more user accounts that match the entered text. Choose or enter the requested user to reveal a new GUI where you’ll assign individual privilege to the user account.

Figure 4.8 shows the Assign Privileges interface within SAP HANA cockpit for the TEST_01 user account. You’ll see several privilege categories starting with System Privileges, Object Privileges, Analytic Privileges, Application Privileges, Package Privileges, and Privileges on Users. Each link is an interface via which specific types of privileges can be granted or revoked. In the following sections, we’ll review the System Privileges, Object Privileges, Analytic Privileges, Package Privileges, and Privileges on Users options in more detail. Because Application Privileges represent application security in the SAP HANA XS platform and because the SAP HANA cockpit does not support repository-based role granting, where application privileges should be configured, we won’t go into details with the SAP HANA cockpit.

Managing Privileges Assigned to User Account in the SAP HANA Cockpit

Figure 4.8    Managing Privileges Assigned to User Account in the SAP HANA Cockpit

System Privileges

Let’s first review the workflows necessary to grant and revoke system privileges. The first tab in the Assign Privileges window is named System Privileges. Use this tab to list, grant, or revoke system privileges for a given user account.

The privileges listed in this interface only include privileges already granted to the user. If the list is blank, nothing is currently granted. To grant a system privilege, click the Edit button located on the far-right side of the interface near the Search field. Once you click the Edit button, new buttons labeled Cancel, Add, and Remove will appear. Click the Add button to open the Select System Privileges popup window. Within this window, you’ll see a Search bar and a list of system privileges with checkboxes on the left. Use the search bar to filter the list based on the entered text. Then, scroll through the list and select the checkboxes beside the system privileges you want to grant. With one or more system privileges checked, click the OK button to add the desired items to the user. The system privileges you just selected should now appear under the System Privileges tab, as shown in Figure 4.9.

List of Selected System Privileges

Figure 4.9    List of Selected System Privileges

To assign the Grantable to Others privilege, locate a system privilege in the list. To the right, you’ll see a slider button move to Yes, thus allowing the user the ability to grant the same privilege to another user or role. Repeat this process for each system privilege in the list. Once you’ve added all the desired system privileges to the list, click the Save button to grant the privileges.

To revoke a system privilege, start by clicking the Edit button again, then select the checkboxes beside each system privilege in the list, and click the Remove button to revoke the privileges. Repeat the process for each system privilege you want to revoke. Once you’ve removed all the desired system privileges, click the Save button. The removed privileges will be revoked, and the user object will be updated.

During the process of adding or removing system privileges, you can click the Cancel button to discard any unsaved changes. Clicking the Cancel button will also deactivate the Add, Cancel, and Remove buttons and return you to a read-only list of currently granted system privileges.

Object Privileges

The second tab in Assign Privileges window is named Object Privileges. The process for granting or revoking object privileges is slightly different than the process used for system privileges in the previous section. Privileges listed in this interface have already been granted to the user. If the list is blank, nothing is currently granted. To grant an object privilege, click the Edit button located on the far-right side of the interface near the Search field. Once you click the Edit button to enable edit mode, new buttons labeled Cancel, Add Object, Change Privileges, and Remove Object will appear. Click the Add Object button to open the Select Objects popup window. Within this window, you’ll see a search bar and a list of catalog objects.

Use the search bar to filter the list based on the entered text. The search allows for multiple words, thus allowing you to enter the object name and type. For example, you can enter “_SYS_BIC schema” to filter objects with that name and of a catalog object type. Scroll through the list and highlight the catalog object you want to assign privileges to. Figure 4.10 shows an example of how you can use multiple words to filter to just the _SYS_BIC schema.

Filtering the List of Object Privileges When Searching for a Catalog Object

Figure 4.10    Filtering the List of Object Privileges When Searching for a Catalog Object

Once you click the desired catalog object, the Add Object with Privileges window will appear. In this window, you’ll see a list of possible object privileges that can be assigned. Select the checkbox beside each privilege you want to grant. In addition, for each privilege, you can use the Yes/No slider under Grantable to Others to allow the privilege to be granted to other users. Figure 4.11 shows an example of the interface used to assign object privileges and the Yes/No slider. Click the OK button located at the bottom right to continue.

You should now see the selected catalog object and the privileges you assigned listed in the Object Privileges tab.

Selecting and Granting Object Privileges for a Given Catalog Object

Figure 4.11    Selecting and Granting Object Privileges for a Given Catalog Object

Unlike the SAP HANA Web-Based Development Workbench and the older SAP HANA Studio, the SAP HANA cockpit GUI does not support SAP HANA XS repository-based catalog objects and schemas. Therefore, you cannot grant privileges to design-time objects nor their runtime objects in the catalog. In short, the SAP HANA cockpit is not designed to work with SAP HANA XS repository objects that are owned by the _SYS_REPO account. Why SAP chose not to support this functionality is not clear, but the most likely explanation is that future versions of SAP HANA will not support the SAP HANA XS development architecture.

To revoke an object privilege, click an assigned object and its assigned privilege from the list and then click the Remove Object button to revoke the privilege. To make the changes active, click the Save button or continue making other changes if necessary. Note that, once the Save button is clicked, you’ll be returned to the list of privileges and will need to click the Edit button again to make further changes.

To change assigned object privileges, while in edit mode, click an assigned object and its assigned privilege from the list and then click the Change Privileges button to open a new popup window with the title Change Privileges of concatenated with the name of the object. A list of assigned and unassigned privileges for the object will appear. Select the checkboxes to grant the privilege or deselect the checkbox to revoke the privilege. Use the Yes/No slider to manage the grantable to others privilege. Click the OK button at the bottom right to apply your changes. Again, to make the changes active, click the Save button or continue making other changes as necessary until you’re ready to activate all changes.

Analytic Privileges

The third tab in the user management interface is the Analytic Privileges tab. The process for granting or revoking analytic privileges is like the process used for system privileges discussed earlier in this section.

The analytic privileges listed in this interface have already been granted to the user. If the list is blank, nothing is currently granted. To grant an analytic privilege, click the Edit button located on the far-right near the Search field to enable edit mode. Once you click the Edit button, new buttons labeled Cancel, Add, and Remove will appear. Click the Add button to open the Select Analytic Privileges popup window. Within this window, you’ll see a search bar and a list of runtime analytic privileges with checkboxes. Use the search bar to filter the list based on the entered text. Then, scroll through the list and select the checkbox next to the analytic privilege you want to grant. With one or more analytic privileges selected, click the OK button to add the desired items to the user. Your selected analytic privileges should now appear in the list.

To assign the Grantable to Others privilege, while in edit mode, locate an analytic privilege in the list. To the right, you’ll see a Yes/No slider. Move the slider to Yes to allow the user to grant the same privilege to another user or role. Note that the slider will not be enabled if your current logged in user account does not have the privileges necessary to grant Grantable to Others. Repeat this process for each analytic privilege in the list. Once you’ve added all the desired analytic privileges to the list, click the Save button to grant the privileges.

To revoke an analytic privilege, start by clicking the Edit button again to enable edit mode, then select the checkbox beside each analytic privilege you want to revoke, and click the Remove button to revoke the privileges. Repeat the process for each analytic privilege you want to revoke. Once you’ve removed all the desired analytic privileges, click the Save button. The removed privileges will be revoked, and the user object will be updated.

During the process of adding or removing privileges, you can click the Cancel button to discard any unsaved changes. Clicking the Cancel button will deactivate the Add, Cancel, and Remove buttons and return you to a read-only list of currently granted analytic privileges.

Package Privileges

The fifth tab in the Assign Privileges window is named Package Privileges. The process for granting or revoking package privileges like the process used for object privileges. The packages and privileges listed in this interface have already been granted to the user. If the list is blank, nothing is currently granted. To grant a package privilege, click the Edit button located on the far-right side of the interface near the Search field. Once you click the Edit button to enable the edit mode, new buttons labeled Cancel, Add Package, Change Privileges, and Remove Package will appear. Click the Add Package button to open the Select Objects popup window. Within this window, you’ll see a search bar and a list of packages. Use the search bar to filter the list based on the entered text. Scroll through the list and highlight the package you want to assign privileges to.

Once you click the desired package, the Add Package with Privileges window will appear. In this window, you’ll see a list of possible package privileges that can be assigned. Select the checkbox beside each privilege you want to grant. In addition, for each privilege, you can use the Yes/No slider under Grantable to Others to allow the privilege to be granted to other users. Click the OK button located at the bottom right side of the interface to continue.

You should now see the selected packages and the privileges you assigned listed in the Package Privileges tab.

To revoke a package privilege, click an assigned package and its assigned privilege from the list and then click the Remove Package button to revoke the privilege. To make the changes active, click the Save button or continue making other changes if necessary. Note that once the Save button is clicked, you’ll be returned to the list of privileges and be required to click the Edit button again to make further changes.

To change assigned package privileges, while in edit mode, click an assigned package and its assigned privilege from the list and then click the Change Privileges button to open a new popup window with the title Change Privileges of concatenated with the name of the package. A list of assigned and unassigned privileges for the package will appear. Select the checkbox to grant the privilege or deselect the checkbox to revoke the privilege. Use the Yes/No slider to manage the grantable to others privilege. Click the OK button at the bottom right of the window to apply your changes. Again, to make the changes active, click the Save button or continue making other changes if necessary until you’re ready to activate all changes.

Privileges on Users

The sixth tab in Assign Privileges window is named Privileges on Users. You’ll use this interface to grant the Attach Debugger privilege to the grantee for the current authenticated user. This GUI is limited to just one action. Clicking the Add button will automatically add the Attach Debugger privilege, and the grantor will give the user account access the registered systems within the SAP HANA cockpit. Click the Save button to grant the privilege or click the Cancel button to discard the change. If the privilege is already granted, click the Remove button to revoke the privilege.

4.3.3    Granting and Revoking Privileges with the SAP HANA Web-Based Development Workbench

The SAP HANA Web-Based Development Workbench security manager, hosted within the XS engine, provides a GUI you can use to create users, create roles, or manage security. You can access the SAP HANA Web-Based Development Workbench via a supported Internet browser. The following two URLs can be customized to match the details of your environment:

In the second example, replace <sap_hana_host> with the host name of the SAP HANA system in your environment and <instance_number> with the two-digit instance corresponding to your SAP HANA system.

For secure access, the following examples should help you construct the correct URL:

Once you have access to the SAP HANA Web-Based Development Workbench, expand the Security node on the right side of the window. Expand Users to reveal a list of user accounts. Click on a user to open the management interface for that user. With the selected User window open, you can begin granting and revoking privileges. Locate the privilege tabs just below the user settings: Granted Roles, System Privileges, Object Privileges, Analytic Privileges, Package Privileges, Application Privileges, and Privileges on Users. Figure 4.12 shows an example of this user interface where you can edit user settings and also assign privileges to a user account.

In the absence of SAP HANA Studio, you can use this GUI to manage security for standard catalog objects and _SYS_REPO-owned catalog objects that were created at design time.

Layout of the User Management Interface Where You Can Configure User Settings and Assign Privileges to a User

Figure 4.12    Layout of the User Management Interface Where You Can Configure User Settings and Assign Privileges to a User

While working in this interface, if any activation issues arise, warning messages will appear in the status pane at the bottom of the User Management window. The status pane has a dark background color, and error messages will appear in red-colored text. Errors are generated only during an attempt to activate or save changes. In the following sections, we’ll review the System Privileges, Object Privileges, Analytic Privileges, Package Privileges, Application Privileges, and Privileges on Users tabs in more detail.

System Privileges

To grant or revoke system privileges on a user account, locate the System Privileges tab. To add a system privilege, click + and to open the Find System Privileges tab. You can use the search box to filter the list based on the entered text. Select one or more privileges form the list and click OK to continue. Your selected system privileges will now be listed in the tab. To assign the Grantable to Other Users and Roles, select the privilege and locate the checkbox to the right in the Details for area to the right of each selected system privilege as needed. Figure 4.13 shows an example of the interface where you can see system privileges listed and the details section where you can configure the Grantable to Other Users and Roles.

Working with System Privileges in the SAP HANA Web-Based Workbench Security Interface

Figure 4.13    Working with System Privileges in the SAP HANA Web-Based Workbench Security Interface

To revoke a system privilege, select the privilege from the list and click the red X. To save your changes, press (Ctrl)+(S). Alternatively, click the save icon (floppy disk) at the top left to save and activate the privilege. Look for activation errors just below privileges tab in the section with a dark colored background. Errors will be listed in red text. White text will indicate that the change was successful.

Object Privileges

The third tab in the user management interface is called Object Privileges. You’ll use this tab to grant or revoke object privileges. To add an object privilege, click + and search for the desired catalog objects. A search dialog will appear, allowing you to search for an object. Unlike the SAP HANA cockpit, the SAP HANA Web-Based Development Workbench also provides additional search options as checkboxes based on the object type, which likely will make it easier to pinpoint specific objects by type. Select the desired objects and click OK. A list of selected objects will appear in the tab, as shown in Figure 4.14. To the right of each object is a list of applicable privileges that can be granted using checkboxes. Checked items will be granted and unchecked boxes will be revoked when you save the changes. The Grantable to Others option is also available using the Yes and No radio buttons.

Working with Object Privileges in the SAP HANA Web-Based Workbench Security Interface

Figure 4.14    Working with Object Privileges in the SAP HANA Web-Based Workbench Security Interface

To revoke all privileges for an object privilege, select the object from the list and click the red X. This will remove the object and revoke all currently granted privileges. To save your changes, press (Ctrl)+(S) or the disk icon to save and activate the privilege. Look for activation errors just below the tab in the section with a dark colored background. Errors will be listed in red text. White text will indicate that the change was successful.

Analytic Privileges

The fourth tab in the user management interface is called Analytic Privileges. To add an analytic privilege to the list, click + and search for the desired analytic privilege. To revoke an analytic privilege, selected the privilege from the list and click the red X to remove the analytic privilege. To save your changes, press (Ctrl)+(S) or click the disk icon to save and activate the privilege.

Package Privileges

The fifth tab in the user management interface is called Package Privileges. To add a package and configure its privileges, click + and search for the desired package hierarchy level. The Find Package search interface window will appear, allowing you to search for and selected one or more package levels. Select the packages you want to secure and click the OK button to return to the previous screen where you can grant the desired package privileges. Figure 4.15 shows an example of the Package Privileges interface where you can assign packages and grant privileges.

Working with Package Privileges in the SAP HANA Web-Based Workbench Security Interface

Figure 4.15    Working with Package Privileges in the SAP HANA Web-Based Workbench Security Interface

To revoke a package privilege, selected the package hierarchy level from the list and choose the red X to remove it. To save your changes, press (Ctrl)+(S) or click the disk icon to save and activate the privilege.

Application Privileges

The sixth tab in the user management interface is called Application Privileges. To add an application privilege to the list, click the plus icon (+) and search for the desired application privilege. To revoke an application privilege, select the privilege from the list and click the red X to remove the application privilege. To save your changes, press (Ctrl)+(S) or click the disk icon to save and activate the privilege.

Privileges on Users

The seventh link in user management interface is named Privileges on Users. You’ll use this interface to grant the attach debugger privilege to the grantee for the current authenticated user. This GUI interface is limited to just one action. Clicking the add icon (green plus sign) will automatically add the logged on user to the list. Then, only the Attach Debugger privilege can be checked or selected. To save your changes, press (Ctrl)+(S) or click the disk icon to save and activate the privilege. If the privilege is already granted, click the revoke icon (the red X) to revoke the privilege by selecting the user in the list.

4.3.4    Effective Privileges System View

Periodically, you may need to query the system to view the privileges that have been granted to a specific user. This database view is included in the SYS schema and is called EFFECTIVE_PRIVILEGES. This view will list all privileges 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.7 shows an example of the SQL code required to query this view.

SELECT 
"USER_NAME", "GRANTEE", "GRANTEE_TYPE", "GRANTOR", "GRANTOR_TYPE", 
"OBJECT_TYPE", "SCHEMA_NAME", "OBJECT_NAME", "COLUMN_NAME", "PRIVILEGE", 
"IS_GRANTABLE", "IS_VALID"
FROM "SYS"."EFFECTIVE_PRIVILEGES" 
WHERE "USER_NAME" = '<USER_NAME>' AND GRANTEE_TYPE = ’USER’;

Listing 4.7    SQL Code Executed against the EFFECTIVE_PRIVILEGES View

The <USER_NAME> variable should be replaced with an active user account name. The view won’t execute unless the WHERE clause contains a filter on USER_NAME. For a complete list of additional filters, refer to the SAP HANA SQL and System View Reference Guide, available at http://s-prs.co/v498204.

The SAP HANA Web-Based Development Workbench provides an easy GUI interface where administrators can manage security for both standard catalog objects and the runtime version of objects created in the SAP HANA XS platform that are owned by the user _SYS_REPO. In the following sections, we’ll discuss how you can grant roles to user accounts.