Properly managing users is crucial for any DBA – in this blog, we‘ll tell you how to propely manage users in a MySQL relational database system. Have a read!

Users are an absolutely necessary part of any database management system, and MySQL is no exception. Properly managing users within your MySQL database can be a matter of life and death – and while the main user provided by MySQL (the “root” user) has all privileges and is essentially the “God” in all aspects, there are multiple other things you should know to properly work with and manage your databases. Let’s roll!

Database User Management Explained

MySQL Users Explained

As you can probably tell, MySQL comes with three default users – these are the mysql.session, mysql.sys, and root users. These three users within MySQL are reserved for special use cases:

The mysql.session user is used by plugins to access MySQL.
The mysql.sys user is used to avoid problems should anyone rename or remove the root account (the root account can be renamed for security purposes.)
The root user is the principal user within MySQL – it has godlike privileges across all databases and tables. This user is used to create other users, grant them privileges, as well as to complete other administrative tasks.
Besides those users, MySQL will also list all other users that were created by the user itself (see example above.) It’s nothing revolutionary, really – DBAs grant users privileges, and those privileges are then used to work with data. The more privileges are assigned, the greater the power of the user.

MySQL 8 also has these things called roles – roles are essentially collections of privileges that are assigned to a specific user. The more privileges a role has, the more powerful the user.

Working with Users and Privileges in MySQL

That’s because MySQL has thought of everything – from the users being able to show databases to work with files inside set directories and locking tables: there are privileges for everything. And that’s not without a reason, either – developers often are advised against working with their databases by using the superuser provided by MySQL. That’s because if the password of that user gets compromised, we can say goodbye to our databases and everything within – to add to that, the root user has many privileges that are simply not necessary to complete tasks related to daily DBA work, so instead, DBAs and devs turn to privileges. Here’s what you need to know about those on a high level:

| Security Level | Set These Privileges                                                                                                                                                                             |
|----------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| High           | CRUD (INSERT, SELECT, UPDATE, DELETE)                                                                                                                                                            |
| Medium         | CRUD and Alter, Index, Reload, and Shutdown privileges                                                                                                                                           |
| Low            | CRUD, Alter, Index, Reload, and Shutdown privileges, the FILE privilege (allows to insert data into MySQL from a file), the GRANT privilege if there’s a necessity to grant roles to users, etc. |

Once that’s done, feel free to log in to mysql using this approach (note that there’s no password):

$ mysql -unotroot [database_name]

Protect your users with a secure password and these tricks will take your database further than you could imagine!

Partial Revokes & Other Tricks

1. MySQL 8 introduced many features that would have been otherwise unknown – one of those features are partial revokes which is a variable that controls whether users can partially revoke privileges – by default, this privilege is OFF, which means that if we grant 2 or more privileges and then try to revoke a part of them, we will face an error like the following: ERROR 1141 (42000): There is no such grant defined for user ‘demo’ on host ‘%’ If we run a query like SET PERSIST partial_revokes = ON, we will no longer face such an error and we will enable ourselves to partially revoke privileges.
2. Accounts can be locked by specifying the ACCOUNT LOCK keyword as well: ALTER USER ‘username’ IDENTIFIED BY ‘password’ ACCOUNT LOCK will lock the account and any attempts to access data via that account will produce an error saying “Access denied for user ‘username’@’host_name’. Account is locked.”
3. Some may elect to limit the users within MySQL – to use limits, add them when creating a user like so:

   

4. Some users may elect to use proxy users – these accounts can’t be logged in directly, but have privileges (do note that the creation of such users is only available in MySQL 8 and above – attempting to use proxy users on other versions will produce an error):

   

5. MySQL also gives us the possibility of logging account activity specific to certain accounts. This is known as SQL-based account activity logging and can be used to audit what certain users do in a database. This feature is detailed in the MySQL documentation.

After your users are secured with a strong password and you keep the tips given above in mind, always abide by security best practices, avoid re-using passwords in more than one place, and you should be good to go!

We hope that this blog has taught you something new in the database space, make sure to explore the blog of DbVisualizer to learn more about databases and their functionality in the future, and until next time!

FAQs

What Is User Management in MySQL?

In MySQL, user management refers to the tools and processes used to manage users who work with data in the databases and tables within MySQL and its flavors.

How Do I Secure Users in MySQL?

Use strong passwords, consider using the security plugins provided by MySQL, and make sure to use proper privileges. For maximum security, consider using SQL clients like DbVisualizer as they come with multiple features relevant to database security, as well as data breach search engines like BreachDirectory to check if you or anybody within your team is at risk of identity theft now or in the foreseeable future.

Where Can I Learn More About MySQL Security?

You can learn more about the security measures MySQL takes to secure its users over at its documentation or on our blog called TheTable.

About the author

Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.