Audit User Accounts from MySQL System

Show All Users from the MySQL System Database

Write a MySQL query to list all users from the MySQL system database.

Solution:

-- This command queries the mysql.user system table to list all database users
-- Selects the User (username) and Host (connection origin) columns
SELECT User, Host FROM mysql.user; 

Explanation:

• Purpose of the Query:
• The goal is to display all user accounts configured on the MySQL server.
• This demonstrates how to query the system table for auditing purposes.
• Key Components:
• SELECT User, Host FROM mysql.user : Retrieves the username and host for each user account.
• Real-World Application:
• Useful for security audits and monitoring user access on the server.

Notes:

• Accessing the mysql.user table requires administrative privileges.
• Handle the retrieved data carefully to maintain user confidentiality.

For more Practice: Solve these Related Problems:

• Write a MySQL query to list all users along with their host information from the mysql.user table.
• Write a MySQL query to display distinct usernames and their allowed hosts from the MySQL system database, sorted alphabetically.
• Write a MySQL query to retrieve the count of user accounts grouped by host from the mysql.user table.
• Write a MySQL query to show all user accounts from the MySQL system database and filter out system accounts.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Revoke All Privileges from a User.
Next MySQL Exercise: Grant Execution Privilege on Stored Procedures.