Alternative for _SYS_BI_CP_ALL Analytical Privilege in HANA cloud?
In HANA XS classic, to get access on all analytical privileges(classic AP) in the system _SYS_BI_CP_ALL can be granted to an user(directly or via a role), generally for developers/ HANA Admin. But in HANA cloud or XS advanced using HDI container there is no such analytical privilege exists for developers.
Even though Developer has got the access on all HDI container schema objects like tables and CVs, still get authorization error while accessing CVs that have analytical privileges as there is no CP_ALL. So what is the alternative solution?
The diagram below explains it. In this example, using XS classic Developer got _SYS_BI_CP_ALL AP, so full access on all analytical privileges is applied.
In the second part, using HANA cloud HDI container owns all the CVs instead of _SYS_BIC schema and there is no _SYS_BI_CP_ALL AP. So we are going to create a new AP(AP_ALL) and add all the CVs that have APs. Also ,in definition of Developer AP, there will be no restrictions defined on any of the CVs used. that’s it, simple!.
then Dev role can be created on this AP and granted to Developers/Admin to get full access on all APs. There wouldn’t be any change in business user roles, it is same as before to the most part.
Note: When a new CV is created, SQL analytic Privilege is selected by default in the semantics. So if we don’t want analytical Privilege assigned for a CV then uncheck the option and activate the CV. In other case, an analytical privilege must be defined that includes the CV.
Defined analytical Privileges can be found in the System View STRUCTURED_PRIVILEGES in SYS schema.
Thanks for sharing. What is the approach in case we want to assign every new CV to the developer role automatically?
Hi Konrad, if you grant access on container schema to a DB user, you get access access on all schema objects including CVs(Provided CV doesn't enabled for SQL AP). But, if any CV that has Apply privilege property with value 'SQL Analytical Privileges' must be manually added to an analytical privilege and that AP to a role. I couldn't find an automatic way to grant access on all CVs that have AP applied.