Manage MySQL User Permissions

Introduction

In this project, you will learn how to manage user permissions in a MySQL database. Specifically, you will create a new local user named "Rong" and grant them access to the performance_schema database.

👀 Preview

MariaDB [(none)]> SHOW GRANTS FOR Rong@localhost;
+-------------------------------------------------------------------------------------------------------------+
| Grants for Rong@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `Rong`@`localhost` IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT ON `performance_schema`.* TO `Rong`@`localhost`                                                |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

🎯 Tasks

In this project, you will learn:

• How to start the MySQL server and log in to the MySQL terminal
• How to create a new local user with a specific password
• How to grant a user access to a specific database and its tables

🏆 Achievements

After completing this project, you will be able to:

• Manage user accounts and permissions in a MySQL database
• Understand the process of granting and revoking database access for users
• Apply these skills to set up user permissions in your own MySQL-based applications

Start MySQL and Log In

In this step, you will learn how to start the MySQL server and log in to the MySQL terminal.

1. Open a terminal on your system.

2. Run the following command to start the MySQL service:

   sudo /etc/init.d/mysql start
   

3. Log in to the MySQL terminal:

   mysql -uroot
   

   You should now be in the MySQL terminal, where you can enter MySQL commands.

Create a Local User

In this step, you will learn how to create a new local user named "Rong" with the password "123456".

1. Create the manUser.sql file in the ~/project directory.

2. Add the following code to the file to create the user:

   CREATE USER 'Rong'@'localhost' IDENTIFIED BY '123456';
   

   This creates a new user named "Rong" with the password "123456" and allows the user to log in from the local machine (localhost).

Grant Access to the Performance_schema

In this step, you will learn how to grant the "Rong" user access to the performance_schema database.

1. Open the manUser.sql file.

2. Grant the necessary permissions after the code added in the previous step:

   GRANT SELECT ON performance_schema.* TO 'Rong'@'localhost';
   

   This grants the "Rong" user the SELECT permission on all tables in the performance_schema database.

3. Finally, flush the privileges and make the changes effective:

   FLUSH PRIVILEGES;
   

Verify the Solution

1. Run the SQL script in the MySQL prompt:

SOURCE /home/labex/project/manUser.sql;

2. Verify the results by running the following command in the MySQL terminal:

SHOW GRANTS FOR Rong@localhost;

This should display the grants for the "Rong" user, including the SELECT permission on the performance_schema database.

MariaDB [(none)]> SHOW GRANTS FOR Rong@localhost;
+-------------------------------------------------------------------------------------------------------------+
| Grants for Rong@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `Rong`@`localhost` IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT ON `performance_schema`.* TO `Rong`@`localhost`                                                |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.