6.2    Managing Repository Roles with Design-Time Scripts

In broad terms, as of SAP HANA 2.0 SPS 04, you can create repository roles in two ways: First, you can define repository roles in the SAP HANA Web-Based Development Workbench editor text editor as hand-coded scripts. The scripting language is unique to SAP HANA but is similar to JavaScript Object Notation (JSON) scripting. To avoid hand-coding scripts, the SAP HANA Web-Based Development Workbench editor offers a graphical user interface (GUI) that is similar to the role creation user interface (UI) found in SAP HANA Studio. Most beginners will find using the SAP HANA Web-Based Development Workbench editor GUI much easier because learning a scripting language is not required.

6.2.1    Creating a Repository Package

Before you can create a package, let’s review the information necessary to access the SAP HANA Web-Based Development Workbench editor, a tool where you can create and manage packages. 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 last 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 number corresponding to your SAP HANA system.

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

To use the editor, the user account first will need to be granted one of the roles listed in Table 6.1. Users only need one of these two roles to access the SAP HANA Web-Based Development Workbench editor.

Role Name

Summary

sap.hana.ide.roles::EditorDeveloper

Within the SAP HANA Web-Based Development Workbench, users will only be able to access the editor. The editor provides interfaces to create a variety of repository objects, including repository-based roles.

sap.hana.ide.roles::Developer

This role grants a higher level of privileges for users that need full access to the SAP HANA Web-Based Development Workbench. This role grants access to the editor, catalog, and security interfaces and is considered the parent of the sap.hana.ide.roles::EditorDeveloper role.

Table 6.1    Roles Required to Use the SAP HANA Web-Based Development Workbench Editor

Additional Privileges

Note that the roles in Table 6.1 simply allow the granted user access to the interface. The user will also need access to the package in which the repository roles are stored. To grant repository roles to other users and roles, the user will need EXECUTE access to the system stored procedure named "_SYS_REPO"."GRANT_ACTIVATED_ROLE". To revoke repository roles, the user will need EXECUTE access to the "_SYS_REPO"."REVOKE_ACTIVATED_ROLE" stored procedure.

Once you have access to the SAP HANA Web-Based Development Workbench editor, expand the Content node on the right side of the window. Expand Packages to reveal a list of packages defined in the system.

Before you create a repository role, you must define a unique repository package in which to store repository roles. The package should be structured in such a way that it can be completely isolated using package privileges. Only administrators assigned the responsibility of creating repository roles should have any level of access to the package. We typically recommend that a first-level package named Security be created. Within that package, a subpackage named Roles should be created to host your repository roles. Figure 6.1 shows one way to set up a package hierarchy for security-related objects such as repository roles.

SAP HANA Package Hierarchy for Security-Related Objects

Figure 6.1    SAP HANA Package Hierarchy for Security-Related Objects

We highly recommend using this methodology because only security role creators should access to these objects. If a standard developer were to accidently gain edit access to this package, the developer could update the design-time repository role, activate changes, and elevate its own privileges. Therefore, placing this package at the root level is less risky through preventing the accidental inheritance of package privileges. Ideally, this package will contain the base repository roles used to establish roles and privileges in the SAP HANA system. Now, let’s look at the process used to create a repository role.

6.2.2    Creating Repository Roles within a Package

As mentioned earlier, packages are used to store repository roles. To create a new repository role in the Security.Roles package, shown in Figure 6.1, right-click the Roles package and choose New • File. Name the file ExampleRole.hdbrole, as shown in Figure 6.2. The extension .hdbrole is mandatory, and you should name the role file with the same name you expect to give the role.

Naming Your .hdbrole File

Figure 6.2    Naming Your .hdbrole File

By default, a new tab will appear to the right. The SAP HANA Web-Based Development Workbench will default the object to the repository role GUI editor interface. However, for illustration, we want to review the role file script’s text. To view the text, you’ll need to save the file, close the tab, and reopen the file with the text editor. Once the file is saved, right-click the ExampleRole.hdbrole file and choose Open With • Text Editor. Figure 6.3 shows the right-click menu used to open the Examplerole.hdbrole file with the text editor.

Opening the ExampleRole.hdbrole File with the Text Editor

Figure 6.3    Opening the ExampleRole.hdbrole File with the Text Editor

6.2.3    Defining the Role Name Tag

Because we used the SAP HANA Web-Based Development Workbench editor to create the role, this portion of the syntax was completed during the creation process. However, the syntax of a repository role begins with the word role followed by the fully qualified repository role name. A fully qualified role name is prefixed with the complete package hierarchy name, followed by two colons and the role name. The role name here in this script be exactly the same as the .hdbrole file name prefix. The following script represents the basic syntax of a repository role. Replace the items in angle brackets (<>) with objects unique to your environment:

role <package.subpackage>::<repository role name> {    
}

For example, let’s assume that you need to create a role named ExampleRole that will be stored in the Security.Roles package. The following script would be the starting point for defining that repository role:

role Security.Roles::ExampleRole {    
}

6.2.4    Extending Roles

Between the name of the role and the first opening bracket, you can optionally add existing roles that will extend the new repository role. Thus, the privileges within the referenced roles will be granted to your new repository role, in addition to any privileges you explicitly define after the brackets. Listing 6.1 shows you how to include existing roles by adding extends role, followed by a comma-separated list of existing, fully qualified repository role names.

role Security.Roles::ExampleRole extends role     
sap.hana.xs.formLogin.profile::ProfileOwner,
sap.hana.xs.selfService.user.roles::USSExecutor {
}

Listing 6.1    Repository Role Script with Multiple Extended Roles

6.2.5    Assigning Privileges

Each privilege is granted or assigned to a role using a special syntax. The privileges will be specified within the opening and closing curly brackets ({}). The script shown in Listing 6.2 will grant schema privileges for the StagingMart and SalesMart schemas in addition to granting the CATALOG READ system privilege, an analytic privilege, and a package privilege.

role Security.Roles::ExampleRole extends role   
 sap.hana.xs.formLogin.profile::ProfileOwner,
 sap.hana.xs.selfService.user.roles::USSExecutor {

  -- Begin Schema Privileges  
  catalog schema "StagingMart": SELECT, EXECUTE;
  catalog schema "SalesMart": SELECT, EXECUTE;

  -- Begin System Privileges
  system privilege: CATALOG READ;

  -- Begin Analytic Privileges
  catalog analytic privilege : "_SYS_BI_CP_ALL";

  // Begin Package Privileges
  package .REPO_PACKAGE_ROOT: REPO.READ; 
}

Listing 6.2    Repository Role Script Granting Different Privilege Types

Each privilege line must end with a semicolon. To place comments in the script, start the comment line with two consecutive dashes or slashes (-- or //). Comments are optional, but they should be included to communicate important information about the script as a whole or about each script line. Again, the individual privileges must be confined within the opening and closing curly brackets. As shown in Listing 6.2, you can define different types of privileges within the same script. In most cases, each privilege type will require a slightly different syntax.

6.2.6    Save and Activate

Once the script is complete, you’ll need to save and activate the script. To save the script, click the save icon (floppy disk) or press (Ctrl)+(S). Saving also activates the file in the SAP HANA Web-Based Development Workbench editor. However, if any error arises when saving the file, look for the status pane just below the text editor. The status area has a dark background by default. If there are any errors, look for them with red text. Messages in white text typically indicate a successful save action.

6.2.7    Runtime Repository Roles

When a repository role is activated, a runtime version of the role will be created within the SAP HANA catalog. The name of the runtime role will be constructed using a combination of the package hierarchy and repository role name. Each element will be separated by two colons, as shown in the following examples:

The role will be visible within the standard roles list found in the security manager of SAP HANA Web-Based Development Workbench editor. Now, let’s take a moment to look at how you can define privileges for each object and privilege type.