How to Manage Users in Vultr Managed Databases for MySQL

MySQL and MariaDB Security Managed Databases

Updated on November 12, 2023

How to Manage Users in Vultr Managed Databases for MySQL header image

Introduction

MySQL is a high-performance open-source Relational Database Management System (RDBMS). Vultr Managed Databases for MySQL offer a secure, highly available, and scalable solution designed to support production applications and environments.

This guide explains how to manage users in a Vultr Managed Database for MySQL. You are to create new users for a deployed managed MySQL database using the cluster control panel, API, and the Vultr CLI tool to explore the usage of each tool. In addition, you will manage user privileges using the MySQL CLI tool. This includes managing user access to databases, tables, and the permitted actions a user can perform when accessing a MySQL database.

Prerequisites

Before you begin:

Deploy a Vultr Managed Database for MySQL

On your development machine:

Install the Postman API application
Install the Vultr CLI tool
Install MySQL CLI tool to access the database remotely

Create a New MySQL User using the Vultr Managed Database for MySQL Control Panel

The Vultr Managed Database for MySQL control panel allows you to create and manage database users without using any extra tools. To access the control panel, navigate to access your Vultr Customer Portal account and create new users as described in the steps below.

Log in to your Vultr account using the Vultr Customer Portal
Click Products on the main navigation menu
Navigate to Databases
Select your existing Vultr Managed Database for MySQL to open the cluster dashboard
Within the Control Panel, click Users & Databases on the top menu
Verify that the default vultradmin user is available in the Users section
Click the Add New User button to set up a new MySQL database user
In the open dialog, enter your desired username in the Username field, keep Default as the Password Encryption type, enter a strong password in the Password field, then click Create New User to save the new user details
Verify that your new MySQL user account is listed on the Users list in your database dashboard

Create a new MySQL Database User using the Vultr API

The Vultr API allows you to manage your account resources without directly accessing the Vultr customer portal. In this section, use the Postman API platform with your Vultr API key to create a new user on your Vultr Managed Database for MySQL.

Before you begin:

Enable your API key in the Vultr Customer Portal
Add your source development computer’s public IP Address on the allowed list to enable access to the API key

Set up the Postman Environment

From your computer's applications menu, open Postman
Within the Postman interface, click the + create new collection button, and select Blank Collection from the list of options
Assign the new collection a name such as Vultr API, then click Variables on the collection menu bar
Click the Add new Variable field and create the following two variables
- Variable: domain | Value: https://api.vultr.com/v2
- Variable: VULTR_API_KEY | Value: <enterYourApiKeyhere>
Navigate to the Authorization tab
Click the Type drop-down and select Bearer Token from the list of options
Enter your Vultr API key in the token field to enable all requests in the collection to use the authorization

Create a New MySQL Database User using the Vultr API

To create a new user in a Vultr Managed Database for MySQL using Postman and your Vultr API key, list your available databases and get your target databaseId. Using the databaseID, get the list of available users and create a new user for the target database. Each of the following requests is prefixed by the domain collection variable you created earlier by adding {{domain}} to each API URL.

Get the list of available databases:
- Create a GET HTTP request to get the list of available managed databases.
- Click Send to generate a response with an array of available databases
If the request fails, verify that your Vultr API Key is correct and your IP Address is allowed to access the key.
- Copy your target MySQL database id value to your clipboard
To get the list of database users:
- Create a new GET HTTP request to view the list of available database users using the API endpoint https://api.vultr.com/v2/databases/:databaseId/users. Replace the databaseId value with the database ID you copied earlier.
- Click Send to generate an array of available database users. The default vultradmin user details should display in your output.
To create a new database user for the database:
- Create a new POST HTTP request https://api.vultr.com/v2/databases/:databaseId/users to create a new user for your target database. Replace databaseId with the actual ID value you copied earlier.
- With your request body, select raw, click the JSON dropdown, and select JSON from the list to reveal the request body. Enter your desired new user in the username field, and a strong password in the password field. When blank, a new password is auto-generated for your database user
- Click Send to create the new user and verify your username, password and password encryption values
View the database users list again and verify that the new user is available

Create a New MySQL User using the Vultr CLI Tool

The Vultr CLI Tool grants you access to your Vultr Managed Database Control Panel functionalities similar to the API and customer portal. In this section, generate your target database ID from the available databases list, then, use the ID to create a new MySQL user for the database as described below.

Start a new Terminal or Windows PowerShell session to use the Vultr CLI tool
Export your Vultr API key as an environment variable
```
 $ export VULTR_API_KEY=<your API KEY here>
```
The above command activates the key for use in your current terminal session.

View the list of available databases

 $ vultr-cli database list

The above command lists all available databases attached to your Vultr account. Copy the ID value of your target database generated in your output similar to the one below:

 $ ID                         194de602-79ec-45b0-9a8d-1d9c8131ba7b
 $ DATE CREATED               2023-10-15 10:00:06
 $ REGION                     NRT
 $ DATABASE ENGINE            mysql
 $ DATABASE ENGINE VERSION    8
 $ STATUS                     Running
 $ LABEL                      sql1
 $ DB NAME                    defaultdb
 $ HOST                       vultr-prod-194de602-79ec-45b0-9a8d-1d9c8131ba7b-vultr-prod-169a.vultrdb.com
 $ USER                       vultradmin
 $ PASSWORD                   ****************
 $ PORT                       16751
 $ MAINTENANCE DOW            saturday
 $ MAINTENANCE TIME           09:00
 $ LATEST BACKUP              2023-10-15 14:05:23

View the list of available database users. Replace databaseId with the database ID you copied earlier

 $ vultr-cli database user list databaseId

Your output should look like the one below, verify that the vultradmin user is available on the list:

 $ USERNAME      vultradmin
 $ PASSWORD      ************
 $ ENCRYPTION    Default (MySQL 8+)
 $ ---------------------------
 $ USERNAME      new_user
 $ PASSWORD      ************
 $ ENCRYPTION    Default (MySQL 8+)
 $ ---------------------------
 $ ======================================
 $ TOTAL
 $ 2

Create a new database user. Replace new_user_2, strong-password with your desired password

 $ vultr-cli database user create databaseId -u new_user_2 -p strong-password

Output:

 $ USERNAME      new_user_2
 $ PASSWORD      ************
 $ ENCRYPTION    Default (MySQL 8+)

View the list of available database users to verify the new user details

 $ vultr-cli database user list databaseId

Output:

 $ USERNAME      vultradmin
 $ PASSWORD      ************
 $ ENCRYPTION    Default (MySQL 8+)
 $ ---------------------------
 $ USERNAME      new_user
 $ PASSWORD      ************
 $ ENCRYPTION    Default (MySQL 8+)
 $ ---------------------------
 $ USERNAME      new_user_2
 $ PASSWORD      ************
 $ ENCRYPTION    Default (MySQL 8+)
 $ ---------------------------
 $ ======================================
 $ TOTAL
 $ 3

You have created a new MySQL database user using the Vultr CLI tool. You can create multiple users and grant them different privileges to access your database resources.

Manage Vultr Managed Database for MySQL User Privileges

By default, a Vultr Managed Database for MySQL includes a vultradmin user with administrative privileges to every database within the cluster. When you create new users using any method of your choice, you can either use the cluster control panel or the MySQL CLI tool to manager user privileges. For advanced user permission controls, use the MySQL CLI tool as described in the steps below.

Using the MySQL client tool, connect to your Vultr Managed Database for MySQL. Replace mydb.vultrdb.com,vultradmin,1234, with your actual database details
```
 $ mysql -h mydb.vultrdb.com -p 1234 -u vultradmin -p
```
When prompted, enter your Vultr Managed Database for MySQL password.

When connected, verify that you can access the MySQL console similar to the output below:
```
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 mysql> 
```

View User Privileges

View the available user privileges

 mysql> SHOW GRANTS;

Your output should look like the one below:

 +----------------------------------------------------------+
 | Grants for vultradmin@% |
 +----------------------------------------------------------+
 | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT,CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO "vultradmin"@"%" WITH GRANT OPTION |
 | GRANT REPLICATION_APPLIER,ROLE_ADMIN ON *.* TO "vultradmin"@"%" WITH GRANT OPTION|
 | REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON "metrics_user_telegraf".* FROM "vultradmin"@"%" |
 | REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON "mysql".* FROM "vultradmin"@"%" |
 | REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON "sys".* FROM "vultradmin"@"%" |
 +----------------------------------------------------------+
 5 rows in set (0.13 sec)

To view a specific user’s privileges, for example, new_user, use the SHOW GRANTS statement

 mysql> SHOW GRANTS FOR 'new_user';

Your output should look like the one below:

 +--------------------------------------------------------------------------------------------------------------------+
 | Grants for new_user@% |
 +--------------------------------------------------------------------------------------------------------------------+
 | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO "new_user"@"%" WITH GRANT OPTION |
 | GRANT REPLICATION_APPLIER,ROLE_ADMIN ON *.* TO "new_user"@"%" WITH GRANT OPTION |
 | REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON "metrics_user_telegraf".* FROM "new_user"@"%" |
 | REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON "mysql".* FROM "new_user"@"%" |
 | REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON "sys".* FROM "new_user"@"%" |
 +--------------------------------------------------------------------------------------------------------------------+
 5 rows in set (0.13 sec)

Grant User Privileges

To grant all privileges to a user on a specific database, use the GRANT ALL command. For example, grant new_user full privileges to the defaultdb database
```
 mysql> GRANT ALL ON defaultdb.* TO 'new_user'@'%';
```
Replace defaultdb.* with your actual MySQL database available in your cluster. To grant the user similar privileges to a table, increment the database with the table name. For example, defaultdb.posts
To grant another user GRANT privileges to allocate other users similar rights, include the option at the end of your SQL statement
```
 mysql> GRANT ALL ON defaultdb.* TO 'new_user_2'@'%' WITH GRANT OPTION;
```
The above statement assigns new_user_2 GRANT privileges on the defaultdb database. The user can manage other user privileges including the vultradmin user on the assigned database.

Grant a user SELECT privileges on a database

 mysql> GRANT SELECT ON defaultdb TO 'new_user'@'%';

To grant a user privileges on a specific table on a database, include the table name with the privilege:
- Grant the user user2 SELECT privileges to the posts table in the defaultdb database
```
  mysql> GRANT SELECT ON defaultdb.posts TO 'user2'@'%';
```
- Grant a user INSERT privileges on the posts table
```
  mysql> GRANT INSERT ON defaultdb.posts TO 'user2'@'%';
```
The above table-level privileges grant the user permissions to run the permissions on the target table within the database.

Revoke User Privileges

To revoke all user privileges on a database, for example, defaultdb, run the following command
```
 mysql> REVOKE ALL ON defaultdb.* FROM 'new_user'@'%';
```
The above SQL statement removes all privileges for the user new_user user on the defaultdb database.
To revoke INSERT privileges, apply the REVOKE INSERT permission
```
 mysql> REVOKE SELECT ON defaultdb.* TO 'new_user_2'@'%';
```
The above statement revokes INSERT privileges for the user new_user_2 on the defaultdb database.
To only allow READ privileges on a particular table, revoke the user's permissions, then grant your desired READ privileges. For example:
- Revoke all new_user_2 privileges on the posts table
```
  mysql> REVOKE ALL ON defaultdb.posts FROM 'new_user_2'@'%';
```
- Grant the user READ privileges on the table
```
  mysql> GRANT SELECT ON defaultdb.posts TO 'new_user_2'@'%';
```
The above SQL statements allow the user new_user_2 to read the posts table data but cannot perform any other operations in the defaultdb database.

Conclusion

You have created new users on a Vultr Managed Database for MySQL using the Vultr Customer Portal, the Vultr API, and the Vultr CLI tool. Using the MySQL CLI, you connected to the database and set up basic user privileges. This allows you to create additional users and assign different databases to a user. For more information, visit the Vultr Managed Databases for MySQL reference guide.

More Information

For more information, visit the following resources: