SAP Datasphere CLI & Python Automation : ADD 1000+ Users in Datasphere space in few seconds

[vikasparmar88]

This blog is part of a blog series from SAP Datasphere CLI & Python Automation with the focus on SAP Datasphere & Python capabilities:

• SAP Datasphere CLI & Python: Exporting Modeling Objects to CSV Files for Each Artifact

Introduction

Managing users in SAP Datasphere can take a lot of time, especially when there are hundreds or thousands of users. Adding them one by one is slow and tiring. This blog explains an easy way to automate the process using Python and SAP Datasphere CLI. With this method, you can add over 1000 users in just a few seconds, saving time and effort.

Install or Update the SAP Datasphere Command Line Interface

https://help.sap.com/docs/SAP_DATASPHERE/d0ecd6f297ac40249072a44df0549c1a/f7d5eddf20a34a1aa48d8e2c68... 

Step-by-Step Process

Step 1: Prepare Login.Json file

Create OAuth Client with Purpose as Interactive Usage and Redirect URL as http://localhost:8080

Get the Refresh Token and access Token by executing below command 

datasphere config secrets show

Get the value of all below fields from the OAuth Client and prepare the Login.json file.

{
"client_id": "",
"client_secret": "",
"authorization_url": "",
"token_url": "",
"access_token": "",
"refresh_token": ""
}

Step-2: Prepare USERS.CSV File for Input:

There are 3 columns in CSV file.

1) Space : Space Name 

2) User : User ID in Datasphere 

3) Role : Scope Role name, required for user.

As shown below, There are 1357 users need to be assigned to Datasphere space with given role in Datasphere.

Step-3: Space_User_Role_Json.py file with below code

The Code: Automating User Assignments

The following Python script simplifies bulk user assignments by:

• Reading user-role mappings from a CSV file.
• Dynamically creating JSON files containing space-user-role mappings.
• Executing SAP Datasphere CLI commands to automate user additions.

dsp host : give URL of Datasphere Tenant.

secrets_file : Give Path of Login.json file.

import subprocess  # For OS commands on DSP CLI
import json  # For handling the JSON file format
import pandas as pd  # For reading the CSV file
import os  # For creating directories dynamically
import sys  # For accessing command-line arguments

def create_json_file(space_name, user_role_data, directory):
    """
    Create a JSON file for the given space name and associated user-role data, saving it to the specified directory.

    Args:
        space_name (str): The name of the space.
        user_role_data (list): A list of dictionaries containing user and role mappings.
        directory (str): The directory where the file should be saved.
    """
    # Define the JSON structure
    data = [
        {
            "id": user_role["User"],
            "roles": [user_role["Role"]]  # Roles are explicitly wrapped in a list
        }
        for user_role in user_role_data
    ]


    # Specify the file name and path
    file_name = f"{space_name}.json"
    file_path = os.path.join(directory, file_name)

    # Ensure the directory exists
    os.makedirs(directory, exist_ok=True)

    # Convert `data` to a JSON string with the desired format
    json_content = json.dumps(data, indent=2, separators=(',', ': '))

    # Remove unncessary new lines and spaces for Role
    json_content = json_content.replace("[\n", "[").replace("\n    ]","]").replace("[      ","[")

    # Create the JSON file with the updated content
    with open(file_path, "w") as json_file:
        json_file.write(json_content)

    print(f"JSON file created successfully for space : {space_name}")
    return file_path

def manage_spaces(csv_file_path):
    """
    Reads a CSV file to verify columns, then processes spaces, users, and roles to create JSON files
    and execute Datasphere CLI commands.

    Args:
        csv_file_path (str): The path to the CSV file containing Space, User, and Role columns.
    """
    try:
        # Check if the argument is passed
        if not csv_file_path:
            raise ValueError("csv_file_path argument is required.")
        
        # Check if the argument is a string and contains ".csv"
        if not isinstance(csv_file_path, str) or not csv_file_path.endswith(".csv"):
            raise ValueError("The csv_file_path must be a string and end with '.csv'.")
        
        print(f"Argument received: {csv_file_path}")
        
        # Login to Datasphere using host and secrets file
        dsp_host = '<DATASPHERE URL>'
        secrets_file = '<PATH>/Login.json'
        command = f'datasphere login --host {dsp_host} --secrets-file {secrets_file}'
        subprocess.run(command, shell=True)  # Execute the login command

        # Replace forward slashes with backslashes for consistency
        csv_file_path = csv_file_path.replace("/", "\\")
        print(f"Formatted csv_file_path: {csv_file_path}")
        
        # Read the CSV file
        data = pd.read_csv(csv_file_path)

        # Validate columns
        required_columns = ['Space', 'User', 'Role']
        if not set(required_columns).issubset(data.columns):
            raise ValueError("CSV file must contain 'Space', 'User', and 'Role' columns.")

        # Extract unique spaces
        spaces = data['Space'].unique()

        # Process each space
        for space in spaces:
            
            #For each space find users and their role given in csv file
            space_data = data[data['Space'] == space]
            user_role_data = space_data[['User', 'Role']].to_dict(orient='records')
            
            # Check if the space exists
            check_space_command = f'datasphere spaces read --space {space}'
            result = subprocess.run(check_space_command, shell=True, capture_output=True, text=True)
            
            # Create JSON file for each space
            path = csv_file_path.rsplit('\\', 1)[0] + "\\"
            json_file_path = create_json_file(space, user_role_data, path)
              
            # Add users to the space using the generated JSON file
            add_users_command = f'datasphere spaces users add --space "{space}" --file-path "{json_file_path}"'
            print(f"Adding Users to {space} space now...")
            try:
                result = subprocess.run(add_users_command, shell=True, capture_output=True, text=True)
                if result.returncode == 0:
                    print(f"Successfully added users to space '{space}' using file '{json_file_path}'.")
                else:
                    print(f"Failed to add users to space '{space}'. Error: {result.stderr}")
            except Exception as e:
                print(f"An error occurred while adding users to space '{space}': {e}")

    except FileNotFoundError:
        print(f"CSV file '{csv_file_path}' not found.")
    except ValueError as ve:
        print(f"Argument error: {ve}")
    except Exception as e:
        print(f"An error occurred: {e}")

# Check if the script is run with the required argument
if len(sys.argv) > 1:
    manage_spaces(sys.argv[1])
else:
    print("Please provide the CSV file path as an argument when running the script.")

Step 4: Open command prompt and execute the Space_User_Role_Json.py file

Give the path of USERS.csv file as argument

python Space_User_Role_Json.py('C:/DataSphere/USERS.csv')

Execution will take few seconds to complete and Once it's done you can see all users ( User count 1356 in my case ) are added to respective space with role assigned in USERS.csv file.

Conculsion

With this automation, SAP Datasphere user management becomes seamless and scalable. Python scripting combined with Datasphere CLI simplifies user assignments and minimizes human effort. Whether managing hundreds or thousands of users, this method enhances efficiency while maintaining structured access control.

If you have questions or noticed a scenario I didn’t cover, feel free to leave a comment below the blog post.

Thank

Vikas Parmar

SAP Datasphere  SAP HANA OData SQL Python