Types of Privileges in SAP HANA

There are different types of privileges in authorization concept. You should have good understanding of each type of privilege to grant users the right privilege.

System Privileges

These are used for administrative tasks, they are assigned to users and roles.

SQL Privileges

These are used to restrict the access and modification of database objects such as tables and views.

Depending on the object type and action type (CREATE, SELECT, ALTER, DROP etc.) authorization can be created for each object.

SQL privileges are assigned to users and roles.

Analytic Privilege

There should be restricted access to information models (attribute views, analytic views and calculation views). The read operation on these views should be restricted by filtering some attribute values.

These privileges are only applied when user runs a query on these views.

These type of privileges must be created and activated before they can be assigned to any user or role.

Package Privilege

These are used to restrict access to the use of packages in SAP HANA repository.

Package contains attribute views, analytic views, calculation views and functions as well as analytic privileges.

To be able to work with a package, the respective package privilege must be granted.

SQL Privileges

There are privileges available to control the authorization of SQL commands.

There are two types of SQL privileges:

System Privilege

These are system wide privileges that control some general system activities mainly related to administration such as creating schema, creating and changing users and roles.

Object Privilege

These privileges are bound to an object e.g. a database table. These enable object specific control activities for example: SELECT, DELETE to be performed.

CREATE ANY

With this privilege, a user can create all kinds of objects like tables, views, synonyms, SQL script functions or database procedures in a schema. This privilege can only be granted on a schema.

ALL PRIVILEGES

This type of privilege can be granted on an object like table or view but not on a schema. It is a collection of DDL and DML privileges.

DROP AND ALTER

These provide users the privilege for DROP and ALTER SQL commands. DROP is valid for all types of objects. ALTER is not valid for synonym and sequences as their definition cannot be changed after creation.

SELECT, INSERT, UPDATE AND DELETE

These give DML privileges to user on objects. SELECT is valid on all kinds of objects except functions and procedures. INSERT, UPDATE, DELETE are valid only for schemas, tables, table types and table views.

INDEX

This allows creation, alteration and removal of indexes for an object using CREATE INDEX, ALTER INDEX and DROP INDEX commands. This privilege can only be applied to a schema, table and table type.

EXECUTE

This allows execution of SQL script function or a database procedure using CALLS or CALL command respectively.

System Privileges Types

Users and Roles

USER ADMIN

This privilege authorizes the creation and changing of users using the CREATE USER, ALTER USER and DROP USER SQL commands.

ROLE ADMIN

This privilege authorizes the creation and deletion of roles using CREATE ROLE and DROP ROLE commands. It also authorizes the granting and revocation of roles using the GRANT and REVOKE SQL commands.

Catalog and Schema management

CREATE SCHEMA

This privilege authorizes the creation of database schema using the CREATE SCHEMA SQL command.

DATA ADMIN

This privilege authorizes users to have read only access to full content of all system and monitoring views as well as to execute all DDL (and only DDL) commands in HANA database. The content of the views is filtered based on the user privilege.

CATALOG READ

This privilege authorizes all users to have unfiltered read only access to the full content of all system and monitoring views. For installation and upgrade the ROOT user is used. Do not use the Root user
for day-to-day activities.

System Management

These privileges authorize various types of system activities using ALTER SYSTEM SQL commands. Because of high impact of this privilege, these are not designed for normal user. Caution must be taken when granting this privilege (for example, only grant them to support user or role)

Data Import and Export

IMPORT privilege allows user to use SQL commands IMPORT or LOAD TABLE. The user also needs INSERT privilege on the target tables that are imported.

EXPORT privilege authorizes the export activity in the database via the EXPORT or LOAD TABLE SQL commands. Note that, besides this privilege, user also needs SELECT privilege on source tables to be exported.

Analytic Privileges

Analytic privileges provide row level access to data from information views. Thus different users can use the information views and they may see different data from the same view.

For example, sales representative is only allowed to see sales data from his/her region.

An analytical privilege contains several restrictions.

• cube restriction determines which column views the privilege is used
• activity restriction determines effected action e.g. READ
• validity restriction determines at what time periods the privilege is valid

In addition to above three restrictions, there are dimensional restrictions. These are applied to actual attributes of a view. For example year filter value ‘2008’.

Only dimension attribute values can be employed in these restrictions. It is not possible to restrict based on key figure or measure values.

Analytic Privileges are repository objects. They can be created and managed via SAP HANA studio. You can create in any package and they may not be in same package as views.

Generally, a user has access to an individual, independent view (Attribute, Analytic or Calculation) if the following properties are met:

• The user was granted SELECT privilege on the view or the containing schema
• The user was granted an Analytic privilege that is applicable to the view. This happens when analytic privilege contains the view in the cube restriction and contains at least one filter on one attribute of this view.

No select privilege on the underlying base tables or views of this view is required.

Implement row level security with analytic privilege.

Restrict access to a given data container to selected attribute views

– Field from Attribute View
– Field from Attribute View used in Analytic View
– Private Dimension of Analytic View
– Attribute field in Calculation View
– Combinations of the above
– Single value, range, IN-list

Right click on any package, and select new analytic privilege. Give name and description and select type as “Classic”

Add views in the analytic privilege, click finish.

Select applicable Information Models
– Views have two functions in privilege
– Views you want to grant access to.
– Views from which you want to select fields for restrictions.
– You can add further views to the privilege later.

The SAP HANA database repository is structured hierarchically with packages assigned to other packages as subpackages.
If you grant privileges to a user for a package, the user is automatically also authorized for all corresponding subpackages.

Appendix

Note: If a user having system privilege CATALOG READ is also the owner of the table, they can also move the table without SQL privilege ALTER