Knowledgebase

Introduction

MySQL is a high-performance open-source Relational Database Management System (RDBMS). Rcs 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 Rcs Managed Database for MySQL. You are to create new users for a deployed managed MySQL database using the cluster control panel, API, and the Rcs 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 Rcs Managed Database for MySQL

On your development machine:

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

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

The Rcs 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 Rcs Customer Portal account and create new users as described in the steps below.

1. Log in to your Rcs account using the Rcs Customer Portal

2. Click Products on the main navigation menu

3. Navigate to Databases

   

4. Select your existing Rcs Managed Database for MySQL to open the cluster dashboard

5. Within the Control Panel, click Users & Databases on the top menu

   

6. Verify that the default vultradmin user is available in the Users section

   

7. Click the Add New User button to set up a new MySQL database user

   

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

   

9. 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 Rcs API

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

Before you begin:

• Enable your API key in the Rcs 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

1. From your computer's applications menu, open Postman

2. Within the Postman interface, click the + create new collection button, and select Blank Collection from the list of options

3. Assign the new collection a name such as Rcs API, then click Variables on the collection menu bar

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

5. Navigate to the Authorization tab

6. Click the Type drop-down and select Bearer Token from the list of options

   

7. Enter your Rcs 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 Rcs API

To create a new user in a Rcs Managed Database for MySQL using Postman and your Rcs 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.

1. 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 Rcs API Key is correct and your IP Address is allowed to access the key.

   • Copy your target MySQL database id value to your clipboard

   

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

   

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

   

4. View the database users list again and verify that the new user is available

   

Create a New MySQL User using the Rcs CLI Tool

The Rcs CLI Tool grants you access to your Rcs 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.

1. Start a new Terminal or Windows PowerShell session to use the Rcs CLI tool

2. Export your Rcs 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.

3. View the list of available databases

    $ vultr-cli database list

   The above command lists all available databases attached to your Rcs 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

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

5. 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+)

6. 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 Rcs CLI tool. You can create multiple users and grant them different privileges to access your database resources.

Manage Rcs Managed Database for MySQL User Privileges

By default, a Rcs 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.

1. Using the MySQL client tool, connect to your Rcs 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 Rcs 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

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

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

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

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

3. Grant a user SELECT privileges on a database

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

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

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

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

3. 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 Rcs Managed Database for MySQL using the Rcs Customer Portal, the Rcs API, and the Rcs 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 Rcs Managed Databases for MySQL reference guide.

More Information

For more information, visit the following resources:

• Rcs Managed Databases Quickstart
• MySQL User Privileges