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

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql(("MySQL")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["Database Functions and Data Types"]) mysql(("MySQL")) -.-> mysql/TransactionManagementandSecurityGroup(["Transaction Management and Security"]) mysql(("MySQL")) -.-> mysql/SystemManagementToolsGroup(["System Management Tools"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("External Code Execution") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/user("User Info Function") mysql/TransactionManagementandSecurityGroup -.-> mysql/identified_by("User Authentication") mysql/TransactionManagementandSecurityGroup -.-> mysql/grant_permission("Permission Granting") mysql/SystemManagementToolsGroup -.-> mysql/show_status("Status Overview") mysql/SystemManagementToolsGroup -.-> mysql/mysqladmin("Admin Utility") subgraph Lab Skills mysql/use_database -.-> lab-301430{{"Manage MySQL User Permissions"}} mysql/select -.-> lab-301430{{"Manage MySQL User Permissions"}} mysql/source -.-> lab-301430{{"Manage MySQL User Permissions"}} mysql/user -.-> lab-301430{{"Manage MySQL User Permissions"}} mysql/identified_by -.-> lab-301430{{"Manage MySQL User Permissions"}} mysql/grant_permission -.-> lab-301430{{"Manage MySQL User Permissions"}} mysql/show_status -.-> lab-301430{{"Manage MySQL User Permissions"}} mysql/mysqladmin -.-> lab-301430{{"Manage MySQL User Permissions"}} end

Start MySQL and Log In

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

Open a terminal on your system.
Run the following command to start the MySQL service:
```
sudo /etc/init.d/mysql start
```
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".

Create the manUser.sql file in the ~/project directory.
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.

Open the manUser.sql file.
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.
Finally, flush the privileges and make the changes effective:
```
FLUSH PRIVILEGES;
```

Verify the Solution

Run the SQL script in the MySQL prompt:

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

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)

✨ Check Solution and Practice

Summary

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