Day12-Day13

Database • A database role is a collection of privileges that can be Roles granted to either a database user or another role in runtime. • A role typically contains the privileges required for a particular function or task, for example:  Business end users reading reports using client tools such as Microsoft Excel  Modelers creating models and reports  Database administrators operating and maintaining the database and its users • Roles in the SAP HANA database can exist as runtime objects only (catalog roles), or as design-time objects that become catalog objects on deployment (database artifact with file suffix .hdbrole).

ROLE A role can contain any number of the following privileges: STRUCTURE • System privileges for general system authorization, in particular administration activities • Object privileges (for example, SELECT, INSERT, UPDATE) on database objects (for example, schemas, tables, views, procedures, and sequences) • Analytic privileges on SAP HANA information models • Package privileges on repository packages (for example, REPO.READ, REPO.EDIT_NATIVE_OBJECTS, REP O.ACTIVATE_NATIVE_OBJECTS) • Application privileges for enabling access to SAP HANA- based applications developed in an SAP HANA XS classic environment

Repository-Roles In an SAP HANA XS classic environment, role developers create database roles as design-time objects in the built- in repository of the SAP HANA database using either the SAP HANA Web Workbench or the SAP HANA studio. Points to be noted Description Authorization does a user need to • User has the privilege him – or herself grant privilege on role • User is authorized to grant privilege to others ( WITH GRANT OPTION) • _SYS_REPO is owner of all activated repository objects therefore when we create role with object privilege access then that privilege access should also given to _SYS_REPO explicitly. WITH GRANT OPTION • It only possible through the SQL statements, if we are doing it through roles then WITH GRANT option is not possible. GRANT & REVOKED Repository Only possible with procedures roles • GRANT_ACTIVATED_ROLE • REVOKE_ACTIVATED_ROLE Repository roles dropped Not possible with statement DROP role, we need to delete the role from design time and then activate the change. Audit: Repository Roles Yes possible with action: ACTIVATE REPOSITORY CONTENT

Privileges required explicitly to _SYS_REPO while deploying the design time role Privilege Action Necessary to Grant in Repository Role System privilege None Package privilege None Analytic privilege None Application privilege None SQL object on activated object (for example, attribute view, analytic None view) SQL object privilege on runtime object (for example, replicated table) Grant privilege to user _SYS_REPO with WITH GRANT OPTION NOTE: If we make any changes to the repository role in runtime then on activate of the same role in design time will overwrite the changes done on runtime.

Role Domain-Specific Language Syntax Role <Package_name>::<Role_name>

Create a • Perquisites Design Time  Package for storing roles. Role  We must have access to object privilege, system privilege and others for creating and activating the objects in repository.  We should have granted privileges on any catalog-only objects that we plan to grant in the new role to the technical user _SYS_REPO. • The design-time definition of a role is specified in a text file with the extension .hdbrole • In the SAP HANA studio, you create and define a role in a role- specific text editor using the role domain-specific language (DSL) 

CREATE ROLE In container field, Select package File -> New -> Other -> SAP HANA ->  Database Development Role

Role is now ready to define We can extend both catalog roles & repository roles Granting All privileges is not possible but we can grant every privilege individually

Object privileges on design-time objects, that is tables, views, procedures, and sequences Object privileges on catalog objects, we must qualify the object privilege with schema name, design time catalog objects access will remove if they are reactivated again. Always grant privilege on design time objects

Privilege on Design time schemas Privilege on Catalog Schemas Package Privileges

Analytical Privileges Catalog Analytical Privileges Application Privilege

Reference Link • https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.05/en-US/1547c14105be409ebfc3a9e9 634a7188.html • https://blogs.sap.com/2019/04/07/grc-12.0-integration-with-hana-db-for-user-access-provisioning/