When a user accesses the SAP HANA database using a client interface (for example, ODBC, JDBC, or HTTP), their ability to perform database operations on database objects is determined by the privileges that they have been granted.
Object privileges allow access to and modification of database objects, such as tables and views. Depending on the object type, different actions can be authorized (for example, SELECT, CREATE ANY, ALTER, DROP, and so on).
For each SQL statement type (for example, SELECT, UPDATE, or CALL), a corresponding object privilege exists. If a user wants to execute a particular statement on a simple database object (for example, a table), they must have the corresponding object privilege for either the actual object itself, or the schema in which the object is located. This is because the schema is an object type that contains other objects. A user who has object privileges for a schema automatically has the same privileges for all objects currently in the schema and for any objects created there in the future.
You can grant object privileges for database catalog objects such as tables, views, and procedures. You can also grant object privileges for non-catalog objects such as development objects in the repository of the SAP HANA database.
Object Privilege Activities
Object privilege activities also include the following:
This privilege allows the creation of all kinds of objects, in particular, tables, views, sequences, synonyms, SQL script functions, or database procedures in a schema. You can only grant this privilege on a schema.
This is a collection of all Data Definition Language (DDL) and Data Manipulation Language (DML) privileges that the grantor currently has and is allowed to grant, and that can be granted on this particular object. This collection is evaluated dynamically for the given grantor and object. ALL PRIVILEGES is applicable to a schema, a table, a view, or a table type.
DROP and ALTER
These are DDL privileges and they authorize the DROP and ALTER SQL commands. While the DROP privilege is valid for various objects, the ALTER privilege is not valid for sequences and synonyms. This is because their definitions cannot be changed after creation.
SELECT, INSERT, UPDATE, and DELETE
These are DML privileges and they authorize the respective SQL commands. SELECT is valid for all kinds of objects, except for functions and procedures. However, INSERT, UPDATE, and DELETE are only valid for schemas, tables, table types, and table views.
This special DDL privilege authorizes the creation, alteration, or revocation of indexes for an object using the CREATE INDEX, ALTER INDEX, and DROP INDEX commands. This privilege can only be applied to a schema, table, and table type.
This special DML privilege authorizes the execution of an SQL script function or a database procedure using the CALLS or CALL command, respectively.
For more information about the object privileges available in SAP HANA and for which objects they are relevant, see the SAP HANA Security Guide - Object Privileges.
System privileges control general system activities. They are mainly used for administrative purposes, such as creating schemas, creating and changing users and roles, performing data backups, managing licenses, and so on.
System privileges are mainly used to authorize users to perform administrative actions, including the following:
Creating and deleting schemas
Managing users and roles
Performing data backups
Monitoring and tracing
System privileges are also used to authorize basic repository operations. This includes the following examples:
Importing and exporting content
Maintaining Delivery Units (DUs)
Types of System Privilege
The following list gives an example of system privileges that are available on the SAP HANA Database:
- Users and Roles, which include the following:
This privilege authorizes the creation and changing of users with the CREATE USER, ALTER USER, and DROP USER SQL commands.
This privilege authorizes the creation and deletion of roles with the CREATE ROLE and DROP ROLE SQL commands. It also authorizes the granting and cancellation of roles with the GRANT and REVOKE SQL commands.
- Catalog and Schema Management, which include the following:
This privilege authorizes the creation of database schemas with the CREATE SCHEMA SQL command.
This privilege authorizes all users to have unfiltered read-only access to the full content of all system and monitoring views. It also authorizes users to execute all DDL – and only DDL – commands in the SAP HANA database. The content of those views is filtered based on the privileges of the user.
This privilege authorizes all users to have unfiltered read-only access to the full content of all system and monitoring views. The content of those views is filtered based on the privileges of the accessing user.
These privileges authorize the various system activities that can be performed using the ALTER SYSTEM SQL commands. Because of the high level of impact on the system, these privileges are not designed for a normal database user. Proceed with caution when granting these privileges (for example, only grant them to a support user or role.)
Data Import and Export
The following system privileges are available for the authorization of the data import and export in the database:
This privilege authorizes the import activity in the database using the IMPORT or LOAD TABLE SQL commands. Note that, besides this privilege, the user needs the INSERT privilege on the target tables to be imported.
This privilege authorizes the export activity in the database via the EXPORT or LOAD TABLE SQL commands. Note that, besides this privilege, the user needs the SELECT privilege on the source tables to be exported.
For more information about the system privileges, see the SAP HANA Security Guide - System Privileges
Package privileges authorize actions on individual packages in the classic SAP HANA repository. The privileges can grant you read access to the repository, or give you full access (read/write) to the classic SAP HANA repository.
The SAP HANA database repository is structured hierarchically with packages assigned to other packages as subpackages.
Packages contain design-time versions of various objects, such as calculation views, as well as analytic privileges and functions. To work with packages, the respective package privileges must be granted.
Privileges granted on a repository package are assigned implicitly to the design-time objects in the package, as well as to all subpackages. Users are only allowed to maintain objects in a repository package if they have the necessary privileges for the package in which they want to perform an operation, for example to read or write to an object in that package. To perform operations in all packages, a user must have privileges on the root package REPO_PACKAGE_ROOT.
For a requested operation in a specific package, the authorization check is repeated on the parent package and recursively up the package hierarchy to the root level of the repository. If the user does not have the necessary privileges for any of the packages in the hierarchy chain, the authorization check fails, and the user is not permitted to perform the requested operation.
In the context of repository package authorizations, there is a distinction between native packages and imported packages.
If you grant privileges to a user for a package, the user is also authorized automatically for all corresponding subpackages.
For more information about the package privileges, see the SAP HANA Security Guide - Package Privileges
Analytic privileges allow read access to data in SAP HANA information models such as calculation views depending on certain values or combinations of values. Analytic privileges are evaluated during query processing.
Analytic privileges grant different users access to different portions of data in the same view based on their business role. Within the definition of an analytic privilege, the conditions that control which data users see is either contained in an XML document or is defined using SQL.
Standard object privileges (SELECT, ALTER, DROP, and so on) implement coarse-grained authorization at object level only. Users either have access to an object, such as a table, view or procedure, or they don't. While this is often sufficient, there are cases when access to data in an object depends on certain values or combinations of values. Analytic privileges in the SAP HANA database provide fine-grained control at row level of the type of data that individual users can see within the same view.
All calculation views that have been designed in the modeler and have been activated from the modeler of the SAP Web IDE for SAP HANA are supported automatically by the analytic privilege mechanism.
If you are already familiar with the authorization model of SAP Business Warehouse (SAP BW), you can see that there are many similarities between the two models.
The overall idea behind analytic privileges is the reuse of analytic views by different users. However, the different users may not be allowed to see the same data. For example, different regional sales managers, who are only allowed to see sales data for their regions, can reuse the same analytic view. They obtain the analytic privilege to see only data for their region, and their queries on the same view return the corresponding data. This is a major difference compared with the SAP BW model. While the concept is very similar, if you execute a query that returns values that you are not authorized to see, SAP BW forwards an error message. With the SAP HANA database, the query is executed and, corresponding to your authorization, only the values that you are entitled to see are returned.
For more information about the analytic privileges, see the SAP HANA Security Guide - Analytic Privileges
Developers of applications for SAP HANA extended application services can create application privileges to authorize user and client access to their application.
These privileges authorize user and client access to the application. For example, they authorize users and clients to start the application or to perform administrative actions in the application.
You can grant and revoke application privileges through the GRANT_APPLICATION_PRIVILEGE procedures and through the REVOKE_APPLICATION_PRIVILEGE procedure in the _SYS_REPO schema.
You can grant application privileges to users or roles in runtime. However, we recommend that you grant application privileges to roles created in the repository.
For more information about the application privileges, see the SAP HANA Security Guide - Application Privileges
Privileges on Users
Privileges on users are SQL privileges that users can grant to other users. ATTACH DEBUGGER is the only privilege that can be granted on a user.
For example, User A can grant User B the privilege ATTACH DEBUGGER. This allows User B to debug SQLScript code in the session of User A. User A is the only user who can grant this privilege.
User B also needs the object privilege DEBUG on the relevant SQLScript procedure.
You cannot grant the ATTACH DEBUGGER privilege on behalf of other users.