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/TransactionManagementandSecurityGroup(["`Transaction Management and Security`"]) mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"]) mysql(("`MySQL`")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["`Database Functions and Data Types`"]) sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"]) sql(("`SQL`")) -.-> sql/DataManipulationandQueryingGroup(["`Data Manipulation and Querying`"]) sql(("`SQL`")) -.-> sql/DatabaseManagementandOptimizationGroup(["`Database Management and Optimization`"]) mysql/TransactionManagementandSecurityGroup -.-> mysql/identified_by("`User Authentication`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/user("`User Info Function`") sql/BasicSQLCommandsGroup -.-> sql/select("`SELECT statements`") sql/DataManipulationandQueryingGroup -.-> sql/in("`IN clause`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("`Data Retrieval`") mysql/TransactionManagementandSecurityGroup -.-> mysql/grant_permission("`Permission Granting`") sql/DatabaseManagementandOptimizationGroup -.-> sql/security_permissions("`Security and Permissions`") subgraph Lab Skills mysql/identified_by -.-> lab-301430{{"`Manage MySQL User Permissions`"}} mysql/source -.-> lab-301430{{"`Manage MySQL User Permissions`"}} mysql/user -.-> lab-301430{{"`Manage MySQL User Permissions`"}} sql/select -.-> lab-301430{{"`Manage MySQL User Permissions`"}} sql/in -.-> lab-301430{{"`Manage MySQL User Permissions`"}} mysql/select -.-> lab-301430{{"`Manage MySQL User Permissions`"}} mysql/grant_permission -.-> lab-301430{{"`Manage MySQL User Permissions`"}} sql/security_permissions -.-> 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