Contact UsGet started
© 2024 Iron Hive LLC Belgrade
Yandex Managed Service for MySQL®

Managing MySQL users

Written by
Updated at September 25, 2024

You can add and remove users, as well as manage their settings.

Warning

To change user permissions at the level of the entire cluster or an individual database, use the Yandex Cloud interfaces. Changes made by SQL commands are not saved.

For more information, see User permissions.

Getting a list of users

  1. Go to the folder page and select Managed Service for MySQL.
  2. Click the name of the cluster you need and select the Users tab.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To get a list of cluster users, run the following command:

yc managed-mysql user list --cluster-name=<cluster_name>

You can request the cluster name with a list of clusters in the folder.

To get a list of users, use the list REST API method for the User resource or the UserService/List gRPC API call and provide the cluster ID in the clusterId request parameter.

You can fetch the cluster ID with a list of clusters in the folder.

Creating a user

  1. Go to the folder page and select Managed Service for MySQL.

  2. Click the name of the cluster you need and select the Users tab.

  3. Click Create user.

  4. Enter the database username and password.

    Note

    The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter, number, or underscore. It must be from 1 to 32 characters long.

    The password must be from 8 to 128 characters long.

  5. Select one or more databases that the user should have access to:

    1. Click Add database.
    2. Select the database from the drop-down list.
    3. Repeat the previous two steps until all the required databases are selected.
    4. To delete a database added by mistake, hover over the line with the database name and click at the end of the line.

  6. Set up user privileges for each of the selected databases:

    1. Click in the Roles column.
    2. Select the privilege you want to add to the user from the drop-down list.
    3. Repeat the previous two steps until all the required privileges are added.

  7. To revoke a privilege granted by mistake, click to the right of its name.

  8. If necessary, specify the MySQL settings and administrative privileges for the user.

  9. Click Create.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To create a user in a cluster, run the command:

yc managed-mysql user create <username> \
  --cluster-name=<cluster_name> \
  --password=<user_password> \
  --permissions=<DB_list>

Where permissions is a list of DBs the user must have access to.

Note

The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter, number, or underscore. It must be from 1 to 32 characters long.

The password must be from 8 to 128 characters long.

You can request the cluster name with a list of clusters in the folder.

  1. Open the current Terraform configuration file with an infrastructure plan.

    For more information about how to create this file, see Creating clusters.

  2. Add the yandex_mdb_mysql_user resource:

    resource "yandex_mdb_mysql_user" "<username>" {
  cluster_id = "<cluster_ID>"
  name       = "<username>"
  password   = "<password>"
  permission {
    database_name = "<DB_name>"
    roles         = [<list_of_privileges>]
  }
  ...
}

    Where:

    • database_name: Name of the DB the user must have access to.
    • roles: List of user privileges for the DB.

    Note

    The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter, number, or underscore. It must be from 1 to 32 characters long.

    The password must be from 8 to 128 characters long.

  3. Make sure the settings are correct.

    1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

    2. Run the command:

      terraform validate

      If there are errors in the configuration files, Terraform will point to them.

  4. Confirm updating the resources.

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

To create a user, use the create REST API method for the User resource or the UserService/Create gRPC API call and provide the following in the request:

  • ID of the cluster in which you want to create a user, in the clusterId parameter. To find out the cluster ID, get a list of clusters in the folder.

  • Username in the userSpec.name parameter.

  • User password in the userSpec.password parameter.

    Note

    The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter, number, or underscore. It must be from 1 to 32 characters long.

    The password must be from 8 to 128 characters long.

  • The names of the databases that the user should have access to, in the required number of userSpec.permissions.databaseName parameters.

  • User privileges for each of the selected databases, in one or more userSpec.permissions.roles parameters.

Changing a password

  1. Go to the folder page and select Managed Service for MySQL.
  2. Click the name of the cluster you need and select the Users tab.
  3. Click and select Change password.
  4. Set a new password and click Edit.

Note

The password must be between 8 and 128 characters.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To change the user's password, run the command:

yc managed-mysql user update <username> \
  --cluster-name=<cluster_name> \
  --password=<new_password>

Note

The password must be between 8 and 128 characters.

You can request the cluster name with a list of clusters in the folder.

  1. Open the current Terraform configuration file with an infrastructure plan.

    For more information about how to create this file, see Creating clusters.

  2. Find the desired user's yandex_mdb_mysql_user resource.

  3. Change the value of the password field:

    resource "yandex_mdb_mysql_user" "<username>" {
  cluster_id = "<cluster_ID>"
  name       = "<username>"
  password   = "<new_password>"
  ...
}

    Note

    The password must be between 8 and 128 characters.

  4. Make sure the settings are correct.

    1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

    2. Run the command:

      terraform validate

      If there are errors in the configuration files, Terraform will point to them.

  5. Confirm updating the resources.

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

To update a user's password, use the update REST API method for the User resource or the UserService/Update gRPC API call and provide the following in the request:

  • ID of the cluster in which the user is located, in the clusterId parameter. To find out the cluster ID, get a list of clusters in the folder.

  • Username in the userName parameter. To get the username, retrieve a list of users in the cluster.

  • New user password in the password parameter.

    Note

    The password must be between 8 and 128 characters.

  • List of user configuration fields to update (password in this case) in the updateMask parameter.

Warning

This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the updateMask parameter (one line separated by commas).

Changing user settings

Tip

To change user permissions to access databases, follow this guide.

  1. Go to the folder page and select Managed Service for MySQL.
  2. Click the name of the cluster you need and select the Users tab.
  3. Click and select Configure.
  4. Configure the MySQL settings for the user.
  5. Click Save.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To configure the MySQL settings for the user, run the command:

yc managed-mysql user update <username> \
  --cluster-name=<cluster_name> \
  --global-permissions=<list_of_privileges> \
  --authentication-plugin=<authentication_plugin> \
  --max-questions-per-hour=<max_queries> \
  --max-updates-per-hour=<max_UPDATE_queries> \
  --max-connections-per-hour=<max_connections> \
  --max-user-connections=<max_concurent_connections>

Where:

  • global-permissions: Comma-separated list of administrative privileges.
  • max-questions-per-hour: Maximum number of queries per hour.
  • max-updates-per-hour: Maximum number of UPDATE queries per hour.
  • max-connections-per-hour: Maximum number of connections per hour.
  • max-user-connections: Maximum number of concurrent connections.

You can request the cluster name with a list of clusters in the folder.

  1. Open the current Terraform configuration file with an infrastructure plan.

    For more information about how to create this file, see Creating clusters.

  2. Find the desired user's yandex_mdb_mysql_user resource.

  3. To set limits on the number of connections and requests, add a block named connection_limits to its description:

    resource "yandex_mdb_mysql_user" "<username>" {
  ...
  connection_limits {
    max_questions_per_hour   = <max_queries>
    max_updates_per_hour     = <max_UPDATE_queries>
    max_connections_per_hour = <max_connections>
    max_user_connections     = <max_concurent_connections>
    ...
  }
}

    Where:

    • max-questions-per-hour: Maximum number of queries per hour.
    • max-updates-per-hour: Maximum number of UPDATE queries per hour.
    • max-connections-per-hour: Maximum number of connections per hour.
    • max-user-connections: Maximum number of concurrent connections.

  4. To configure a user authentication plugin, add a block named authentication_plugin to its description:

    resource "yandex_mdb_mysql_user" "<username>" {
  ...
  authentication_plugin = "<authentication_plugin>"
}

  5. Make sure the settings are correct.

    1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

    2. Run the command:

      terraform validate

      If there are errors in the configuration files, Terraform will point to them.

  6. Confirm updating the resources.

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

To update user settings, use the update REST API method for the User resource or the UserService/Update gRPC API call and provide the following in the request:

Warning

This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the updateMask parameter (one line separated by commas).

Deleting a user

  1. Go to the folder page and select Managed Service for MySQL.
  2. Click the name of the cluster you need and select the Users tab.
  3. Click and select Delete.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To remove a user, run:

yc managed-mysql user delete <username> --cluster-name=<cluster_name>

You can request the cluster name with a list of clusters in the folder.

  1. Open the current Terraform configuration file with an infrastructure plan.

    For more information about how to create this file, see Creating clusters.

  2. Delete the yandex_mdb_mysql_user resource with the description of the desired user.

  3. Make sure the settings are correct.

    1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

    2. Run the command:

      terraform validate

      If there are errors in the configuration files, Terraform will point to them.

  4. Confirm updating the resources.

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

To delete a user, use the delete REST API method for the User resource or the UserService/Delete gRPC API call and provide the following in the request:

Examples

Creating a user with read-only permissions

To create a new user named user2 with the SecretPassword password and read-only access to the db1 database in an existing cluster1:

Create a user named user2. When creating a user:

  1. Add the db1 database to the list of DBs.
  2. Add the SELECT role for the db1 database.

  1. Create a user named user2:

    yc managed-mysql user create "user2" \
  --cluster-name "cluster1" \
  --password "SecretPassword"

  2. Add the SELECT role for the db1 database:

    yc managed-mysql users grant-permission "user2" \
  --cluster-name "cluster1" \
  --database "db1" \
  --permissions "SELECT"

  1. Open the current Terraform configuration file with an infrastructure plan.

    For more information about creating this file, see Creating an MySQL cluster.

  2. Add the yandex_mdb_mysql_user resource:

    resource "yandex_mdb_mysql_user" "user2" {
  cluster_id = yandex_mdb_mysql_cluster.cluster1.id
  name       = "user2"
  password   = "SecretPassword"
  permission {
    database_name = "db1"
    roles         = ["SELECT"]
  ...
  }
}

  3. Make sure the settings are correct.

    1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

    2. Run the command:

      terraform validate

      If there are errors in the configuration files, Terraform will point to them.

  4. Confirm updating the resources.

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

Previous
Managing databases
Next
Managing user permissions
© 2024 Iron Hive LLC Belgrade
In this article: