Sap Hana Privileges 4r26c

SAP HANA PRIVILEGES By Sudip Kumar Das

SAP HANA Privileges When a accesses the SAP HANA database using a client interface (for example, ODBC, JDBC), his or her ability to perform database operations on database objects is determined by the privileges that he or she has been granted.

SAP HANA Privileges System privilege

Object privilege Analytic privilege Package privilege Granted Roles

SAP HANA Privileges Authorization Check All the privileges granted directly or indirectly (through roles) to a are combined. This means that whenever a tries to access an object, the system performs an authorization check on the , the 's roles, and directly granted privileges.

SAP HANA Privileges Object Privileges • •

•

Object privileges are SQL privileges that are used to allow access to and modification of database objects. For each SQL statement type (for example, SELECT, UPDATE, or CALL), a corresponding object privilege exists. If a wants to execute a particular statement on a database object (for example, table, view, or stored procedure), he or she 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 who has object privileges for a schema automatically has the same privileges for all objects currently in the schema and any objects created there in the future. Initially, the owner of an object and the owner of the schema in which the object is located are the only s who can access the object and grant object privileges on it to other s.

SAP HANA Privileges Object Privileges An object can be accessed only by the following s: •

The owner of the object

•

The owner of the schema in which the object is located

•

s to whom the owner of the object has granted privileges

•

s to whom the owner of the parent schema has granted privileges

SAP HANA Privileges Analytic Privileges •

SQL privileges implement coarse-grained authorization at object level only. s either have access to an object, such as a table, view or procedure, or they do not. 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 are used in the SAP HANA database to provide such fine-grained control of which data individual s can see within the same view.

SAP HANA Privileges Analytic Privileges Example •

Sales data for all regions are contained within one analytic view. However, regional sales managers should only see the data for their region. In this case, an analytic privilege could be modeled so that they can all query the view, but only the data that each is authorized to see is returned.

SAP HANA Privileges Analytic Privileges •

All column views modeled and activated in the SAP HANA modeler automatically enforce an authorization check based on analytic privileges. Column views created using SQL must be explicitly ed for such a check (by ing the parameter VIEWFORAPCHECK).

•

Analytic privileges do not apply to database tables or views modeled on row-store tables. Access to database tables and row views is controlled entirely by SQL object privileges.

SAP HANA Privileges Analytic Privileges •

To create analytic privileges, the system privilege CREATE STRUCTURED PRIVILEGE is required.

•

To drop analytic privileges, the system privilege STRUCTUREDPRIVILEGE is required.

•

As repository objects, analytic privileges are owned by the _SYS_REPO . To be able to grant and revoke an analytic privilege, a needs the privilege EXECUTE on the procedures GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE and REVOKE_ACTIVATED_ANALYTICAL_PRIVILEGE respectively.

SAP HANA Privileges Package Privileges •

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

•

Native packages are packages that were created in the current system and should therefore be edited in the current system. Imported packages from another system should not be edited, except by newly imported updates. An imported package should only be manually edited in exceptional cases.

SAP HANA Privileges Native Package Privileges •

Developers should be granted the following privileges for native packages: REPO.READ - This privilege authorizes read access to packages and design-time objects, including both native and imported objects. REPO.EDIT_NATIVE_OBJECTS - This privilege authorizes all kinds of inactive changes to design-time objects in native packages. REPO.ACTIVATE_NATIVE_OBJECTS - This privilege authorizes the to activate or reactivate design-time objects in native packages. REPO.MAINTAIN_NATIVE_PACKAGES - This privilege authorizes the to update or delete native packages, or create sub-packages of native packages.

SAP HANA Privileges Import Package Privileges Developers should only be granted the following privileges for imported packages in exceptional cases: •

REPO.EDIT_IMPORTED_OBJECTS - This privilege authorizes all kinds of inactive changes to design-time objects in imported packages.

•

REPO.ACTIVATE_IMPORTED_OBJECTS - This privilege authorizes the to activate or reactivate design-time objects in imported packages.

•

REPO.MAINTAIN_IMPORTED_PACKAGES - This privilege authorizes the to update or delete imported packages, or create sub-packages of imported packages.

SAP HANA Privileges System Privileges

•

Developers require the following system privileges to be able to work in the repository: REPO.EXPORT - This privilege authorizes the to export, for example, delivery units.

•

REPO.IMPORT - This privilege authorizes the to import transport archives.

•

REPO.MAINTAIN_DELIVERY_UNITS - This privilege authorizes the to maintain delivery units (DU, DU vendor and system vendor must be the same).

•

REPO.WORK_IN_FOREIGN_WORKSPACE - This privilege authorizes the to work in a foreign inactive workspace.

SAP HANA Privileges Roles •

A role is a collection of privileges that can be granted to either a or another role in runtime. A role typically contains the privileges required for a particular function or task.

•

Privileges can be granted directly to s of the SAP HANA database. However, roles are the standard mechanism of granting privileges as they allow you to implement complex, reusable authorization concepts that can be modeled on business roles. Several standard roles are delivered with the SAP HANA database (for example, MODELING, MONITORING). We can use these as templates for creating your own roles.

•

A role can also extend other roles.

SAP HANA Privileges Procedure for Grant & Revoke Previlege

SAP HANA Privileges Query to view Privilege

•

•

Query this view to see which privileges have been granted directly to a : SELECT * FROM "PUBLIC"."GRANTED_PRIVILEGES" where GRANTEE = '<> ‘ Query this view to see which roles have been granted directly to a : SELECT * FROM "PUBLIC"."GRANTED_ROLES" where GRANTEE = ' < role_name> ' > >