How to Manage MySQL Users and Databases in a Database Cluster

MySQL database clusters come configured with a default database (defaultdb) and a default administrative user (doadmin). These defaults are necessary for cluster replication and administration, so you can’t delete them, but you can add additional users and databases.

By default, every database cluster is publicly accessible. To limit access, you can add trusted sources or manage user permissions.

Create or Delete a Database User Using the CLI

Create, Update, or Delete a Database User Using the API

curl -X POST \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
  -d '{"name": "app-01"}' \
  "https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30/users"

import (
    "context"
    "os"

    "github.com/digitalocean/godo"
)

func main() {
    token := os.Getenv("DIGITALOCEAN_TOKEN")

    client := godo.NewFromToken(token)
    ctx := context.TODO()

    addUserRequest := &godo.DatabaseCreateUserRequest{
        Name: "app-01",
    }

    user, _, err := client.Databases.CreateUser(ctx, "88055188-9e54-4f21-ab11-8a918ed79ee2", addUserRequest)

}

import os
from pydo import Client

client = Client(token=os.environ.get("DIGITALOCEAN_TOKEN"))

add_user_resp = client.databases.add_user(database_cluster_uuid="ab7bb7a", body={"name": "app-01"})

curl -X PUT \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
  -d '{"settings": {"acl": [{"topic": "events", "permission": "produce"}]}}' \
  "https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30/users"

import (
    "context"
    "os"

    "github.com/digitalocean/godo"
)

func main() {
    token := os.Getenv("DIGITALOCEAN_TOKEN")

    client := godo.NewFromToken(token)
    ctx := context.TODO()
    userName := "test-user"

    updateUserRequest := &godo.DatabaseUpdateUserRequest{
      Settings: {
        ACL: [
          {
            Permssion: "consume",
            Topic: "events",
          }
          {
            Permission: "produce",
            Topic: "metrics",
          }
        ]
      }
    }

    user, _, err := client.Databases.UpdateUser(ctx, "88055188-9e54-4f21-ab11-8a918ed79ee2", userName, updateUserRequest)
}

curl -X DELETE \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
  "https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30/users/app-01"

import (
    "context"
    "os"

    "github.com/digitalocean/godo"
)

func main() {
    token := os.Getenv("DIGITALOCEAN_TOKEN")

    client := godo.NewFromToken(token)
    ctx := context.TODO()

    _, err := client.Databases.DeleteUser(ctx, "9cc10173-e9ea-4176-9dbc-a4cee4c4ff30", "app-01")
}

import os
from pydo import Client

client = Client(token=os.environ.get("DIGITALOCEAN_TOKEN"))

delete_resp = client.databases.delete_user(database_cluster_uuid="aba134a", username="backend_user1")

Add or Delete a Database User Using the Control Panel

To add or delete users or databases to a database cluster, click the name of the database to go to its Overview page, then select the Users & Databases tab.

Create a new database in the Databases section by entering a name in the Add new database field and clicking Save. You can also delete a database here by opening its More menu, clicking Delete, and then confirming the deletion.

Create new users in the Users section by entering a name in the Add new user field, selecting a Password encryption option, and clicking Save. New users will receive the permissions of the doadmin user by default; these can be changed by following the instructions at How to Modify User Privileges in MySQL Databases.

You can also delete a user here by opening the user’s More menu, clicking Delete, and then confirming the deletion. Similarly, to reset a user’s password, open the user’s More menu and select Reset password. If you have any services that depend on that user’s credentials, such as log forwarding, resetting the password may break that service.

Password Encryption

DigitalOcean Managed Databases using MySQL 8+ are automatically configured to use caching_sha2_password authentication by default. caching_sha2_password uses a stronger password encryption than prior versions of MySQL and some applications (such as PHP based applications using PHP 7.1 or older) have trouble connecting to MySQL 8+ databases. You can use the Password Encryption option to set a user’s password encryption to the legacy version (mysql_native_password) if your applications are experiencing authentication issues.

To change a user’s password encryption on a database using the control panel, click the name of the database to go to its Overview page, then select the Users & Databases tab. Beside the user you want to change, click the More menu and select Edit Password Encryption.

In the Password encryption menu, select the desired encryption type, then click Save. The database automatically updates with the new encryption preference.