User Management in SAP HANA
In this lesson, we will learn:
- User and role concept, creation and management
- Grant/Revoke/Assign privilege to users
The users of SAP HANA database require their own credentials to log on. The administrator has to set up user id and roles for each user in the system.
Why security is required in SAP HANA?
- Database administration should be restricted to skilled employees only
- ECC tables should be restricted access
- SAP HANA information models can only be edited by who created them
- Object access as well as access to data should be controlled in SAP HANA
Relationship between User, Role, Object and Privilege
- A user can logon to database, a user can be owner of database objects
- A role is a collection of privileges granter to user or to another role (nesting)
- A privilege is used to impose restrictions on some objects or certain operations done by users
Privileges can be assigned to user directly or indirectly using roles. Privilege is required to design access control. Roles can be used to structure the access control scheme and design reusable business roles.
It is recommended by SAP to manage authorization for users using roles. Roles can be nested so that role hierarchies can be implemented.
Several predefined roles exist in the database. Some of them are templates that need to be customized; others can be used as they are.
User management is configured using SAP HANA studio.
You can create users, roles, assign users to roles and check user authentication in HANA studio.
You can also use SQL commands. This is useful when using scripts for automated processing.
CREATE USER <user name> PASSWORD <password>
CREATE ROLE <role_name>
It is often necessary to specify different security for different types of users. A user can be user, technical user, system user or application specific technical user.
User types
Users
Named users represent real persons and are daily working in SAP HANA database. These are created by administrator.
SYSTEM user
This user is the built in overall system administrator
Technical Users
SYS, _SYS_STATISTICS and _SYS_REPO
These are internal users within the SAP HANA database and cannot log from outside.
_SYS_STATISTICS user is used by statistics server and _SYS_REPO user is used by the repository.
Application specific technical users
An application server may log in to HANA database using dedicated technical user.
User Provisioning
Privileges for Client Interface
When accessing the SAP HANA database using a client interface (such as ODBC, JDBC, MDX), any access to data must be backed by corresponding privileges.
Privileges for SQL statement
For each SQL statement type (for example, SELECT, UPDATE, and CALL), a corresponding privilege exists that the executing user needs to have.
Object Privileges
Objects in the database (such as tables, views, or stored procedures) have an owner who can access the objects and grant privileges for them.
This authorization functions on the object level, for example, a table or a view. No user, besides the owner of an object and users that the owner has provided with a privilege, can access this particular object.
Analytic Privileges
Analytic Privileges are used to provide row-level authorization on certain kinds of database objects, such as Analytic Views.
You can give command in SQL editor to create a role:
CREATE ROLE <role name>
For creating users, choose relevant authentication methods, define the initial password and other user settings.
Note: You should also define the default client for a user as it is used as an implicit filter when reading from SAP HANA studio data models.
Assign roles to users
Use statement GRANT <role name> TO <user>
To remove access, use statement REVOKE <role_name> FROM <user>
For installing, upgrading, operating the HANA database, the following users are necessary:
Database users
SYSTEM is a database user which is created by default when to install SAP HANA database.
It has all system privileges such as ability to create other database users, access system tables and so on.
Note: For security reasons, SAP recommends not to use SYSTEM user for day to day activities. Users created by SYSTEM should be used instead.
Several internal database users are also created while installation such as SYS and _SYS_STATISTICS. These users cannot log into SAP HANA database.
Operating System User
In addition to user SYSTEM, installation also creates an external operating system user (SIDadm).
This user is also known as operating system administrator. It owns all the SAP HANA files, and operating system processes related to them. The operating system user credentials are required for starting/stopping of database processes or starting recovery process.
The operating system user is not a SAP HANA database user.
For installation and upgrade the ROOT user is used. Do not use the Root user for day-to-day activities.
