Knowledgebase

How to Manage Users in Rcs Managed Databases for MySQL Print

  • 0

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:

On your development machine:

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

    Managed 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

    Users & Databases

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

    Default Users & Databases

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

    Add New 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

    New User Created

  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

    Add env variables for collection

    • 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

    Set Up the Collection Authoration

  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:

    Database List API URL

    • Click Send to generate a response with an array of available databases

    Database List

    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

    Database ID

  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.

    User List for Database

  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

    Create User Request

    • Click Send to create the new user and verify your username, password and password encryption values

    New User Created

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

    User List with new user

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:

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. Log in to your Rcs account using the Rcs Customer Portal Click Products on the main navigation menu Navigate to Databases Select your existing Rcs 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 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 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 Rcs 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: Navigate to the Authorization tab Click the Type drop-down and select Bearer Token from the list of options 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. 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 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 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. Start a new Terminal or Windows PowerShell session to use the Rcs CLI tool Export your Rcs API key as an environment variable $ export VULTR_API_KEY= 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 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 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 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. 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 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 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

Was this answer helpful?
Back

Powered by WHMCompleteSolution