Managing user permissions

Written by

Updated at November 18, 2024

Changing user privileges
Examples
- Creating a user with read-only permissions

You can manage user permissions at the level of an individual database by updating user privileges.

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.

Changing user privileges

Management console

CLI

Terraform

REST API

gRPC API

Go to the folder page and select Managed Service for MySQL.
Click the name of the cluster you need and select the Users tab.
Click and select Configure.
Add the databases required for the user:
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 revoke access to a specific database, delete it from the list by clicking to the right of the database name.
Set up user privileges for each of the user's databases.
1. In the Roles column, click .
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.
To revoke a privilege, click to the right of its name.
If necessary, set the administrative privileges for the user.
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.

Granting privileges to a user:
```
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, the DB name, with the list of databases in the cluster, and the user's name, with the list of users in the cluster.

Revoking user privileges:

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

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

Open the current Terraform configuration file with an infrastructure plan.

For more information about creating this file, see Creating clusters.
Find the yandex_mdb_mysql_user resource of the user you need and change the list of their privileges for the appropriate database in the roles parameter:
```
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 DB the user must have access to.
- roles: List of user privileges for the DB.
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.
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.

Get an IAM token for API authentication and put it into the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```
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": "permissions",
              "permissions": [
                {
                  "databaseName": "<DB_name>",
                  "roles": [
                    "<privilege_1>", "<privilege_2>", ..., "<privilege_N>"
                  ]
                }
              ]
            }'
```
Where:
- updateMask: List of parameters to update as a single string, separated by commas.
  
  In this case, only one parameter is provided.
- 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 the list of possible values, see User privileges in a cluster.
  For each database, add a separate item with permission settings to the permissions array.
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.
View the server response to make sure the request was successful.

Get an IAM token for API authentication and put it into the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```
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.
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": [
              "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 parameters to update as an array of paths[] strings.
  
  In this case, only one parameter is provided.
- 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 the list of possible values, see User privileges in a cluster.
  For each database, add a separate item with permission settings to the permissions array.
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.
View the server response to make sure the request was successful.

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:

Management console

CLI

Terraform

Create a user named user2. When creating a user:

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

Create a user named user2:

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

Add the SELECT role for the db1 database:

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

Open the current Terraform configuration file with an infrastructure plan.

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

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"]
  ...
  }
}

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.
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.

Managing user permissions

Changing user privilegesChanging user privileges

ExamplesExamples

Creating a user with read-only permissionsCreating a user with read-only permissions

Was the article helpful?

Changing user privileges

Examples

Creating a user with read-only permissions