[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for MySQL®](../index.md) > [Step-by-step guides](index.md) > MySQL® users > Managing user permissions

# Managing user permissions

You can manage [user permissions](../concepts/user-rights.md) for the whole cluster or individual databases by [changing user privileges](#grant-privilege). The [administrative privileges](../concepts/settings-list.md#setting-administrative-privileges) are set at the cluster level.

{% note warning %}

To change user permissions at the [cluster](../concepts/settings-list.md#setting-administrative-priveleges) or [database](grant.md#grant-privilege) level, use the Yandex Cloud interfaces. Changes made using SQL commands are not saved.

For more information, see [User permissions in Managed Service for MySQL®](../concepts/user-rights.md).

{% endnote %}

## Changing user privileges {#grant-privilege}

{% list tabs group=instructions %}

- Management console {#console}

  1. Navigate to **Managed Service for MySQL**.

  1. Click the name of your cluster and select the ![image-users](../../_assets/console-icons/persons.svg) **Users** tab.

  1. Click ![image](../../_assets/console-icons/ellipsis.svg) and select **Configure**.

  1. Optionally, add the databases required for the user:

     1. Click **Add database**.
     1. Select the database from the drop-down list.
     1. Repeat these two steps to select all required databases.
     1. To revoke access to a specific database, delete it from the list by clicking ![image](../../_assets/console-icons/xmark.svg) to the right of the database name.

  1. Specify the required user privileges for each database individually:

     1. In the **Roles** column, click ![image](../../_assets/console-icons/plus.svg).
     1. In the drop-down list, select the privilege you want to grant the user.
     1. Repeat these two steps to add all required privileges.

  1. To revoke a privilege, click ![image](../../_assets/console-icons/xmark.svg) to the right of its name.
  1. Optionally, in the **Global permissions** under **Additional settings**, configure [administrative user privileges](../concepts/settings-list.md#setting-administrative-privileges) at the cluster level.
  1. Click **Save**.

- CLI {#cli}

  If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

  The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

  * Granting privileges to a user:

      ```bash
      yc managed-mysql user grant-permission <username> \
        --cluster-name <cluster_name> \
        --database <DB_name> \
        --permissions <privileges_separated_by_commas>
      ```

      You can get the cluster name with the [list of clusters in the folder](cluster-list.md), the database name, with the [list of databases in the cluster](databases.md#list-db), and the username, with the [list of users in the cluster](cluster-users.md#list-users).

  * Revoking privileges from a user:

      ```bash
      yc managed-mysql user revoke-permission <username> \
        --cluster-name <cluster_name> \
        --database <DB_name> \
        --permissions <privileges_separated_by_commas>
      ```

      To grant or revoke `ALL_PRIVILEGES`, specify the `ALL` alias as the privilege name.

- Terraform {#tf}

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

      For information on how to create this file, see [Creating a cluster](cluster-create.md).

  1. Find the relevant `yandex_mdb_mysql_user` resource and change the list of user’s privileges for the appropriate database in the `roles` parameter:

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

      Where:

      * `database_name`: Name of the database the user will have access to.
      * `roles`: List of user privileges for the DB.

  1. Make sure the settings are correct.

      1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.
      1. Run this command:
      
         ```bash
         terraform validate
         ```
      
         Terraform will show any errors found in your configuration files.

  1. Confirm updating the resources.

      1. Run this command to view the planned changes:
      
         ```bash
         terraform plan
         ```
      
         If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.
      
      1. If everything looks correct, apply the changes:
         1. Run this command:
      
            ```bash
            terraform apply
            ```
      
         1. Confirm updating the resources.
         1. Wait for the operation to complete.

  For more on the properties of the `yandex_mdb_mysql_user` resource, see [this provider guide](../../terraform/resources/mdb_mysql_user.md).

- REST API {#api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

      ```bash
      export IAM_TOKEN="<IAM_token>"
      ```

  1. Call the [User.update](../api-ref/User/update.md) method, e.g., via the following [cURL](https://curl.se/) request:

      {% note 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.
      
      {% endnote %}

      ```bash
      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": "permissions",
                    "permissions": [
                      {
                        "databaseName": "<DB_name>",
                        "roles": [
                          "<privilege_1>", "<privilege_2>", ..., "<privilege_N>"
                        ]
                      }
                    ]
                  }'
      ```

      Where:

      * `updateMask`: Comma-separated string of settings to update.

          Here, we provide only one setting.

      * `permissions`: User permission settings:

          * `databaseName`: Name of the database to which the user will have access.
          * `roles`: Array of user privileges, each provided as a separate string in the array. For the list of possible values, see [User privileges in a cluster](../concepts/user-rights.md#db-privileges).

          In the `permissions` array, add a separate element with permission settings for each database.

      You can get the cluster ID from the [list of clusters in your folder](cluster-list.md#list-clusters), and the username from the [list of cluster users](#list-users).

  1. Check the [server response](../api-ref/User/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

      ```bash
      export IAM_TOKEN="<IAM_token>"
      ```

  1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
     
     ```bash
     cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
     ```
     
     Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
  1. Call the [UserService/Update](../api-ref/grpc/User/update.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

      {% note 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.
      
      {% cut "Format for listing settings" %}
      
      ```yaml
      "update_mask": {
          "paths": [
              "<setting_1>",
              "<setting_2>",
              ...
              "<setting_N>"
          ]
      }
      ```
      
      {% endcut %}
      
      {% endnote %}

      ```bash
      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": [
                    "permissions"
                  ]
                },
                "permissions": [
                  {
                    "database_name": "<DB_name>",
                    "roles": [
                      "<privilege_1>", "<privilege_2>", ..., "<privilege_N>"
                    ]
                  }
                ]
              }' \
          mdb.api.cloud.yandex.net:443 \
          yandex.cloud.mdb.mysql.v1.UserService.Update
      ```

      Where:

      * `update_mask`: List of settings you want to update as an array of strings (`paths[]`).

          Here, we provide only one setting.

      * `permissions`: User permissions:

          * `database_name`: Name of the database to which the user will have access.
          * `roles`: Array of user privileges, each provided as a separate string in the array. For the list of possible values, see [User privileges in a cluster](../concepts/user-rights.md#db-privileges).

          In the `permissions` array, add a separate element with permission settings for each database.

      You can get the cluster ID from the [list of clusters in your folder](cluster-list.md#list-clusters), and the username from the [list of cluster users](#list-users).

  1. Check the [server response](../api-ref/grpc/User/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

{% endlist %}

## Examples {#examples}

### Creating a user with read-only permissions {#user-read-only}

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

{% list tabs group=instructions %}

- Management console {#console}

  [Create a user](cluster-users.md#adduser) named `user2`. When creating the user:

  1. Add `db1` to the database list.
  1. Add the `SELECT` role for `db1`.

- CLI {#cli}

  1. Create a user named `user2`:

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

  1. Add the `SELECT` role for `db1`:

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

- Terraform {#tf}

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

      For more information on how to create this file, see [this guide](cluster-create.md).

  1. Add the `yandex_mdb_mysql_user` resource:

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

  1. Make sure the settings are correct.

      1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.
      1. Run this command:
      
         ```bash
         terraform validate
         ```
      
         Terraform will show any errors found in your configuration files.

  1. Confirm updating the resources.

      1. Run this command to view the planned changes:
      
         ```bash
         terraform plan
         ```
      
         If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.
      
      1. If everything looks correct, apply the changes:
         1. Run this command:
      
            ```bash
            terraform apply
            ```
      
         1. Confirm updating the resources.
         1. Wait for the operation to complete.

  For more information, see [this Terraform provider article](../../terraform/resources/mdb_mysql_user.md).

{% endlist %}