3    Catalog Objects

In this chapter, we’ll explore the different types of catalog objects and walk through the two major options available for creating them. How you choose to create catalog objects has a profound effect on the security model structure and management processes.

Object privileges play an important role in the development of any SAP HANA security model. They define the types of SQL script actions a user can perform on a catalog object (also sometimes referred to as a database object). SAP HANA hosts multiple types of catalog objects. In this chapter, we’ll explore the different types of catalog objects that SAP HANA hosts. We’ll also explain different ways to create most catalog objects: Catalog objects can be created directly using SQL CREATE statements, in which case they’re called native catalog objects. The user issuing the SQL statement is the owner of the native catalog object. SAP HANA also supports the creation of some catalog objects as repository development artifacts, in which case they’re called repository catalog objects. Repository catalog objects are owned by the _SYS_REPO system account.

Because the principle of object ownership is a critical concept in all security models, the distinction between the ownership of native catalog objects and the ownership of repository catalog objects is important. Therefore, in this chapter, we’ll take a detailed look at the different options for creating catalog objects. The options your organization chooses when creating catalog objects is of critical importance to all security administrators. We’ll define different types of catalog objects, demonstrate how to create both native and repository catalog objects, review the process for deploying repository objects, and conclude with a case study to demonstrate real-world applications.

3.1    What Are SAP HANA Catalog Objects?

Catalog objects consist of various relational database management system (RDBMS) artifacts, such as tables, views, stored procedures, triggers, sequences, and a few other items. Items such as tables both store and define the structure of the data. Other items, such as views, are used to express query logic. The following list describes the most common catalog objects available in SAP HANA:

• Schemas
  A schema object is a logical organizational unit that is a parent to all other catalog objects. When other catalog objects are created, a schema must be specified. From the perspective of a security model, the schema is the most important catalog object. Whether to secure the entire schema or to secure the individual catalog objects within the schema is a critical choice you’ll have to make.

• Tables
  Tables define the structure of your data and store your data within an RDBMS. Their structure is defined by columns, rows, and data types, and rows of data are logically stored in this table structure. Because the data contained in tables might be sensitive, your organization must limit the types of SQL statements that can be executed on tables.

• Triggers
  Triggers are catalog objects that contain code that executes a procedure depending on a variety of events that occur within the RDBMS. They are used for a variety of purposes, ranging from database management to data integrity. Because their code can manipulate data and other catalog objects, your organization will need to restrict where and how triggers are used.

• Views
  Views act as logical tables. However, in SAP HANA, views don’t store data. Instead, they are defined using SQL query statements. Like tables, views offer columns and data types and can be queried. Within their definitions, SQL query statements can perform actions such as combining tables, restricting data, and aggregating results. From a security standpoint, most catalog views must be secured for the same reasons that you need to secure access to tables.

• Synonyms
  Synonyms are logical reference points to other catalog objects. They act as aliases to obscure the identity of other catalog objects. Synonyms exclusively use the granted privileges of the objects they reference.

• Sequences
  Sequences are catalog objects for keeping track of an incremented number value by means of its definition. They can be queried in procedural code to determine the current or next value within a defined sequence. They often are used to create unique identity columns within a table. For example, you can use a sequence to generate a new value in a primary key column, with the requirement that the value be unique for the row.

• Procedures
  A stored procedure is a catalog object that contains complex developed SQL code. Stored procedures can return a dataset, much like a catalog view. However, the code can also perform tasks within the RDBMS system. These procedures support both input and output parameters, which makes them dynamic.

• Indexes
  An index is an object used to optimize the retrieval of data from a table. Although indexes enhance the performance of some queries, they come at the cost of increased storage. Security administrators typically limit privileges to create and delete/drop indexes because of the impact indexes can have on both performance and storage costs.

• Functions
  Functions are custom snippets of SQL code used to perform read-only operations on data. They can be defined as single-valued input and output or table-valued input and output. Once created, functions can be referenced within SQL SELECT statements and SQL stored procedures. Depending on the purpose of a catalog function, regulating access to these objects can be important. For example, if a function applies core business logic within a SQL query, security administrators likely will need to limit the number of individuals that can alter the function.

Now that you are familiar with the common catalog objects used in an SAP HANA system, let’s look at the options for creating them. For this discussion, we’ll demonstrate the processes necessary to create a schema and the processes necessary to create a table. We’ll highlight the differences between these options based on instances where we plan to use SQL statements and instances where we plan to use repository objects. How you create objects is important to your security model, so pay close attention to these options, focusing on how they affect an object’s ownership.