Managing user 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
-
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:
- Click Add database.
- Select the database from the drop-down list.
- Repeat the previous two steps until all the required databases are selected.
- 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.
- Click
in the Roles column. - Select the privilege you want to add to the user from the drop-down list.
- Repeat the previous two steps until all the required privileges are added.
- Click
-
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 <comma-separated_list_of_privileges>
You can request the cluster name with a list of clusters in the folder, the DB name with a list of databases in the cluster, and the user's name with a list of users in the cluster.
-
Revoking user privileges:
yc managed-mysql user revoke-permission <username> \ --cluster-name <cluster_name> \ --database <DB_name> \ --permissions <comma-separated_list_of_privileges>
To grant or revoke the
ALL_PRIVILEGES
privilege, specify theALL
synonym as the privilege name.
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about how to create this file, see Creating clusters.
-
Find the desired user's
yandex_mdb_mysql_user
resource and change the list of their privileges for the required database in theroles
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.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
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.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
For more information, see the Terraform provider documentation
To update user privileges, 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 find out the name, get a list of users in the cluster. - Name of the database where you want to update the list of user privileges, in the
permissions.databaseName
parameter. To find out the name, get a list of databases in the cluster. - New list of user privileges as an array in the
permissions.roles
parameter. - List of user configuration fields to update (
permissions
in this case) in theupdateMask
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).
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:
- Add the
db1
database to the list of DBs. - Add the
SELECT
role for thedb1
database.
-
Create a user named
user2
:yc managed-mysql user create "user2" \ --cluster-name "cluster1" \ --password "SecretPassword"
-
Add the
SELECT
role for thedb1
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 a 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.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
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.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
For more information, see the Terraform provider documentation