Much of the MySQL management and administration data is also stored in databases and tables. An excellent example is the user list, accessed through the MySQL list users function. If you want to know how many users you have as well as who they are, you can make MySQL list users with a simple select.

person in front of screens coding

MySQL list users: view users in MySQL

Administrative data for MySQL is kept within the MySQL database. The data is held within the “user” table for MySQL users. To look at an individual user account, use a selection command like the following:

select * from user where user = ‘root’;
SQL for Data Analysis: Beginner MySQL Business Intelligence

Last Updated August 2022

Bestseller
  • 103 lectures
  • All Levels
4.7 (6,452)

Learn SQL database querying w/ MySQL Workbench. Perfect for SQL beginners and first-time coders. | By Maven Analytics, John Pauler

Explore Course

This pulls up all the information about the root user account. You can also view all the users in MySQL as follows:

select * from user;

This returns all the users in this particular MySQL server. Note that these are users attached to the server itself — each user account that has access to this server machine. These users can have permissions to all databases or just specific databases. 

You can also select specific information: 

select host from user;

This will let you select user host information rather than all the information from the table. Apart from using select to show MySQL user information, you can also use INSERT, UPDATE, and DELETE functions on the database server as normal.

Add a user to MySQL

Let’s say that you look at the MySQL user list, and it doesn’t have the user that you’re looking for. You need to create another user. At a minimum, it is general practice to create a new user for each database, assuming that each database is used for a different application.

First, go into the MySQL database and then use the following commands:

CREATE USER ‘name’@’host’ IDENTIFIED BY ‘password’;

Now, note that you aren’t just using an “INSERT INTO” command on the users’ database; rather, you’re using an internal administrative MySQL command. For one, the above command is going to encrypt the password rather than just setting it to ‘password.’

Next, add the permissions;

GRANT ALL PRIVILEGES ON database TO ‘user’@’host’;

When you’re manipulating the USER database, you don’t always need to include the hostname, but you should include it when you’re using administrative functions. Usually, this will be your own machine: localhost. However, it won’t always be your own machine.

Always use CREATE USER to create users rather than trying to insert directly into the USER table. 

Use MySQL to reset a user password

As noted, you can manipulate the user database directly. So, let’s say you want to update the user password.

UPDATE user SET authentication_string = PASSWORD(“Password”) WHERE user = ‘root’;

The above string would reset the password for the root user, something that you generally don’t want to do unless you know what you’re doing. Note that you need to use the PASSWORD function, as otherwise, the password won’t be properly encrypted. The next time you try to log in to the MySQL account with the root user, you’d use ‘Password.’

There are administrative functions for managing MySQL users as well. If you wanted to use the MySQL server commands from the command line, you’d instead do this:

ALTER USER ‘root’ IDENTIFIED BY ‘Password’;

This would be entered in as any other MySQL commands for administration.

Updating MySQL user privileges

Now you know how to show users by using the “select user from MySQL.user” trick. But probably more frequently, you won’t need to show users but rather update them.

In MYSQL, you update priv settings with the GRANT function:

GRANT ALL ON database TO ‘user’@’host’;

The above grants ALL permissions on a database named “database” to a user named “user.” An alternative is:

GRANT SELECT ON database TO ‘user’@’host’;

Under the above permissions, the user is able to select documents but not insert or delete them — the equivalent of a “read/write” user. This could be incredibly useful for applications that should be able to access data but should not be able to overwrite it.

Privileges can likewise be revoked:

REVOKE ALL ON database TO ‘user’@’host’;

Now, the above user won’t be able to access this database at all, even to just read the information. You can view the privileges like so:

SHOW GRANTS;

The above will give you a complete list of permissions for your user accounts so you can audit them. In general, each user account should only have the access that it strictly needs. While a root account will usually have access to everything, every other account should be restricted to the data that it requires.

MySQL user best practices

User management is a matter of security. Many data breaches or disruptions occur because user accounts haven’t been properly audited. A few MySQL user best practices include:

It’s the role of the database administrator to enforce these best practices and to regularly audit the database.

Learning more about MySQL

MySQL makes it easy to administer MySQL servers and MySQL databases. You can go in through the MYSQLADMIN console to administer the database or administer user accounts and other settings directly through MySQL’s built-in databases and tables. For users, the table you need to reference is the “user” table — this will provide all the information you need regarding MySQL users.
Start learning about other functions (such as MYSQL ENUM) or MySQL alternatives with Udemy’s MySQL Courses today.

Page Last Updated: April 2022

Top courses in MySQL

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (73,048)
Bestseller
SQL for Data Analysis: Beginner MySQL Business Intelligence
Maven Analytics, John Pauler
4.7 (6,452)
Bestseller
MySQL Database Administration: Beginner SQL Database Design
Maven Analytics, John Pauler
4.6 (1,963)
Bestseller
MySQL Course for Beginners with 100+ examples
Studyopedia Trainings
4.5 (28)
Advanced SQL: MySQL Data Analysis & Business Intelligence
Maven Analytics, John Pauler
4.7 (3,652)
Bestseller
The Complete MySQL Developer Course
Rob Percival, Mashrur Hossain, Codestars • over 2 million students worldwide!
4.5 (3,292)

More MySQL Courses

MySQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.

Request a demo