Why should we create Roles/Analytic Privileges as Repository (Design time) objects?
SAP HANA is a SQL-compliant database and the way the objects are secured is very similar to the other databases. However, SAP HANA comes with many extra features that are not standard in most databases. One of such capabilities is designing roles/privileges as Design Time objects.
In the early days of SAP HANA, there used to be only Run-time version. However, the capability of creating Design-time version was added at a later stage. The ultimate goal is to achieve an easy-to-administer yet still secure SAP HANA system.
Few advantages of creating Roles/Analytic Privileges as design-time objects are:
- When the design-time Role/Analytic Privilege is activated, the run-time version will be created automatically with owner as _SYS_REPO.
- Granting them to the other users would be easy withe the GRANT_ACTIVATED_ROLE and GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE SQL objects.
Once the Role/Analytic Privilege is activated, use the below SQL statements:
CALL “_SYS_REPO”.“GRANT_ACTIVATED_ROLE”(‘Role name’,’User Name’);
CALL GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE (‘analytic privilege ‘,’user/role’);
NOTE: Authorization to both the objects should be provided to the user for him/her to assign the authorization to others.
catalog sql object “PUBLIC”.”GRANT_ACTIVATED_ROLE”: EXECUTE;
catalog sql object “PUBLIC”.”REVOKE_ACTIVATED_ROLE”: EXECUTE;