Managing MySQL® users

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 the list of clusters in the folder.

1. Get an IAM token for API authentication and put it into the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.list method and make a request, e.g., via cURL:

   curl \
       --request GET \
       --header "Authorization: Bearer $IAM_TOKEN" \
       --url 'https://mdb.api.cloud.yandex.net/managed-mysql/v1/clusters/<cluster_ID>/users'
   

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

3. View the server response to make sure the request was successful.

1. Get an IAM token for API authentication and put it into the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

3. Use the UserService/List call and make a request, e.g., via gRPCurl:

   grpcurl \
       -format json \
       -import-path ~/cloudapi/ \
       -import-path ~/cloudapi/third_party/googleapis/ \
       -proto ~/cloudapi/yandex/cloud/mdb/mysql/v1/user_service.proto \
       -rpc-header "Authorization: Bearer $IAM_TOKEN" \
       -d '{
             "cluster_id": "<cluster_ID>"
           }' \
       mdb.api.cloud.yandex.net:443 \
       yandex.cloud.mdb.mysql.v1.UserService.List
   

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

4. View the server response to make sure the request was successful.

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.

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

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

   For more information about creating 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.

1. Get an IAM token for API authentication and put it into the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Create a file named body.json and add the following contents to it:

   {
       "userSpec": {
           "name": "<username>",
           "password": "<user_password>",
           "permissions": [
               {
                   "databaseName": "<DB_name>",
                   "roles": [
                       "<privilege_1>", "<privilege_2>", ..., "<privilege_N>"
                   ]
               }
           ]
       }
   }'
   

   Where userSpec lists the new DB user settings:

   • name: Username.

   • password: User 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.

   • permissions: User permission settings:

     • databaseName: Name of the database the user gets access to.
     • roles: Array of user's privileges, each provided as a separate string in the array. For a list of available values, see User privileges in Managed Service for MySQL® clusters.

     For each database, add a separate item with permission settings to the permissions array.

3. Use the User.create method and make a request, e.g., via cURL:

   curl \
       --request POST \
       --header "Authorization: Bearer $IAM_TOKEN" \
       --header "Content-Type: application/json" \
       --url 'https://mdb.api.cloud.yandex.net/managed-mysql/v1/clusters/<cluster_ID>/users' \
       --data "@body.json"
   

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

4. View the server response to make sure the request was successful.

1. Get an IAM token for API authentication and put it into the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

3. Create a file named body.json and add the following contents to it:

   {
       "cluster_id": "<cluster_ID>",
       "user_spec": {
           "name": "<username>",
           "password": "<user_password>",
           "permissions": [
               {
                   "database_name": "<DB_name>",
                   "roles": [
                       "<privilege_1>", "<privilege_2>", ..., "<privilege_N>"
                   ]
               }
           ]
       }
   }
   

   Where user_spec lists the new DB user settings:

   • name: Username.

   • password: User 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.

   • permissions: User permission settings:

     • database_name: Name of the database the user gets access to.
     • roles: Array of user's privileges, each provided as a separate string in the array. For a list of available values, see User privileges in Managed Service for MySQL® clusters.

     For each database, add a separate item with permission settings to the permissions array.

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

4. Use the UserService/Create call and make a request, e.g., via gRPCurl:

   grpcurl \
       -format json \
       -import-path ~/cloudapi/ \
       -import-path ~/cloudapi/third_party/googleapis/ \
       -proto ~/cloudapi/yandex/cloud/mdb/mysql/v1/user_service.proto \
       -rpc-header "Authorization: Bearer $IAM_TOKEN" \
       -d @ \
       mdb.api.cloud.yandex.net:443 \
       yandex.cloud.mdb.mysql.v1.UserService.Create \
       < body.json
   

5. View the server response to make sure the request was successful.

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 password, run this command:

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

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

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

   For more information about creating this file, see Creating clusters.

2. Locate the 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.

1. Get an IAM token for API authentication and put it into the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.update method and make a request, e.g., using cURL:

   Warning

   The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the updateMask parameter as a single comma-separated string.

   curl \
       --request PATCH \
       --header "Authorization: Bearer $IAM_TOKEN" \
       --header "Content-Type: application/json" \
       --url 'https://mdb.api.cloud.yandex.net/managed-mysql/v1/clusters/<cluster_ID>/users/<username>' \
       --data '{
                 "updateMask": "password",
                 "password": "<new_password>"
               }'
   

   Where:

   • updateMask: List of parameters to update as a single string, separated by commas.

     In this case, only one parameter is provided.

   • password: New password.

     Note

     The password must be between 8 and 128 characters.

   You can get the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

3. View the server response to make sure the request was successful.

1. Get an IAM token for API authentication and put it into the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

3. Use the UserService/Update call and make a request, e.g., using gRPCurl:

   Warning

   The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

   Format for listing settings

   "update_mask": {
       "paths": [
           "<setting_1>",
           "<setting_2>",
           ...
           "<setting_N>"
       ]
   }
   

   grpcurl \
       -format json \
       -import-path ~/cloudapi/ \
       -import-path ~/cloudapi/third_party/googleapis/ \
       -proto ~/cloudapi/yandex/cloud/mdb/mysql/v1/user_service.proto \
       -rpc-header "Authorization: Bearer $IAM_TOKEN" \
       -d '{
             "cluster_id": "<cluster_ID>",
             "user_name": "<username>",
             "update_mask": {
               "paths": [
                 "password"
               ]
             },
             "password": "<new_password>"
           }' \
       mdb.api.cloud.yandex.net:443 \
       yandex.cloud.mdb.mysql.v1.UserService.Update
   

   Where:

   • update_mask: List of parameters to update as an array of paths[] strings.

     In this case, only one parameter is provided.

   • password: New password.

     Note

     The password must be between 8 and 128 characters.

   You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

4. View the server response to make sure the request was successful.

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=<maximum_requests> \
  --max-updates-per-hour=<maximum_UPDATE_requests> \
  --max-connections-per-hour=<maximum_connections> \
  --max-user-connections=<maximum_concurrent_connections>

Where:

• global-permissions: Comma-separated list of administrative privileges.
• max-questions-per-hour: Maximum number of requests per hour.
• max-updates-per-hour: Maximum number of UPDATE requests 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 the list of clusters in the folder.

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

   For more information about creating this file, see Creating clusters.

2. Locate the 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   = <maximum_requests>
       max_updates_per_hour     = <maximum_UPDATE_requests>
       max_connections_per_hour = <maximum_connections>
       max_user_connections     = <maximum_concurrent_connections>
       ...
     }
   }
   

   Where:

   • max-questions-per-hour: Maximum number of requests per hour.
   • max-updates-per-hour: Maximum number of UPDATE requests 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.

1. Get an IAM token for API authentication and put it into the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.update method and make a request, e.g., using cURL:

   Warning

   The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the updateMask parameter as a single comma-separated string.

   curl \
       --request PATCH \
       --header "Authorization: Bearer $IAM_TOKEN" \
       --header "Content-Type: application/json" \
       --url 'https://mdb.api.cloud.yandex.net/managed-mysql/v1/clusters/<cluster_ID>/users/<username>' \
       --data '{
                 "updateMask": "globalPermissions,connectionLimits,authenticationPlugin",
                 "globalPermissions": [
                   "<administrative_privilege_1>",
                   "<administrative_privilege_2>",
                   ...,
                   "<administrative_privilege_N>"
                 ],
                 "connectionLimits": {
                   "maxQuestionsPerHour": "<maximum_requests>",
                   "maxUpdatesPerHour": "<maximum_UPDATE_requests>",
                   "maxConnectionsPerHour": "<maximum_connections>",
                   "maxUserConnections": "<maximum_concurrent_connections>"
                 },
                 "authenticationPlugin": "<authentication_plugin>"
               }'
   

   Where:

   • updateMask: List of parameters to update as a single string, separated by commas.

   • globalPermissions: Array of administrative privileges. each provided as a separate string in the array. For a list of available values, see the relevant method description.

   • connectionLimits: User connection settings:

     • maxQuestionsPerHour: Maximum number of requests per hour.
     • maxUpdatesPerHour: Maximum number of UPDATE requests per hour.
     • maxConnectionsPerHour: Maximum number of connections per hour.
     • maxUserConnections: Maximum number of concurrent connections.

     The minimum value for each connection setting is 0.

   • authenticationPlugin: User authentication plugin. For a list of available plugins, see the relevant method description.

   You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

3. View the server response to make sure the request was successful.

1. Get an IAM token for API authentication and put it into the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

3. Use the UserService/Update call and make a request, e.g., using gRPCurl:

   Warning

   The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

   Format for listing settings

   "update_mask": {
       "paths": [
           "<setting_1>",
           "<setting_2>",
           ...
           "<setting_N>"
       ]
   }
   

   grpcurl \
       -format json \
       -import-path ~/cloudapi/ \
       -import-path ~/cloudapi/third_party/googleapis/ \
       -proto ~/cloudapi/yandex/cloud/mdb/mysql/v1/user_service.proto \
       -rpc-header "Authorization: Bearer $IAM_TOKEN" \
       -d '{
             "cluster_id": "<cluster_ID>",
             "user_name": "<username>",
             "update_mask": {
               "paths": [
                 "global_permissions",
                 "connection_limits",
                 "authentication_plugin"
               ]
             },
             "global_permissions": [
               "<administrative_privilege_1>",
               "<administrative_privilege_2>",
               ...,
               "<administrative_privilege_N>"
             ],
             "connection_limits": {
               "max_questions_per_hour": "<maximum_requests>",
               "max_updates_per_hour": "<maximum_UPDATE_requests>",
               "max_connections_per_hour": "<maximum_connections>",
               "max_user_connections": "<maximum_concurrent_connections>"
             },
             "authentication_plugin": "<authentication_plugin>"
           }' \
       mdb.api.cloud.yandex.net:443 \
       yandex.cloud.mdb.mysql.v1.UserService.Update
   

   Where:

   • update_mask: List of parameters to update as an array of paths[] strings.

   • global_permissions: Array of administrative privileges. each provided as a separate string in the array. For a list of available values, see the relevant method description.

   • connection_limits: User connection settings:

     • max_questions_per_hour: Maximum number of requests per hour.
     • max_updates_per_hour: Maximum number of UPDATE requests per hour.
     • max_connections_per_hour: Maximum number of connections per hour.
     • max_user_connections: Maximum number of concurrent connections.

     The minimum value for each connection setting is 0.

   • authentication_plugin: User authentication plugin. For a list of available plugins, see the relevant method description.

   You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

4. View the server response to make sure the request was successful.

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 the list of clusters in the folder.

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

   For more information about creating this file, see Creating clusters.

2. Delete the yandex_mdb_mysql_user resource with the user's description.

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.

1. Get an IAM token for API authentication and put it into the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.delete method and make a request, e.g., via cURL:

   curl \
       --request DELETE \
       --header "Authorization: Bearer $IAM_TOKEN" \
       --url 'https://mdb.api.cloud.yandex.net/managed-mysql/v1/clusters/<cluster_ID>/users/<username>'
   

   You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

3. View the server response to make sure the request was successful.

1. Get an IAM token for API authentication and put it into the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

3. Use the UserService/Delete call and make a request, e.g., via gRPCurl:

   grpcurl \
       -format json \
       -import-path ~/cloudapi/ \
       -import-path ~/cloudapi/third_party/googleapis/ \
       -proto ~/cloudapi/yandex/cloud/mdb/mysql/v1/user_service.proto \
       -rpc-header "Authorization: Bearer $IAM_TOKEN" \
       -d '{
             "cluster_id": "<cluster_ID>",
             "user_name": "<username>"
           }' \
       mdb.api.cloud.yandex.net:443 \
       yandex.cloud.mdb.mysql.v1.UserService.Delete
   

   You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

4. View the server response to make sure the request was successful.

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.