Create multiple database users in Datasphere spaces

[albertosimeoni]

Hello Experts,

I need to create multiple database users in space management (database user related to a space).

The request is to have a user mapping from a Front end BI Tool so I can use datasphere DAC to manage row securities.

The editor lets you create one user at time,

And the bad part is that passwords are auto-generated, most of the time with characters that are not accepted by the front end tool.
I need to create around 350 users.

Does some sort of SAML / SSO exists for database users?
As far as I know Datasphere's database is a "self-managed" hana cloud so we can not create users from there and assign correct roles to see space data.

Do you have any hint?

Thanks,

Alberto

[JulianJuraske · Accepted solution]

Hello,

don't know if the CLI is working for Database Users, but that's the only possible way to create mass users by file. I would at least try it before creating 350 by hand 😉 

https://help.sap.com/docs/SAP_DATASPHERE/d0ecd6f297ac40249072a44df0549c1a/72dc33a8f41944f78318138bc1...

Maybe you also create one by hand for a Space and then try read all the users from the Space, and hopefully you have a blueprint what params you have to Input for your Database User.

But the longer I read the docu for managing Users with CLI the more I think this is only meant for "real" Users and not DB-Users, but you can try it !

Regards
Julian

[albertosimeoni · Accepted solution]

Hello,

I think your hint is correct,

I found in the documentation the format to add database users.

{
	...
	"dbusers":{
		"<Space_ID>#<DB_UserName>":{
		  "ingestion":{
			"auditing":{
			  "dppRead":{
				"retentionPeriod":<days>
				"isAuditPolicyActive":false
			  },
			  "dppChange":{
				"retentionPeriod":<days>
				"isAuditPolicyActive":false
			  }
			}
		  },
		  "consumption":{
			"consumptionWithGrant":false,
			"spaceSchemaAccess":false,
			"scriptServerAccess":false,
			"localSchemaAccess":false,
			"hdiGrantorForCupsAccess":false
		  }
		}
	  }
	}

https://help.sap.com/docs/SAP_DATASPHERE/d0ecd6f297ac40249072a44df0549c1a/3fcbf619f2774b849fa7df5816...

I am a bit worried of resetting and retrieving passwords (probably this requires a little scripting on the terminal).

But it seems feasible.

Thank you

[JulianJuraske · Accepted solution]

As a Consultant I would highly recommend to question the Demand before ;).
350 User ? This doesn't sound like technical Users.
Is the Company you are working for realling creating named DB User for each frontend User?

If you are using another Frontend tool then SAC, wouldn't you rather create 1 DB User for e.g. each Business Department so they can share the login?
Of course this changes if you need to use Data Access Controll ( than you again need 1 DB User for each Frontend User)

[albertosimeoni · Accepted solution]

as Datasphere is not compatible With Calculation Views,

[albertosimeoni · Accepted solution]

@JulianJuraske Hello, as Datasphere is not compatible With Calculation Views, we need to migrate around 400 calculation views, so we need DSP + HDI container. today the customer has rows security implemented in hana modeling on prem through analytic privileges. so the mapping is done between Front end user and database user. Now since datasphere has a managed hana cloud database (we do not have admin rights), the only way to create database users is through spaces (we left apart database analysis user and groups as they have a cap of 10% of the workload). So there are 2 way: one as above, the second is asking improvements to SAP influence.

[JulianJuraske · Accepted solution]

Now I'm even more concerned 😉 
In your Case the Calculation View is the Endresult that you want to provide to the Frontend Tool ( I assume FIORI/UI5 from your desription )? 

The DB User are for OUTBOUND, so from DSP -> SAC (or other BI Tool) or are you using them for this scenario:
https://help.sap.com/docs/SAP_DATASPHERE/be5967d099974c69b77f4549425ca4c0/1aec7ca95af24208a61c1a444b...
But in that Case I don't get why on earth you would need 350 Users ;), shouldn't one be enough? 
And than you implement the DAC in DSP? 

[albertosimeoni · Accepted solution]

Hello,

[albertosimeoni · Accepted solution]

Front end tool is not SAC!!! SAC do not uses database user. the front end tool is Business object, row securities can not be managed in DAC as first they have incompatibilities respect to AP (no OR condition logic if multiple DAC are assigned => loose time to reinvent the weel), second the DAC works if you read a Datasphere view protected by DAC, but I can not do so as many calculation views have multiple value input parameters that are not supported in Datasphere Views (only single value IP). So the choice is Migrate hana content to BAS + HDI container, Use Datasphere as platform to extract data and future integration options. The customer today has only Datasphere, not BAS and container space. the other way whould be remake row secutiries in Business Objects (not optimized execution).

[JulianJuraske · Accepted solution]

BO Support stops after 2025, intresting that there are still projects delivering data to it, especially when the Customer has already Datasphere in User. (I would assume he will/has to also use SAC in Future and this Products work very good together) 

The DAC in DSP had an update like 2 Months ago, you are able work with Patterns now, have a look here 
https://community.sap.com/t5/technology-blogs-by-sap/introducing-new-type-of-data-access-control-quo...
I agree with you, before that update it was kinda nonsense to use it.

In an Analytic Model you are now able to work with Input Variables (Multiple Values), however if you wanna push it down to an IP within a SQL- or GraphicalView, I think you are currently limited to a Single Value.
But....have a look at the roadmap:
https://roadmaps.sap.com/board?PRODUCT=73555000100800002141&range=CURRENT-LAST#;INNO=ABC1893F1B121EE...

[albertosimeoni · Accepted solution]

In a perfect world BI is used for analytics. in the real world BI is 90% used for reporting ("beautiful" old flat tables to export, a thing that has nothing to do with SAC as it is a dashboarding BI tool), and companies have limited budget to spend to IT projects. So the greater value for a specific customer is? spend millions to move every report to SAC (and convince the stakeholders of the project to change the way they use information?) or what's is most valuable for their use case?

[albertosimeoni · Accepted solution]

I'm aware of the roadmap. Sorry but the roadmap you point talk about IP from the sources that can be used in Datasphere view, not the Multiple value parameter in Datasphere view => its very likely it is limited to single value IP.