3.3    Creating and Managing Repository Catalog Objects

For many RDBMS administrators, the process of issuing CREATE statements is a well-known methodology for generating database catalog objects. However, SAP HANA offers an alternative to this traditional approach since SAP HANA is also a development environment that allows organizations to define and store both design-time and runtime versions of objects. Design-time objects are stored in the SAP HANA repository, whereas runtime objects exist within the SAP HANA RDBMS catalog.

For example, you can define a development artifact called a repository table within the development repository found in SAP HANA Web-Based Development Workbench editor. Once defined and activated, a catalog table is created in a specified schema. Repository tables function exactly like tables created with a SQL CREATE statement. However, a repository table is owned by the _SYS_REPO system account, not by the developer that activates it.

The _SYS_REPO system account is the main system account responsible for managing, generating, owning, and activating repository-based objects, including repository schemas, tables, roles, views, and procedures. Leveraging the use of repository objects greatly reduces maintenance within a security model because _SYS_REPO is already the owner of these objects and many other objects within the system. You no longer need to log on as the traditional object owner and grant rights to other users and roles for repository objects. Most developers have a hard time remembering that they need to grant privileges to security administrators each time they create a schema or sometimes objects within a schema. By creating repository objects, developers are decoupled from this responsibility because their repository objects are activated as catalog objects owned by _SYS_REPO. This arrangement simplifies the process and reduces the communication required between SAP HANA developers and security administrators.

_SYS_REPO is also responsible for managing the activation of SAP HANA information views. These multidimensional views reference catalog tables and can’t be activated unless the _SYS_REPO account has been granted SELECT, EXECUTE, and WITH GRANT OPTION/Grantable to others for the object. Again, if you choose to create repository tables or schemas, the _SYS_REPO account will already have the required privileges.

In Chapter 6, we’ll discuss this concept further. For now, note that the simplification of the security model is only fully realized when you use both repository catalog objects and repository roles within your environment. Both objects are owned and executed by the _SYS_REPO account.

As mentioned earlier, the designation of ownership is vital within the security model. Repository objects are owned by the _SYS_REPO system account. Therefore, you must pay close attention to the methods developers use when creating catalog objects. To further your understanding, let’s look at a few examples of the processes for creating both repository schemas and repository tables.

3.3.1    Creating Repository Schemas

You can create repository schemas in the SAP HANA Web-Based Development Workbench editor. The SAP HANA extended application services, classic model (SAP HANA XS) system offers web-based GUIs to help you create and modify repository-based objects.

If you have the appropriate privileges to access the SAP HANA Web-Based Development Workbench editor, log on to the web interface using the URLs mentioned in Section 3.2.1. Once logged in, you can create a repository schema.

Within the SAP HANA Web-Based Development Workbench editor, you can create a repository schema anywhere within the package hierarchy. However, the process for creating a schema is not apparent within the GUI or right-click menus. Fortunately, you can directly create the required .hdbschema file using the editor.

To create the file, right-click the desired package and choose New • File. The name of the file must match exactly the name of the new schema followed by the extension “.hdbschema.” Figure 3.2 shows an example of the GUI window that will appear when naming the file. Enter the name of the file followed by the file extension “.hdbschema.” Again, the name of the file must also be the name of the schema.

Creating a Repository Schema in the SAP HANA Web-Based Development Workbench Editor

Figure 3.2    Creating a Repository Schema in the SAP HANA Web-Based Development Workbench Editor

Click the Create button to continue. An empty editor tab on the right side of the window. Within this editor, you’ll need to define the schema using the specific “.hdbschema” syntax. The syntax for a schema is the following:

schema_name = “MYSCHEMA”;

Figure 3.3 shows an example of how the editor window and syntax should look. Once you’ve completed the syntax, click the save icon in the icon bar on the left of the editor tab. Once saved, a new _SYS_REPO-owned schema will be created at runtime. You’ll then grant users access to the schema via a repository-based role, before the schema can be visible to them in the SAP HANA database explorer.

Syntax of the .hdbschema File

Figure 3.3    Syntax of the .hdbschema File

Once activated, users with the CREATE ANY privilege for the schema can create standard catalog objects within the schema using CREATE statements. However, we recommended that developers use the repository instead to create these objects. The repository schema will be owned by the _SYS_REPO user, with no need for the developer to be granted the CREATE ANY privilege on the schema. Developers will only need access to activate objects within the SAP HANA repository; the _SYS_REPO user account creates the relevant objects on behalf of the developer. Once again, you can simplify the security model in this way because developers won’t need any CREATE ANY privileges for a schema to create a catalog object in that schema.

Another important security aspect of repository schemas is that the _SYS_REPO account will own the schema. Thus, the _SYS_REPO account will have full privileges for all objects created in the schema, regardless of the method used to create those objects. Again, we recommend that developers use the repository to create objects. However, if for some reason they don’t, then the _SYS_REPO account will already have the privileges necessary to establish repository roles that reference objects within the repository schema.

3.3.2    Creating Repository Tables

Repository tables can be created in the SAP HANA Web-Based Development Workbench editor. The SAP HANA XS system offers web-based GUIs to help you create and modify repository-based objects.

If you have the appropriate privileges to access the SAP HANA Web-Based Development Workbench editor, you can log on to the interface using the URLs mentioned in Section 3.2.1. Once logged in, you can create a repository table.

Within the SAP HANA Web-Based Development Workbench editor, you can create a repository table using core data services (CDS) anywhere within the package hierarchy. CDS is an SAP HANA-specific syntax that can create a variety of catalog objects at design time. In the case of the .hdbdd file, you can define multiple tables within a single file. However, the process for creating CDS-based tables is not apparent within the GUI or right-click menus. Fortunately, you can directly create the required .hdbdd file using the editor.

To create the file, right-click the desired package and choose New • File. The name of the file is important because the name will become part of the runtime object’s name and context within the CDS script. Enter the name followed by “.hdbdd.” Figure 3.4 shows an example of the GUI window that will appear when naming the file.

Creating Repository Tables in the SAP HANA Web-Based Development Workbench Editor

Figure 3.4    Creating Repository Tables in the SAP HANA Web-Based Development Workbench Editor

Click the Create button to continue. An empty editor tab will appear on the right side of the window. Within this editor, you’ll need to define the CDS entity using the specific “.hdbdd” syntax. The syntax for a CDS table is considerably more complex than the syntax for a schema. Within the editor, start by defining the namespace. The namespace must match the namespace of the repository package where the .hdbdd file is stored. For example, the first line of the file would contain the following text if the .hdbdd file is to be stored in the dev.datamart package:

namespace dev.datamart;

Within the editor, you must also include a line referencing the name of the schema where the CDS tables will be stored. The syntax for this line is as follows:

@Schema: ’MYSCHEMA’

This statement assumes that we’ll store the table in the schema named MYSCHEMA. Note that this line does not end in a semicolon. The next few lines are as follows:

context tables {
 @Catalog.tableType : #COLUMN
  ENTITY MYTABLENAME {

The text entered after the word context must match the name of the .hdbdd file that was created. In our example, we created a column store table, and the metatag @Catalog.tableType: #COLUMN was added. After the word ENTITY, the table definition starts with its name followed by an opening bracket. After the bracket, you would start defining the columns within the table. You would also complete the ENTITY with a closing bracket and semicolon. You’ll then complete the context with a closing bracket and semicolon as well.

Figure 3.5 shows an example of the editor window and how the syntax should be entered. Once you’ve completed the syntax, click the save icon in the icon bar to the left of the editor tab. Once saved, new _SYS_REPO-owned tables will be created within the referenced schema at runtime. You’ll need to grant users access to the schema, or to individual tables, via a repository-based role before these objects will be visible to them in the SAP HANA database explorer.

Syntax for Defining an .hdbdd CDS-Based Table

Figure 3.5    Syntax for Defining an .hdbdd CDS-Based Table

Note that the design-time object name and catalog object name will be different from the .hdbdd file or its context. The catalog object name will contain the name you specified for the ENTITIY object and will be prefixed with the package hierarchy name (or namespace) and the context (or .hdbdd file extension). For example, using the example shown earlier in Figure 3.4, the runtime object name would be “MYSCHEMA”.”dev.datamart::tables.MYTABLE”. You would query this object using the following SQL statement:

SELECT * FROM "MYSCHEMA"."dev.datamart::tables.MYTABLE"

Because we used lowercase and camel case for the package name and context name, respectively, we must set the schema and catalog table name between double quotes in the SQL statement because SAP HANA is case-sensitive by default. Without the double quotes, the system would look for the object name while assuming you intended to use uppercase on all objects and thus would fail to find the objects because they were created with lowercase or camel case.

SAP HANA Repository Catalog Objects

For more information on the process of creating and managing repository tables and other repository-based objects, refer to the SAP HANA Developer Guide, available at http://help.sap.com/hana_platform.

Again, repository tables are owned by the _SYS_REPO system account, which will greatly simplify the setup of your security model. This arrangement will also help you promote a security model and its referenced repository objects when you moving into another SAP HANA environment. For example, you can define a security model in an SAP HANA development environment, within which you would create a delivery unit containing a design-time repository CDS table, schema, and role. The delivery unit, containing a security model, can be exported to a file and later imported into a production environment. This process is only possible because you defined catalog objects as repository objects. In the next section, we’ll take a closer look at how you can deploy repository objects using a delivery unit.