SAP HANA Admin - User Provisioning
SAP HANA user and role management configuration depends on the architecture of your HANA system. If SAP HANA is integrated with BI platform tools and acts as a reporting database, then the end user and the role are managed in the application server.
If the end user directly connects to SAP HANA database, then the user and the role in the database layer of HANA system is required for both end users and administrators.
Every user who wants to work with HANA database must have a database user with necessary privileges. User accessing HANA system can either be a technical user or an end user depending on the access requirement. After successful logon to the system, the user’s authorization to perform the required operation is verified. Executing that operation depends on the privileges that the user has been granted. These privileges can be granted using roles in HANA Security. HANA Studio is one of the powerful tools to manage the user and the roles for HANA database system.
User Types
User types vary according to the security policies and different privileges assigned to the user profile. User type can be a technical database user or an end user. The user needs access to HANA system for reporting the purpose or for data manipulation.
Standard Users
Standard users are the users who can create objects in their own Schemas and have Read access in the system Information models. Read access is provided by PUBLIC role, which is assigned to every standard user.
Restricted Users
Restricted users are those users who access HANA system with some applications and they don’t have SQL privileges on HANA system. When these users are created they don’t have any access initially.
If we compare restricted users with Standard users −
Restricted users can’t create objects in HANA database or their own Schemas.
They don’t have access to view any data in the database as they don’t have generic Public role added to profile like standard users.
They can connect to HANA database only using HTTP/HTTPS.
HANA Users Administration and Role Management
Technical database users are used only for administrative purpose such as creating new objects in the database, assigning privileges to other users, on packages, applications, etc.
SAP HANA User Administration Activities
Depending on the business needs and the configuration of HANA system, there are different user activities that can be performed using user administration tools such as HANA studio.
Most common activities include −
- Create users
- Grant roles to users
- Define and create roles
- Delete users
- Reset user passwords
- Reactivate users after too many failed logon attempts
- Deactivate users when it is required
Create Users in HANA Studio
Only database users with the system privilege ROLE ADMIN are allowed to create users and roles in HANA Studio. To create users and roles in HANA Studio, go to HANA Administrator Console. You will see the security tab in System view.
When you expand the security tab, it gives an option of User and Roles. To create a new user, right-click on the User and go to New User. A new window will open where you define User and User parameters.
Enter the user name (mandate) and in the Authentication field enter the password. Password is applied while saving password for a new user. You can also choose to create a restricted user.
The specified role name must not be identical to the name of an existing user or role. The password rules include a minimal password length and a definition of which character types (lower, upper, digit, special characters) have to be part of the password.
Different authorization methods can be configured such as SAML, X509 certificates, SAP Logon ticket, etc. Users in the database can be authenticated by varying mechanisms −
Internal authentication mechanism using a password.
External mechanisms such as Kerberos, SAML, SAP Logon Ticket, SAP Assertion Ticket or X.509.
A user can be authenticated by more than one mechanism at a time. However only one password and one principal name for Kerberos can be valid at any one time. One authentication mechanism has to be specified to allow the user to connect and work with the database instance.
It also gives an option to define the validity of the user. You can mention the validity interval by selecting the dates. Validity specification is an optional user parameter.
There are some users that are by default delivered with the SAP HANA database: SYS, SYSTEM, _SYS_REPO, _SYS_STATISTICS.
Once this is done, next is to define the privileges for the user profile.
Types of Privileges to User Profile
There are different types of privileges that can be added to the user profile.
Granted Role
This is used to add inbuilt sap.hana roles to the user profile or to add custom roles created under Roles tab. Custom roles allow you to define roles as per access requirement and you can add these roles directly to the user profile. This removes the need to remember and add objects to a user profile every time for different access types.
Public Role
This is a generic role and is assigned to all database users by default. This role contains read-only access to system views and execute privileges for some procedures. These roles cannot be revoked.
Modeling
It contains all privileges required for using the information modeler in the SAP HANA studio.
System Privileges
There are different types of System privileges that can be added to a user profile. To add system privileges to a user profile, click on the (+) sign.
System privileges are used for Backup/Restore, User Administration, Instance start and stop, etc.
Content Admin
It contains the similar privileges as that in MODELING role, but with the addition that this role is allowed to grant these privileges to other users. It also contains the repository privileges to work with imported objects.
Data Admin
This is another type of privilege that is required for adding Data from the objects to the user profile.
Following are some common supported System Privileges −
ATTACH DEBUGGER − Authorizes the debugging of a procedure call, called by a different user. Additionally, the DEBUG privilege for the corresponding procedure is needed.
AUDIT ADMIN − Controls the execution of the following auditing-related commands: CREATE AUDIT POLICY, DROP AUDIT POLICY and ALTER AUDIT POLICY and the changes of the auditing configuration. Also allows access to AUDIT_LOG system view.
AUDIT OPERATOR − Authorizes the execution of the following command: ALTER SYSTEM CLEAR AUDIT LOG. Also allows access to AUDIT_LOG system view.
BACKUP ADMIN − Authorizes BACKUP and RECOVERY commands for defining and initiating backup and recovery procedures.
BACKUP OPERATOR − Authorizes the BACKUP command to initiate a backup process.
CATALOG READ − Authorizes the users to have unfiltered read-only access to all system views. Normally, the content of these views is filtered based on the privileges of the accessing user.
CREATE SCHEMA − Authorizes the creation of database schemas using the CREATE SCHEMA command. By default, each user owns one schema. With this privilege, the user is allowed to create additional schemas.
CREATE STRUCTURED PRIVILEGE − Authorizes the creation of Structured Privileges (Analytical Privileges). Only the owner of an Analytical Privilege can further grant or revoke that privilege to other users or roles.
CREDENTIAL ADMIN − Authorizes the credential commands: CREATE/ALTER/DROP CREDENTIAL.
DATA ADMIN − Authorizes reading all data in the system views. It also enables the execution of any Data Definition Language (DDL) commands in the SAP HANA database. A user having this privilege cannot select or change data stored tables for which they do not have access privileges, but they can drop tables or modify table definitions.
DATABASE ADMIN − Authorizes all commands related to databases in a multi-database, such as CREATE, DROP, ALTER, RENAME, BACKUP, RECOVERY.
EXPORT − Authorizes export activity in the database via the EXPORT TABLE command. Note that besides this privilege, the user requires the SELECT privilege on the source tables to be exported.
IMPORT − Authorizes the import activity in the database using the IMPORT commands. Note that besides this privilege, the user requires the INSERT privilege on the target tables to be imported.
INIFILE ADMIN − Authorizes changing of system settings.
LICENSE ADMIN − Authorizes the SET SYSTEM LICENSE command to install a new license.
LOG ADMIN − Authorizes the ALTER SYSTEM LOGGING [ON|OFF] commands to enable or disable the log flush mechanism.
MONITOR ADMIN − Authorizes the ALTER SYSTEM commands for EVENTs.
OPTIMIZER ADMIN − Authorizes the ALTER SYSTEM commands concerning SQL PLAN CACHE and ALTER SYSTEM UPDATE STATISTICS commands, which influence the behavior of the query optimizer.
RESOURCE ADMIN − Authorizes commands concerning system resources. For example, ALTER SYSTEM RECLAIM DATAVOLUME and ALTER SYSTEM RESET MONITORING VIEW. It also authorizes many of the commands available in the Management Console.
ROLE ADMIN − Authorizes the creation and deletion of roles using the CREATE ROLE and DROP ROLE commands. It also authorizes the granting and revocation of roles using the GRANT and REVOKE commands.
Activated roles, meaning roles whose creator is the pre-defined user _SYS_REPO, can neither be granted to other roles or users nor dropped directly. Users having ROLE ADMIN privilege are also not able to do so. Please check documentation concerning activated objects.
SAVEPOINT ADMIN − Authorizes the execution of a savepoint process using the ALTER SYSTEM SAVEPOINT command.
Components of the SAP HANA database can create new system privileges. These privileges use the component-name as the first identifier of the system privilege and the componentprivilege-name as the second identifier.
Object/SQL Privileges
Object privileges are also known as SQL privileges. These privileges are used to allow access to objects like Select, Insert, Update and Delete of tables, Views, or Schemas.
Following are the types of Object Privileges −
Object privilege on database objects that exist only in runtime.
Object privilege on activated objects created in the repository, such as calculation views.
Object privilege on schema containing activated objects created in the repository.
Object/SQL Privileges are collection of all DDL and DML privileges on database objects.
Following are some commonly supported Object Privileges −
There are multiple database objects in HANA database, so not all the privileges are applicable to all kinds of database objects.
Object Privileges and their applicability on database objects.
Analytic Privileges in User Profile
Sometime it is required that data in the same view shouldn’t be accessible to other users who don’t have any relevant requirement for that data.
Analytic privileges are used to limit the access on HANA Information Views at the object level. We can apply row and column level security in Analytic Privileges.
Analytic Privileges are used for −
- Allocation of row and column level security for specific value range
- Allocation of row and column level security for modeling views
Package Privileges
In the SAP HANA repository, you can set package authorizations for a specific user or for a role. Package privileges are used to allow access to data models - Analytic or Calculation views or on to Repository objects. All privileges that are assigned to a repository package are assigned to all sub packages too. You can also mention if the assigned user authorizations can be passed to other users.
Steps to add package privileges to User profile −
Step 1 − Click Package privilege tab in HANA studio under User creation → Choose (+) sign to add one or more packages. Use Ctrl key to select multiple packages.
Step 2 − In the Select Repository Package dialog, use all or part of the package name to locate the repository package that you want to authorize access to.
Step 3 − Select one or more repository packages that you want to authorize access to, the selected packages appear in the Package Privileges tab.
Following grant privileges are used on repository packages to authorize the user to modify the objects −
If you choose ‘Yes’ for this, this allows assigned user authorization to pass to the other users.
Application Privileges
Application privileges in a user profile used to define authorization for access to HANA XS application. This can be assigned to an individual user or to a group of users. Application privileges can also be used to provide different level of access to the same application such as to provide advanced functions for database administrators and read-only access to normal users.
To define Application specific privileges in a user profile or to add a group of users, following privileges should be used −
- Application-privileges file (.xsprivileges)
- Application-access file (.xsaccess)
- Role-definition file (<RoleName>.hdbrole)
