Number of Users with Modify Permissions

Introduction

In this project, you will learn how to retrieve the number of users with modify permissions from the user table in a MySQL database.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to start and log in to the MySQL terminal
• How to switch to the mysql database and query the number of users with modify permissions
• How to save the SQL statement to a file
• How to run the SQL script to display the result

🏆 Achievements

After completing this project, you will be able to:

• Understand how to interact with a MySQL database using the MySQL terminal
• Write SQL queries to retrieve specific information from database tables
• Save SQL statements to a file and execute them
• Apply your MySQL knowledge to solve real-world problems involving user permissions

Start MySQL and Log In

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

1. Create the showNum.sql file in the ~/project directory:

   touch ~/project/showNum.sql
   

2. Start MySQL:

   sudo /etc/init.d/mysql start
   

3. Log into the MySQL terminal:

   mysql -uroot
   

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

Switch to the MySQL Database and Query the Number of Users with Modify Permissions

In this step, you will learn how to switch to the mysql database and query the number of users with modify permissions.

1. Switch to the mysql database:

   USE mysql;
   

2. Query the number of users with modify permissions:

   SELECT COUNT(*)
   FROM mysql.user
   WHERE Update_priv = 'Y';
   

   This query will return the number of users with modify permissions.

Save the SQL Statement to a File

In this step, you will learn how to save the SQL statement to a file.

1. Open the showNum.sql file in a text editor and add the following SQL statement:

   USE mysql;
   SELECT COUNT(*)
   FROM mysql.user
   WHERE Update_priv = 'Y';
   

   Save the file.

Run the SQL Script

In this step, you will learn how to run the SQL script.

1. In the MySQL terminal, run the showNum.sql script:

   SOURCE ~/project/showNum.sql;
   

   This will execute the SQL statement in the showNum.sql file and display the result.

   Example output:

   MySQL [mysql]> SOURCE ~/project/showNum.sql;
   +----------+
   | count(*) |
   +----------+
   |        2 |
   +----------+
   1 row in set (0.000 sec)
   

   The output shows that there are 2 users with modify permissions in the mysql database.

Summary

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