8.5    Granting Object Privileges with Repository Roles

In Chapter 6, we introduced you to repository roles and described the process for managing and creating them. Remember that repository roles can be defined and managed using the role script language within an .hdbrole development artifact. Repository roles can also be defined and managed in the SAP HANA Web-Based Development Workbench editor using either the GUI or text editor. In this section, we’ll dive more deeply into the text editor syntax and show you processes specific to catalog object privileges using repository roles.

8.5.1    Script-Based Repository Roles

In Chapter 6, we outlined the steps and procedures necessary for creating an .hdbrole repository artifact. Within that script, you can define catalog object privileges for both individual catalog objects and for schemas. The script supports referencing both runtime and design-time catalog objects. Runtime objects are objects that only exist in the SAP HANA database. Design-time objects are objects active in the development repository.

The syntax for defining privileges against individual runtime catalog objects is as follows:

catalog sql object <schema>.<object>: <array of privileges> ;

The statement starts with catalog sql object and is case sensitive. This term is followed by a fully qualified catalog object name, including the schema name. After the catalog object, you’ll include a colon, followed by a comma-separated array of privileges identified by name. The statement concludes with a semicolon.

For example, to grant the role access to execute a system stored procedure, use the following syntax:

catalog sql object "SYS"."REPOSITORY_REST": EXECUTE;

To grant privileges to multiple individual catalog objects, simply repeat the syntax for each catalog object. Each line will need to end with a semicolon, which tells the system that the statement is complete. Listing 8.3 shows the syntax to grant privileges for three different catalog objects.

catalog sql object "dev_tables"."MyTable": SELECT, DELETE;
catalog sql object "dev_tables"."MyStoredProc": SELECT, EXECUTE, DROP, ALTER;
catalog sql object "dev_tables"."MyUDFunction": SELECT, EXECUTE, DROP, ALTER;

Listing 8.3    Repository Role Script Granting Privileges to Three Different Catalog Objects

Now, let’s look at the syntax used when privileges need to be assigned to an entire runtime schema, as follows:

catalog schema <schema name>: <array of privileges>;

This syntax is similar to the syntax used to grant individual catalog objects, but you’ll start the statement with catalog schema followed by the name of the schema. For example, to grant multiple privileges for the schema named demo_schema, use the following syntax within the .hdbrole development script:

catalog schema "demo_schema": ALTER, SELECT, EXECUTE, INSERT, UPDATE, DELETE, 
CREATE ANY, DROP, DEBUG, TRIGGER, INDEX;

To grant privileges to multiple runtime catalog schemas, simply repeat the syntax for each schema. Remember to conclude each line with a semicolon, as shown in Listing 8.4.

catalog schema "dev_tables": SELECT, EXECUTE;
catalog schema "ECCDATA": SELECT, EXECUTE;
catalog schema "_SYS_BIC": SELECT, EXECUTE;
catalog schema "SLT_ECC_ECP": SELECT, UPDATE, ALTER, DROP;
catalog schema "ECC76DATA": SELECT;

Listing 8.4    Repository Role Script Granting Privileges to Five Different Schemas

For design-time objects, you’ll use a slightly different syntax. Notice that with runtime objects, you’ll use the phrase catalog sql object and catalog schema before referencing the runtime objects in the SAP HANA catalog. With design time objects, you’ll simply begin with the phrase schema or sql object. In addition, when referencing design-time catalog objects, you’ll need to use their fully qualified design-time name. This fully qualified design-time name is a combination of the package hierarchy, a colon, and then the design-time file name. For example, to grant SELECT and EXECUTE on the design-time schema SalesMart stored in the Security.Schemas package, you would use the following syntax:

schema Security.Schemas:SalesMart.hdbschema: SELECT, EXECUTE;

Notice that the schema was stored in the package Security.Schemas and that the name of the schema file was SalesMart.hdbscema. For the fully qualified name of a schema, you’ll use the naming convention of package name separated by a colon (:) and finally the design-time object name.

For other catalog objects like tables, you’ll use a similar syntax. For example, to grant SELECT and DELETE on the design-time table adusers stored in the package Security.Tables, you would use the following syntax:

sql object Security.Tables::adusers: SELECT, DELETE;

Note that, with table objects, you did not reference their design-time script file extension.hdbtable. While schema objects require the extension .hdbschema, references to design-time catalog objects do not require the repository file extension reference. Notice also that, in the fully qualified name of a design-time table, a double colon (::) separates the package name and the catalog object name. You’ll find that this convention is the norm for all design-time catalog objects except for repository-based schemas.

Be aware that, when these privileges are activated in an .hdbrole script, the grantee will be the system managed user _SYS_REPO. However, before the system will allow these privileges to be activated, the owner of the catalog object must ensure that the _SYS_REPO account has complete access to the objects, including the ability to grant the privileges to other users. This requirement is only applicable if the object itself is a standard catalog object owned by a standard user account. However, if the catalog object is repository-based, that object is already owned by the _SYS_REPO account, so you don’t need to grant any additional privileges to the _SYS_REPO user. As mentioned earlier, this arrangement is one reason we recommend developers create catalog objects via the repository-based development process.

8.5.2    SAP HANA Web-Based Development Workbench GUI

The SAP HANA Web-Based Development Workbench editor, hosted within the XS engine, provides an interface that you can use to build and test development artifacts. From a security perspective, you can use this interface to create and manage repository-based roles. This interface helps you manage repository roles with a GUI or to view and edit their underlying scripting language with the built-in text editor. This flexibility allows security administrators to manage repository roles in a single interface.

You can access the SAP HANA Web-Based Development Workbench editor via a supported Internet browser. The following URLs can be customized to match the details of your environment:

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 SAP HANA Web-Based Development Workbench and define a role, the user account first will need to be granted one of the roles listed in Table 8.7. Users only need one of the two roles to use the SAP HANA Web-Based Development Workbench.

Role Name

Summary

sap.hana.ide.roles::EditorDeveloper

Within the SAP HANA Web-Based Development Workbench, users will only have access to the editor. Within the editor, users can view, edit, create, delete, or activate a repository-based role or other repository-based development artifacts found in development packages or the development repository. A user must have package privileges in addition to this role to properly access and activate the repository role or other development artifacts.

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 can be considered the parent to the sap.hana.ide.roles::EditorDeveloper role.

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

Package Privileges Required for the SAP HANA Web-Based Development Workbench Editor

In addition to the roles listed in Table 8.7, users will also need the appropriate package privileges on the package in which they plan to edit, create, or activate repository roles. For example, they’ll need the following package privileges to create a repository role using the SAP HANA Web-Based Development Workbench editor interface:

The SAP HANA Web-Based Development Workbench editor organizes design-time objects into packages. Figure 8.3 shows the editor; on the left, you’ll see a Content folder, which contains the package hierarchy.

View of the SAP HANA Web-Based Development Workbench Editor and an Example Package Hierarchy

Figure 8.3    View of the SAP HANA Web-Based Development Workbench Editor and an Example Package Hierarchy

As you expand the package hierarchy nodes, you’ll probably see development artifacts, depending on what’s available within your environment. To create a repository role, right-click the package where you want to store the role and choose New • Role. A small window will appear asking for the Role Name. After entering the name, click OK, and a new tab-based window will appear on the right, as shown in Figure 8.4. Click the Object Packages tab to manage object privileges.

The Object Privileges Tab in the SAP HANA Web-Based Development Workbench Editor

Figure 8.4    The Object Privileges Tab in the SAP HANA Web-Based Development Workbench Editor

Select or add a catalog object to manage its privileges by clicking the plus sign (+). Once you click the plus sign, the Select Object Privileges window will appear where you can search for catalog objects within SAP HANA. As demonstrated in Section 8.5.1, within .hdbrole scripting, you can reference either design-time or runtime catalog objects. As shown in Figure 8.5, the first two radio buttons in the Select Object Privileges window provides the options to filter for Design-time or Run-time objects. We highly recommend that you use design-time objects when defining repository roles. The use of design-time objects helps the internal dependency checkers used within the SAP HANA development and SAP HANA application lifecycle management. Select an object and click OK to continue, which will return you to the list of catalog objects and their privileges.

Once an object is selected, you’re now ready to grant object privileges to that objects. As shown earlier in Figure 8.4, to the right of a selected object, you’ll see a frame called Privileges with various object privileges listed. Note that the privileges listed here are specific to the type catalog object selected. To grant these privileges, select the checkbox next to each privilege name. When finished, click the Save All icon to save and activate the repository role.

Repository Role and Select Object Privilege GUI with Various Filtering Options

Figure 8.5    Repository Role and Select Object Privilege GUI with Various Filtering Options

By avoiding the need for SQL or repository role scripts to define a security model with repository roles, users will find this GUI to be an efficient alternative. The GUI is easy to use and alleviates the need for security developers to memorize SQL statements or script syntax.

So far, we’ve reviewed the process for granting catalog object privileges using SQL statements, the SAP HANA cockpit, script-based repository roles, and now GUI-based repository roles. In the next section, we’ll walk you through a case study to provide a real-world example of how to use catalog object privileges within a security model based on repository roles.