HANA XSA External access to Classic Schema - Roles

[david_alzate13]

Hi Everyone,

I have been working on an XSA application, and the application is reading tables from external schemas. The way how I achieved this was by creating a runtime role and assigning the authorization to the different schemas.

However, the requirement grew and we I would like to reuse existing calculation views (CVs) that are stored in the classic packages. The problem I have encountered is that some of theses CVs have analytic privileges (APs) and these APs are assigned to an existing design time role. So, I tried to give access to the CUPS user with this design time role but when I ran my .hdbgrants it failed (Code below).

Error: Error executing: GRANT "APPLICATION::EXTERNAL_ACCESS","CLASSIC_SCHEMA::DESIGN_TIME_ROLE" TO "XSA_SCHEMA_APP::access_role"; (nested message: insufficient privilege: Cannot grant activated roles: line 1 col 38 (at pos 37))grantor service: "ServiceName_1", type: "sql", user: "CUPS_USER" file name: cfg/MY_GRANTS.hdbgrants

My two questions are, if it's possible to grant a design time role to my CUPS_USER and that can be included in my .hdbgrants. The second query is if it's possible to grant an APs by using run time SQL commands, or It will not be possible to assign APs and the recommendation to read CVs from XSA schema will be by removing all SQL APs from the Classic calculations views?

Last but not least, when I removed the APs from the CVs everything worked fine.

Thanks beforehand to the community

[Cocquerel]

It looks like CUPS_USER does not have the privilege to grant the role "CLASSIC_SCHEMA::DESIGN_TIME_ROLE" to others.

I would suggest to create a procedure on XS Classic, set it as SECURITY DEFINER, and run the following statement in it : GRANT "CLASSIC_SCHEMA::DESIGN_TIME_ROLE" TO CUPS_USER WITH ADMIN OPTION;

[david_alzate13]

Hi Michael,

I tested that before creating this query. It was the only way to add the role to the CUPS user with the option to grant to others.

However, it is like unrecognized by the compiler, because when I tried to compile the hdbgrants file the error remains.

Do you have any alternative idea?

Thanks,

David

[Cocquerel]

Could you share your hdbgrants file ?

[david_alzate13]

Hi Michael,

Here is the current .hdbgrants file:

{
	"ServiceName_1": {
		"object_owner": {
			"schema_privileges": [{
				"reference": "_SYS_BIC",
				"privileges_with_grant_option": ["SELECT", "EXECUTE"]
			}, {
				"reference": "SAP",
				"privileges_with_grant_option": ["SELECT"]
			}],
			"roles":["CLASSIC_SCHEMA::DESIGN_TIME_ROLE"]<br>		},
		"application_user": {
			"schema_privileges": [{
				"reference": "_SYS_BIC",
				"privileges_with_grant_option": ["SELECT", "EXECUTE"]
			}, {
				"reference": "SAP",
				"privileges": ["SELECT"]
			}],
			"roles":["CLASSIC_SCHEMA::DESIGN_TIME_ROLE"]<br>		}
	}
}

[Cocquerel]

the syntax looks correct. Very strange that is not working. Do you confirm CLASSIC_SCHEMA is the package name where the repository role is ? If yes, the alternative I see would be that you change the granting type of your user provided service to use a custom procedure. (see https://help.sap.com/docs/HANA_CLOUD_DATABASE/b9902c314aef4afb8f7a29bf8c5b37b3/f7381ccf4bc644f286ac7... )
Inside your procedure logic, it would use GRANT_ACTIVATED_ROLE procedure to grant the repository role (see https://help.sap.com/docs/SAP_HANA_COCKPIT/afa922439b204e9caf22c78b6b69e4f2/c719b2e7d9761014b9d79877... )