Exercise: Configure permissions in MySQL

  • 20 minutes

In these lab exercises, you will assign RBAC roles to control access to Azure Database for MySQL resources and MySQL GRANTS to control access to database operations.

Important

You need your own Azure subscription to complete this exercise. If you do not have an Azure subscription, create an Azure free trial.

To complete these exercises, you must have:

  • Created a resource group (see Module 1, Unit 5).
  • Created an Azure Database for MySQL Flexible Server (see Module 1, Unit 5).
  • Downloaded and installed MySQL Workbench (see Module 2, Unit 5).
  • Created the zoodb database (see Module 2, Unit 5).

Tip

If you stopped your Azure Database for MySQL server after completing the last lab, restart it now.

Create a new user account in Microsoft Entra ID

  1. In the Azure portal, sign in using an Owner account and navigate to your Microsoft Entra ID.

  2. Under Manage, select Users.

  3. At the top-left, select New user and then select Create new user.

  4. In the New user page, enter these details and then select Create:

    • Username: Holly
    • Name: Holly Rees
    • Password: Select Let me create password and then type Pa$$w0rd.

    Tip

    When the user is created, make a note of the full User principal name so that you can use it later to log in.

Assign the Reader role

  1. In the Azure portal, select All resources and then select your Azure Database for MySQL resource.
  2. Select Access control (IAM) and then select Role assignments. Holly Rees doesn't appear in the list.
  3. Select + Add and then select Add role assignment.
  4. Select the Reader role, and then select Next.
  5. Add Holly Rees to the list of members and then select Next.
  6. Select Review + Assign.

Test the Reader role

  1. In the top-right of the Azure portal, select your user account and then select Log out.
  2. Log in as the new user, with the user principal name that you noted and the password Pa$$w0rd. Replace the default password if you are prompted to and make a note of the new one.
  3. In the portal home page, select All resources and then select your Azure Database for MySQL resource.
  4. Select Stop. An error is displayed, because the Reader role enables you to see the resource but not change it.

Assign the Contributor role

  1. In the top-right of the Azure portal, select Holly's user account and then select Log out.
  2. Log in using your original Owner account.
  3. Navigate to your Azure Database for MySQL resource, and then select Access Control (IAM).
  4. Select + Add and then select Add role assignment.
  5. Select the Contributor role, and then select Next.
  6. Add Holly Rees to the list of members and then select Next.
  7. Select Review + Assign.
  8. Select Role Assignments. Holly Rees now has assignments for both Reader and Contributor roles.

Test the Contributor role

  1. In the top-right of the Azure portal, select your user account and then select Log out.
  2. Log in as the Holly Rees, with the user principal name and password that you noted.
  3. In the portal home page, select All resources and then select your Azure Database for MySQL resource.
  4. Select Stop and then select Yes. This time, the server stops without errors because Holly has the necessary role assigned.
  5. Select Start to ensure that the MySQL resource is ready for the next steps.
  6. In the top-right of the Azure portal, select Holly's user account and then select Log out.
  7. Log in using your original Owner account.

GRANT access to Azure Database for MySQL

  1. Open MySQL Workbench and connect to your Azure Database for MySQL server.

  2. In the query pane, execute this code. Six user accounts should be returned, including the demo account that you're using to connect:

     SELECT user, host FROM mysql.user;

  3. To create a new user, execute this code

     CREATE USER 'dbuser'@'%' IDENTIFIED BY 'Pa$$w0rd';

  4. To list the new user, execute the above SELECT query again. You should see the dbuser@% user listed.

  5. To enable the new user to query and modify data in all tables in the zoodb database, execute this code:

     GRANT SELECT, INSERT, UPDATE, DELETE ON zoodb.* TO 'dbuser'@'%';
 FLUSH PRIVILEGES;

Test the new user

  1. In MySQL Workbench, on the Database menu, select Manage Connections.

  2. Select the existing connection to Azure Database for MySQL, and then select Duplicate.

  3. In the duplicated connection, in the Username textbox, type dbuser.

  4. Next to Password, select Store in Vault.

  5. In the Password textbox, type Pa$$w0rd and then select OK.

    Screenshot showing the new user account in the Manage Connections dialog in MySQL Workbench.

  6. Select Test Connection. If the connection is successful, select OK and then select Close.

  7. On the Database menu, select Connect to Database.

  8. In the Connect to Database dialog, select the new connection, and then select Connect.

  9. To test whether you have the SELECT privilege, in the query window, execute this code:

     SELECT * FROM animal;

  10. To test whether you have the UPDATE privilege, execute this code:

     UPDATE animal SET Name = "Linda Lioness" WHERE AnimalID = "6";
 SELECT * FROM animal;

  11. To test whether you have the DROP privilege, execute this code. If there is an error, examine the error code:

     DROP TABLE animal;

  12. To test whether you have the GRANT privilege, execute this code:

     GRANT ALL PRIVILEGES ON zoodb.* TO 'dbuser'@'%';

These tests demonstrate that the new user can execute Data Manipulation Language (DML) commands to query and modify data but can't use Data Definition Language (DDL) commands to change the schema. Also, the new user can't GRANT any new privileges to circumvent the permissions.