SAP Datasphere Automation : Creating Database user in SAP Datasphere using Datasphere CLI & Python

Introduction

One way to access datasphere hana database is via database users. Each database user is linked to one space (except database analysis user). We can create database user vai GUI however, its a long process and prone to errors. In this blog, i will share my work on how i automated the process using SAP Datasphere CLI and Python without the need to login into datasphere GUI.

Old Process:

If you want to create a database user via GUI, you need to perform the below steps.

Login into the datasphere tenant. Make sure you have the required application roles to perform the task.Go to Space management and select the space. Scroll down and click create in database user. Provide the username and deploy the space. Save the password and share it with the user. 

Automated Process

To overcome the long process, i have divided the python script to perform all these task in sequential manner. 

Datasphere CLI Setup : Set the CLI and login into datasphereReading the metadata: Read the space metadata using SAP Datasphere CLI space read commandModifying JSON : Enhance the output JSON with the new user details and deploy it using the space create commandPassword Reset : Reset the database user password and save it in a file for sharing.

Set up SAP Datasphere CLI (First time only)

To start working with Datasphere CLI, we need to perform few one time configuration like setting host, login in and setting cache. If you want to login via a different tenant, you need to change the CLIENT_ID and CLIENT_SECRETS as per the tenant.

datasphere config set host {“host_url”}
datasphere login –client-id {“client_id”} –client-secret {“client_secret”}
datasphere config set cache –client-id {“client_id”} –client-secret {“client_secret”}

 Once you are done with the initial setup of CLI, we can start working on the main code which will create database users.

Reading space metadata

Since we do not have a direct command to create DB user, we extract the space metadata JSON using CLI. This command output the space metadata JSON into console. I have stored this into a variable that i will use later in modifying the JSON

space_Json = datasphere space read -space “{space”} –definations

Modifying the JSON and deploying it in datasphere 

To create a new databased user in space, we need to add the new user details in the dbuser object of the JSON. We can store the space metadata JSON into our local machine and modify it or we can modify it on the go using tempfiles. I have used the later approach as it eliminates the need of JSON management 

#Concatenating the space and username
new_db_user = f”{space}#{username}”

#Appending the new_db_user details into the space metadata JSON
space_JSON[space][“spaceDefinition”][“dbusers”][new_db_user] = {
“ingestion”:{
“auditing”:{
“dppRead”:{
“retentionPeriod”:21
“isAuditPolicyActive”:True
},

}
},
“consumption”:{
“consumptionWithGrant”:false,
“spaceSchemaAccess”:True,
“scriptServerAccess”:false,
“localSchemaAccess”:false,
“hdiGrantorForCupsAccess”:false
}

Once the JSON is modified, write the new JSON into a temporary file. I have stored the file path into a variable called tmp_file_path. I will push this file path to datasphere tenant.

#Pushing the modified JSON to datasphere tenant
datasphere space create –file-path “{tmp_file_path}” — force-defination-deployment

If you want to delete a database user, remove the entry from the JSON and run the same command and add –enforce-database-user-deletion in the end. If this command is not added, then the deletion will not work.

Password Reset

Once the database user is created, we need to reset the password to store it in a local file for sharing. Below is the command to perform that action

datasphere dbusers password reset –space{“space”} –databaseuser {“new_db_user”}

 (Tip: Add a 30 seconds wait time between creating the database user and resetting the password commands because the space deployment takes some time and we cannot reset the password before that).

I have also added an automatic email creation in my workflow and inserting database user details into a local table for future analysis.

Conclusion:

With this automation, we can create database users in datasphere tenant without even login into datasphere. This workflow provides a more robust way to handle creation and maintenance database users. 

I would love to know your thoughts on this workflow in the comments below. Lets explore more opportunities of automation using datasphere CLI. 

​ IntroductionOne way to access datasphere hana database is via database users. Each database user is linked to one space (except database analysis user). We can create database user vai GUI however, its a long process and prone to errors. In this blog, i will share my work on how i automated the process using SAP Datasphere CLI and Python without the need to login into datasphere GUI.Old Process:If you want to create a database user via GUI, you need to perform the below steps.Login into the datasphere tenant. Make sure you have the required application roles to perform the task.Go to Space management and select the space. Scroll down and click create in database user. Provide the username and deploy the space. Save the password and share it with the user. Automated ProcessTo overcome the long process, i have divided the python script to perform all these task in sequential manner. Datasphere CLI Setup : Set the CLI and login into datasphereReading the metadata: Read the space metadata using SAP Datasphere CLI space read commandModifying JSON : Enhance the output JSON with the new user details and deploy it using the space create commandPassword Reset : Reset the database user password and save it in a file for sharing.Set up SAP Datasphere CLI (First time only)To start working with Datasphere CLI, we need to perform few one time configuration like setting host, login in and setting cache. If you want to login via a different tenant, you need to change the CLIENT_ID and CLIENT_SECRETS as per the tenant.datasphere config set host {“host_url”}
datasphere login –client-id {“client_id”} –client-secret {“client_secret”}
datasphere config set cache –client-id {“client_id”} –client-secret {“client_secret”} Once you are done with the initial setup of CLI, we can start working on the main code which will create database users.Reading space metadataSince we do not have a direct command to create DB user, we extract the space metadata JSON using CLI. This command output the space metadata JSON into console. I have stored this into a variable that i will use later in modifying the JSONspace_Json = datasphere space read -space “{space”} –definationsModifying the JSON and deploying it in datasphere To create a new databased user in space, we need to add the new user details in the dbuser object of the JSON. We can store the space metadata JSON into our local machine and modify it or we can modify it on the go using tempfiles. I have used the later approach as it eliminates the need of JSON management #Concatenating the space and username
new_db_user = f”{space}#{username}”

#Appending the new_db_user details into the space metadata JSON
space_JSON[space][“spaceDefinition”][“dbusers”][new_db_user] = {
“ingestion”:{
“auditing”:{
“dppRead”:{
“retentionPeriod”:21
“isAuditPolicyActive”:True
},

}
},
“consumption”:{
“consumptionWithGrant”:false,
“spaceSchemaAccess”:True,
“scriptServerAccess”:false,
“localSchemaAccess”:false,
“hdiGrantorForCupsAccess”:false
}Once the JSON is modified, write the new JSON into a temporary file. I have stored the file path into a variable called tmp_file_path. I will push this file path to datasphere tenant.#Pushing the modified JSON to datasphere tenant
datasphere space create –file-path “{tmp_file_path}” — force-defination-deployment If you want to delete a database user, remove the entry from the JSON and run the same command and add –enforce-database-user-deletion in the end. If this command is not added, then the deletion will not work.Password ResetOnce the database user is created, we need to reset the password to store it in a local file for sharing. Below is the command to perform that actiondatasphere dbusers password reset –space{“space”} –databaseuser {“new_db_user”} (Tip: Add a 30 seconds wait time between creating the database user and resetting the password commands because the space deployment takes some time and we cannot reset the password before that).I have also added an automatic email creation in my workflow and inserting database user details into a local table for future analysis.Conclusion:With this automation, we can create database users in datasphere tenant without even login into datasphere. This workflow provides a more robust way to handle creation and maintenance database users. I would love to know your thoughts on this workflow in the comments below. Lets explore more opportunities of automation using datasphere CLI.    Read More Technology Blog Posts by Members articles 

#SAP

#SAPTechnologyblog