This blog is going to show you how to "dynamically" grant access to many calculation views in repository roles in HANA in one go.
First off, let me say that this method would not necessarily be the best practice in role or development standards. If one needs to grant access to a calculation view within a HANA role, then the role should be updated in the repository with the view, tested and then moved to production after all approvals have been received. Of course there are business and support requirements where this is just not feasible to up date roles each and every time, be it time, resources or business process requirements...granting access to calculation views in mass sometimes needs to be done.
The following steps will instruct you on how to grant many calculation views to HANA repository roles.
Create a repository role which will hold the privileges for the end users who will need the access in the HANA DB. Within this role you will specify the required schema privileges, sql object privileges, application privileges, etc. For this example I have created a test repository role and specified an example catalog sql object.
2. Create a catalog role and grant the _SYS_REPO select access with grant option. We need to do this as the catalog role is not owned by the _SYS_REPO originally as it wasn't created in the repository. For this example I created a test catalog role and granted the _SYS_REPO access.
Once the above 2 steps are done, navigate to the role you created first off, the repository role. Add the catalog role as an extension to the repository role.
Now we need to create a procedure. We need a procedure as to avoid updating the repository role each time a new calculation view is created. The procedure will dynamically update a catalog/runtime role directly in the system. This runtime/catalog role would be extended out into a repository role as to avoid assigning catalog roles directly to end users.
PROCEDURE "_SYS_BIC"."insert_name" ( )
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
DEFAULT SCHEMA "_SYS_BIC"
AS
BEGIN
DECLARE CURSOR c_views FOR SELECT * FROM VIEWS
WHERE VIEW_NAME LIKE 'package_names%'
AND VIEW_NAME NOT LIKE '%/dp/%'
AND IS_VALID <> 'FALSE'
AND VIEW_TYPE <> 'HIERARCHY';
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
FOR cur_row AS c_views
DO
EXEC 'GRANT SELECT ON "_SYS_BIC"."'||cur_row.VIEW_NAME||'" 'TO CAL_VIEW_TEST';
END FOR;
END;
Lets break down this procedure into 2 parts.
Part 1:
DECLARE CURSOR c_views FOR SELECT * FROM VIEWS
WHERE VIEW_NAME LIKE 'package_names%'
AND VIEW_NAME NOT LIKE '%/dp/%'
AND IS_VALID <> 'FALSE'
AND VIEW_TYPE <> 'HIERARCHY';
This will query all the calculation views within a certain package/name, for example, If you change the value 'package_names' to 'finance.calculationview', it will pull back all the calculation views called "_SYS_BIC"."finance.calculationview". HANA does not support wildcard so this will allow you to specific the naming path.
Part 2:
FOR cur_row AS c_views
DO
EXEC 'GRANT SELECT ON "_SYS_BIC"."'||cur_row.VIEW_NAME||'" TO <runtime_role_name'; This SQL grants the calculation views to the catalog role directly. As the procedure is not a read only procedure and the security is defined as "Definer", the _SYS_REPO will be called to carry out this task, thus alleviating any issues around authorization issues.
Now that the procedure is written, you can now call the procedure which will then update the catalog role, thus any user that has been granted the repository role will now have access to the relevant calc views.