4    User Accounts

In this chapter, we’ll provide a complete overview of user accounts, including the processes for provisioning, maintaining, and granting privileges and roles to user accounts.

Before a person, application, or service can interact with the SAP HANA system, it must be authenticated. To authenticate with SAP HANA, the person, application, or service must issue credentials. These credentials are typically validated in the form of a user account and a password. The authentication process is designed to uniquely identify the person, application, or service because SAP HANA supports authorizations for each individual user account. Authorization is the process whereby SAP HANA determines the types of actions that the user account can perform, and these authorizations come in the form of granted privileges.

In Chapter 2, we introduced you to the concept behind privileges and explored the different types of privileges available within SAP HANA. Privileges can be granted to user accounts or to roles, and roles can then be granted to one or more user accounts. Inevitably, privileges authorize a user account to perform specific actions within SAP HANA, and so an important relationship exists between privileges and the user accounts that they are assigned to.

In this chapter, we’ll introduce you to the process of creating user accounts, managing user accounts, assigning privileges to user accounts, and assigning roles to user accounts. As you’ll discover, these actions can be performed in multiple ways, such as via SQL statements or using graphical user interfaces (GUIs).

4.1    What Are User Accounts?

A user account is essentially an object in the SAP HANA system that stores a user name, password, and other attributes pertaining to the user account. User names can be defined with any alphanumeric character from A–Z or numbers 0–9. The underscore (_) is also supported. Most special characters, such as periods, colons, equal signs, spaces, commas, and many others are not supported within a user name. By default, passwords must be defined according to system-configured standards for complexity. User accounts also contain other attributes, such as an email address, a Security Assertion Markup Language (SAML) identity, a Kerberos identity, a locale, a statement memory limit, and a time zone. Four different types of user accounts can be created, as we’ll explore in the following sections.

4.1.1    Standard User Accounts

A standard user account is assigned the PUBLIC role by default. When you use GUI interfaces within the SAP HANA Web-Based Development Workbench security GUI, a standard user account is created by default, and when you issue a CREATE USER SQL statement, a standard user account is created. Standard users have minimal access to SAP HANA by default. Each standard user account is provided a user schema. The user will have the CREATE ANY object privilege for its home schema. Any object created within that home schema will be owned by the user, which therefore will have full control of those objects. However, the standard user will also have any of the privileges assigned to the PUBLIC role, which grants access to several individual catalog objects within SYS and other _SYS_* schemas. When queried or executed, most of these catalog objects provide metadata pertaining to other objects within the system. For example, without the PUBLIC role, a user could not access SAP HANA administration tools like SAP HANA Studio or the SAP HANA cockpit.

Standard user accounts are created for a variety of purposes. Three subtypes of standard user accounts can be created, each of which has a different purpose. Let’s look briefly at each:

4.1.2    Technical User Accounts

Technical user accounts are essentially standard user accounts—but these accounts are set up with the express purpose of supporting administrative and development users within the SAP HANA landscape. Recall that, if you delete a user account in SAP HANA, all objects that user account owns, and the all privileges it grants, are also removed. Within your environment, if users are creating catalog objects or standard roles outside the repository, we recommend that these users be given special use accounts that will, by policy and procedure, never be deleted from the system. Such accounts technically can still be deleted from the system, but a special naming convention should be implemented for these accounts to help avoid accidental deletion. For example, you can create the account TECH_JHAUN to facilitate the implementation of a security model or develop catalog objects. As other technical user accounts are needed, you would also be prefix them with TECH_ to identify their importance. This naming convention would signal to other administrative users that these accounts should never be deleted from the system.

A best practice is to use repository objects when developing catalog objects and roles in SAP HANA because those objects are owned by the built-in technical user, _SYS_REPO. This account can technically never be deleted from the system. Thus, the same is true of your security model, which is owned by _SYS_REPO. If you’re using repository-based roles, their privileges will be granted as for the system account _SYS_REPO. You might ask, if we’re following these best practices, then why would we need to create technical user accounts?

Even when following repository-based best practices, technical user accounts might be necessary to support users performing administrative tasks within SAP HANA. Your security model should strive to ensure that all objects and privileges are owned and granted by _SYS_REPO. Specific use cases do exist in which a schema and other catalog objects can’t be created in the development repository. In these cases, you’ll need technical user accounts to preserve any privileges granted to other users or roles.

Some organizations might use the built-in SYSTEM account to perform administrative actions. The SYSTEM account can’t be deleted and therefore can serve as an excellent technical user account. However, when an organization chooses to use this account, some accountability is lost because the ability to audit administrative activity is limited. If multiple individuals are using the same SYSTEM account to perform administrative activities, identifying which individual user performed a specific action could be difficult, perhaps impossible. Therefore, from a security audit standpoint, we don’t recommend using the SYSTEM account in this fashion.

4.1.3    Restricted User Accounts

Unlike standard and technical user accounts, a restricted user account is technically quite different. This type of account doesn’t have CREATE ANY privileges granted for its own schema, and Open Database Connectivity (ODBC)/Java Database Connectivity (JDBC) access is denied by default. These users also are not granted the PUBLIC role by default. They’re ideally intended to support user access to custom applications developed in the SAP HANA extended application services, classic model (SAP HANA XS) engine (XS engine). However, restricted user accounts can be used for the express purpose of providing extremely limited access to an SAP HANA relational database management system (RDBMS). Although not a requirement, the security administrator will need to grant privileges that are typically granted through the PUBLIC role to the restricted users before they can use most functionalities within the RDBMS.

Note that restricted users can’t be converted into standard users, or vice versa, in SAP HANA 1.0. However, in SAP HANA 2.0, you can convert restricted users to standard users, and vice versa. In addition, the three major settings that differentiate a restricted user from a standard user can be configured independently of each other. For example, you can disable the creation of objects in a user’s own schema while also granting them the PUBLIC role and allowing ODBC/JDBC access.

Starting with SAP HANA 2.0, an administrator can now use SQL statements to grant or revoke the PUBLIC role on behalf of the SYS account. To revoke the PUBLIC role and to disable ODBC/JDBC connectivity, use the following SQL syntax:

ALTER USER <USER_NAME> REVOKE ROLE PUBLIC;
ALTER USER <USER_NAME> DISABLE CLIENT CONNECT;

Replace <USER_NAME> with the user account name defined within your system. The first statement revokes the PUBLIC role, and the second statement disables client connectivity using ODBC/JDBC. This step effectively converts a standard user to a restricted user within SAP HANA.

To convert a restricted user to a standard user, use the following SQL syntax:

ALTER USER <USER_NAME> GRANT  ROLE PUBLIC;
ALTER USER <USER_NAME> ENABLE CLIENT CONNECT;

Replace <USER_NAME> with the user account name defined within your system. The first statement grants the PUBLIC role on behalf of the SYS user, and the second statement enables client connectivity using ODBC/JDBC. This step effectively converts a restricted user to a standard user within SAP HANA.

In SAP HANA 1.0, you couldn’t prevent a user from accessing his own schema unless that user was set up as a restricted user. With SAP HANA 2.0, you can now grant or revoke a standard user’s access to his own schema. An admin can use the following SQL syntax to prevent a user from creating objects within his own schema:

ALTER USER <USER_NAME> REVOKE CREATE ANY ON OWN SCHEMA;

Replace <USER_NAME> with the user account name defined within your system. To allow a user to create objects within his own user schema, use the following syntax:

ALTER USER <USER_NAME> GRANT  CREATE ANY ON OWN SCHEMA;

The SAP HANA cockpit GUI also provides radio buttons for you to quickly enable or disable the Creation of Objects in Own Schema, the assignment of the PUBLIC Role, or Disable ODBC/JDBC Access. Figure 4.1 shows the new user creation screen where you can configure the desired settings for a user.

Managing Restricted User Settings in the SAP HANA Cockpit

Figure 4.1    Managing Restricted User Settings in the SAP HANA Cockpit

Without the PUBLIC role, access for a restricted user is limited to such a degree that these users can’t use the SQL console, the SAP HANA Web-Based Development Workbench, or SAP HANA administration tools like SAP HANA Studio or the SAP HANA cockpit until additional privileges or roles are granted to the account. For example, to give a restricted user basic SQL console access to query a table, you must perform the following steps:

  1. Enable the user property ODBC/JDBC Access. This step can be completed in SAP HANA cockpit user management GUI or by issuing the following SQL statement: ALTER USER <USER> ENABLE CLIENT CONNECT.

  2. Grant the RESTRICTED_USER_ODBC_ACCESS role.

  3. Grant the RESTRICTED_USER_JDBC_ACCESS role.

  4. Grant SELECT on the catalog objects containing the data.

Restricted users can be created in two ways: You can issue a special SQL CREATE statement, or you can use the SAP HANA cockpit user management GUI. To create a restricted user using a SQL statement, use the following syntax:

CREATE RESTRICTED USER <USER> PASSWORD <PASSWORD>

Replace <USER> with a desired user name and replace <PASSWORD> with the desired initial password. Alternately, you can use the SAP HANA cockpit. To create a restricted user via the GUI, connect to a system using the SAP HANA cockpit and then locate the Manage Users interface in the cockpit System Overview dashboard. On the left side of the user management interface, locate the plus sign (+) and select Create Restricted User. Figure 4.2 shows an example of the GUI and various options described earlier.

Creating a Restricted User via the SAP HANA Cockpit

Figure 4.2    Creating a Restricted User via the SAP HANA Cockpit

A pane will appear on the right of the interface where you can specify the user account information.

In the following sections, we’ll provide more details about the process of creating standard, restricted, and technical user accounts. We’ll also review options involving SQL statements and the SAP HANA cockpit.

4.1.4    LDAP User Accounts

Starting with SAP HANA 2.0 SPS 03, users based on the Lightweight Directory Access Protocol (LDAP) can now be provisioned within SAP HANA, and their LDAP-managed passwords can be used for authentication. When an SAP HANA user is set up for LDAP authentication, standard local password authentication is disabled. As a result, a user can only authenticate via one of the supported single sign-on (SSO) providers or using their LDAP credentials. For example, if your Microsoft Active Directory account is configured as an LDAP account in SAP HANA, you can access SAP HANA Studio using your Active Directory-managed password. You wouldn’t be able to set a locally managed SAP HANA password for that same account.

Before the creation of LDAP users, SAP HANA must also be preconfigured with an LDAP provider, and SAP HANA database roles must be mapped to LDAP groups. Note that repository-based roles cannot be mapped to LDAP groups. Based on the configuration of the LDAP provider, LDAP users can be automatically created in SAP HANA. In addition, you can use the CREATE USER <USER_NAME> WITH IDENTITY FOR LDAP PROVIDER SQL statement to create an LDAP user manually. The SAP HANA cockpit also provides the option, when creating a user, of choosing an authentication password mode, either Local or LDAP. Note that user accounts can be changed from local to LDAP password authentication within the SAP HANA cockpit. The same change can be achieved with SQL by first running ALTER USER <USER_NAME> DISABLE PASSWORD then ALTER USER <USER_NAME> ENABLE LDAP.

We’ll discuss the LDAP setup process in more detail in Chapter 12. To validate that a user can authenticate with LDAP, execute the following SQL to verify that the user is properly mapped and that the LDAP provider is properly configured:

VALIDATE LDAP PROVIDER ldap_ecorp CHECK USER JONATHHAU99 PASSWORD "NeverG0ing2Get!t";

If a user does not already exist in SAP HANA, you can validate that the LDAP provider would automatically create a missing user using the following example SQL for the user JIMGTO88:

VALIDATE LDAP PROVIDER ldap_edcorp CHECK USER CREATION FOR LDAP USER JIMGOT88;