If you are a new MySQL Database, you must know the commands to get a complete list of all the current Database users. Although we can easily list the Databases on our system using the “SHOW DATABASE” command, there is no MySQL command like “SHOW USERS” to list the available users. That’s why it becomes essential for database administrators to be familiar with different commands to query MYSQL, including those we are going through in this tutorial, for better database management.
Step 1: Access the MySQL Command Line
To run different queries on MySQL, we must first log in as the root user to access its command line. On your Linux, Windows, or MacOS, where you have installed the MYSQL Database server, open the terminal and execute the given command. When the system asks for the root user password, enter it and hit the Enter key.
mysql -u root -pStep 2: Show All Users
Immediately after logging in and getting MySQL Database command-line access as the root user, you can run the given SQL query to display all Database users. The command will list all the users and even their associated hostnames.
SELECT User, Host FROM mysql.user;
Step 3: To Get Detailed User Information
Using the previous step’s command, we can only have a limited amount of details about existing database users; however, if we want to retrieve additional fields, such as user privileges, run the given command or query:
SELECT User, Host, authentication_string, plugin, password_expired FROM mysql.user;
How to Filter MySQL Users
So far, we have learned how to list the users, but what if we want to filter users based on specific criteria? We can use the given query and modify it according to our requirements; for example, if we you want to find users just from localhost or any other particular host:
SELECT User, Host FROM mysql.user WHERE Host='localhost';Additional Tips for User Management
1. Creating a New User
To create a new user in MySQL, use the CREATE USER statement. Here’s an example:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';2. Granting Privileges
To grant privileges to a user, use the GRANT statement. For example, to grant all privileges on a specific database:
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';3. Revoking Privileges
To revoke privileges from a user, use the REVOKE statement:
REVOKE ALL PRIVILEGES ON database_name.* FROM 'newuser'@'localhost';4. Removing a User
To remove a user from MySQL, use the DROP USER statement:
DROP USER 'newuser'@'localhost';5. Viewing User Privileges
To view the privileges assigned to a user, you can query the mysql.user table or use the SHOW GRANTS command:
SHOW GRANTS FOR 'username'@'host';Other Articles:
