Skip to Content
Technical Articles
Author's profile photo Jascha Kanngiesser
Jascha Kanngiesser
February 21, 2023 5 minute read

@sap/dwc-cli: Reset Database User Passwords

This blog post is part of a series of blogs I published about @sap/dwc-cli. Find all blog posts related to this topic in my overview blog post here.

The Node.js-based Command-Line Interface (CLI) for SAP Data Warehouse Cloud, @sap/dwc-cli hosted on npmjs.com, allows you to interact with your SAP Data Warehouse Cloud tenant from the terminal or command line. Since version 2023.1 you can use the CLI to reset and retrieve new passwords for the database users in your space.

Introduction

Since the release of SAP Data Warehouse Cloud version 2023.1, you can now use the SAP Data Warehouse Cloud Command-Line Interface (CLI) to reset and retrieve new passwords for your database users in your spaces. This allows you to fully automate the handling of database users by using the CLI to create, update, and delete database users in your space.

In this blog post, I walk you through all the steps and highlight the new command to reset and retrieve new database user passwords.

Before you start with the first step, you need to initialize the cache of the CLI again, if you haven’t done this after version 2023.1 had been deployed into your tenant, using the dwc cache init command. I’m using the OAuth Client-based login mechanism using the dwc login –secrets-file command. I have set the host I’m working with globally using the dwc host set command, so that I don’t have to repeat the host and credentials with each and every command throughout this blog.

$ dwc host set https://myhost.eu10.hcs.cloud.sap/
$ dwc login --secrets-file /path/to/secrets/file.json 
$ dwc cache init

Code Sample 1 – Updating the CLI

Since this is a server-side enhancement only, you are not required to install a new version of the @sap/dwc-cli module from npmjs.com. You can use the new command to reset the passwords with versions of @sap/dwc-cli less than 2023.x, for example, 2022.24.0.

Creating a Space

I’m using the CLI to create a space with a technical ID MYEXAMPLESPACE and description “My Example Space” with only minimal configuration. I do only specify two database users MY_FIRST_DB_USER and MY_SECOND_DB_USER in my space and omit the optional properties, falling back to default values.

{
  "MYEXAMPLESPACE": {
    "spaceDefinition": {
      "version": "1.0.4",
      "label": "My Example Space",
      "assignedStorage": 0,
      "assignedRam": 0,
      "members": [
        {
          "name": "JKANNGIESSER",
          "type": "user"
        }
      ],
      "dbusers": {
        "MYEXAMPLESPACE#MY_FIRST_DB_USER": {
          "ingestion": {
            "auditing": {
              "dppRead": {
                "isAuditPolicyActive": false,
                "retentionPeriod": 7
              },
              "dppChange": {
                "isAuditPolicyActive": false,
                "retentionPeriod": 7
              }
            }
          },
          "consumption": {
            "localSchemaAccess": true,
            "spaceSchemaAccess": true,
            "hdiGrantorForCupsAccess": true
          }
        },
        "MYEXAMPLESPACE#MY_SECOND_DB_USER": {
          "ingestion": {
            "auditing": {
              "dppRead": {
                "isAuditPolicyActive": false,
                "retentionPeriod": 7
              },
              "dppChange": {
                "isAuditPolicyActive": false,
                "retentionPeriod": 7
              }
            }
          },
          "consumption": {
            "localSchemaAccess": true,
            "spaceSchemaAccess": true,
            "hdiGrantorForCupsAccess": true
          }
        }
      }
    }
  }
}

Space Definition Example 1

Using the CLI, I can create the space using the dwc spaces create command, pointing it to the space definition file using the –file-path option.

$ dwc spaces create --file-path space.json

Code Sample 2 – Creating a new space

The space was created successfully.

Successfully%20created%20space

Picture 1 – Successfully created space

Also, the two database users have been created.

Existing%20database%20users

Picture 2 – Existing database users

Reseting Database User Passwords

After you have initialized the CLI again, you find a new command dbusers when running dwc.

$ dwc
Usage: dwc [options] [command]

Command-Line Interface for SAP Data Warehouse Cloud.

Options:
  -v, --version              output the current version
  -H, --host <host>          specifies the url where the tenant is hosted (optional)
  -O, --options-file <file>  path to options file (optional)
  -h, --help                 display help for command

Commands:
  cache                      work with the local CLI cache
  dbusers [options]          manage and orchestrate database users
  help [command]             display help for command
  host                       configure host properties
  login [options]            log in to your account using interactive OAuth authentication
  logout                     log out from your account
  passcode-url [options]     display the passcode url
  secrets                    work with the locally stored secrets
  spaces [options]           manage and orchestrate spaces

Code Sample 3 – New command dbusers

Digging deeper into this new dbusers command, it reveals a subcommand password for working with passwords.

$ dwc dbusers
Usage: dwc dbusers [options] [command]

manage and orchestrate database users

Options:
  -H, --host <host>   specifies the url where the tenant is hosted (optional)
  -h, --help          display help for command

Commands:
  password [options]  maintain password of database user
  help [command]      display help for command

Code Sample 4 – dwc dbusers 

The command dwc dbusers password again comes with another subcommand reset.

$ dwc dbusers password
Usage: dwc dbusers password [options] [command]

maintain password of database user

Options:
  -H, --host <host>  specifies the url where the tenant is hosted (optional)
  -h, --help         display help for command

Commands:
  reset [options]    reset password of database user
  help [command]     display help for command

Code Sample 5 – dwc dbusers password reset

This command requires two options –space and –databaseuser, specifying the space ID and ID of the database user for which you would like to reset the password. I have removed some of the default options for better readability.

$ dwc dbusers password reset --help
Usage: dwc dbusers password reset [options]

reset password of database user

Options:
  -P, --pretty                       pretty-formats JSON responses (optional)
  -S, --space <space>                space id (optional)
  -j, --databaseuser <databaseuser>  database user id (optional)
  -o, --output <output>              specifies the file to store the output of the command (optional)
  -h, --help                         display help for command

Code Sample 6 – Options available for command dwc dbusers password reset

To reset and retrieve a new password for any of the existing database users within my space, I can provide the space ID and database user ID directly as options to the command. I also use the –pretty option to pretty-format the response. I could also use the –output option to store the response in a file.

$ dwc dbusers password reset --space MYEXAMPLESPACE --databaseuser MYEXAMPLESPACE#MY_FIRST_DB_USER --pretty
{
  "password": ")@:Nrga/6eQq,RPg3-05SL9.Z%]dL_WO",
  "username": "MYEXAMPLESPACE#MY_FIRST_DB_USER",
  "host": "somehost.hana.prod-eu10.hanacloud.ondemand.com",
  "port": 443
}

Code Sample 7 – Resetting the password of a database user

Please note: You always have to specify the full database user ID like <space ID>#<user ID>. In this example I have specified the ID as MYEXAMPLESPACE#MY_FIRST_DB_USER.

The CLI also allows you to enter the values for the space ID and database user ID interactively, by running the dwc dbusers password reset command again without specifying the –space and –databaseuser options.

$ dwc dbusers password reset --pretty
✔ Provide a value for option space (space id): … MYEXAMPLESPACE
✔ Provide a value for option databaseuser (database user id): … MYEXAMPLESPACE#MY_FIRST_DB_USER
{
  "password": "M5|44kP?=9s[6;*=VlRK:/aS4jkJZ@$P",
  "username": "MYEXAMPLESPACE#MY_FIRST_DB_USER",
  "host": "myhost.hana.prod-eu10.hanacloud.ondemand.com",
  "port": 443
}

Code Sample 8 – Resetting the password in interactive mode

Deleting Database Users

To remove any existing database users again, I send the updated space definition file to the tenant, again using the command dwc spaces create, removing any database user I want to delete. In this example I’m deleting the first of the two users. The updated space definition file looks like this.

{
  "MYEXAMPLESPACE": {
    "spaceDefinition": {
      "version": "1.0.4",
      "label": "My Example Space",
      "assignedStorage": 0,
      "assignedRam": 0,
      "members": [
        {
          "name": "JKANNGIESSER",
          "type": "user"
        }
      ],
      "dbusers": {
        "MYEXAMPLESPACE#MY_SECOND_DB_USER": {
          "ingestion": {
            "auditing": {
              "dppRead": {
                "isAuditPolicyActive": false,
                "retentionPeriod": 7
              },
              "dppChange": {
                "isAuditPolicyActive": false,
                "retentionPeriod": 7
              }
            }
          },
          "consumption": {
            "localSchemaAccess": true,
            "spaceSchemaAccess": true,
            "hdiGrantorForCupsAccess": true
          }
        }
      }
    }
  }
}

Space Definition Example 2

After running the same command from Code Sample 2 again, the database users have been updated and the first of the two users had been deleted successfully.

Picture 3 – Updated database access section

Conclusion

I’d be happy to hear your thoughts, ideas, and comments on this tool and what you think would be a nice-to-have enhancement to the CLI, making your life and work with SAP Data Warehouse Cloud easier. Let me know in the comments!

Further Reading

Command-Line Interface for SAP Data Warehouse Cloud on npmjs.com

Command-Line Interface for SAP Data Warehouse Cloud on SAP Help

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jens Rottmann-Matthes
      Jens Rottmann-Matthes

      Hi Jascha,

      this is really cool and a feature I was waiting for for a long time! Thank you. I hope that I will find some time soon to test it and automize my deployment-scripts further.

      Could you please also highlight, which options of the DWC-cli changed (like -D became -m ...) This unfortunately broke a script of mine, which I use in a productive system on a day-to-day basis.

      But the dwc-cli is really helpful for me and my colleagues! One thing which I would also really appreciate is, when you can also include the definition of analytic models in the downloaded json, or is it already available with some undocumented flag?

      Thank you so much for your cool work

      Jens

      Author's profile photo Jascha Kanngiesser
      Jascha Kanngiesser
      Blog Post Author

      Hi Jens Rottmann-Matthes

      thanks for reaching out! Great to hear the CLI helps you a lot in your daily work to automate activities related to SAP Data Warehouse Cloud. 🙂

      In general, we recommend to use the option's long name, for example, --definitions, instead of the short flag (-m, was -D). The reason is that the long names stay stable, the short flags might change.

      Which analytic models are you exactly referring to? Analytical Data Sets (ADS) you can create in the Data Builder as Tables or Views? These should be included already.

      Thanks,
      Jascha