MySQL List Users: View All Users in MySQL
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.
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’;|
Last Updated April 2022
Learn SQL database querying w/ MySQL Workbench. Perfect for SQL beginners and first-time coders. | By Maven Analytics, John PaulerExplore 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:
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:
- Delete any unused users. When users are removed from the company, they should be removed from the system.
- Restrict permissions as is prudent. If a user doesn’t need to be able to update the database, they shouldn’t be able to update the database.
- Enforce password protocol. Passwords should be unique, complex, and lengthy; make sure that passwords are properly set.
- Delete any test accounts. Test accounts often present a vulnerability as attackers know that the test accounts exist.
- Never use wildcards in the hostname. Always set the hostname specifically to the hostname that you expect.
- Audit your accounts frequently. Make sure all the passwords have been properly set and that there aren’t any suspicious settings.
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.
Top courses in MySQL
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 for Business.