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
Before jumping into users in MySQL, we want you to be aware of the basics – users in databases should be treated just like any other user in any other piece of software. Databases are not an exception – in MySQL and other database management systems alike, users are used to run queries: before running a query, MySQL checks whether a certain user has the permission to run it, then proceeds accordingly: runs it and returns results or responds with an error. A query like so will provide us with a list of all users existing within MySQL – WHERE clauses can be used as well:
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
For now, we have provided you the basics – users have privileges, these privileges can often turn into roles. However, that’s far from the end – run a query like DESC user and you will indeed see that the privileges provided by MySQL are rather numerous:
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. |
The bottom line is this – the less privileges are assigned, the more secure your database will be. The same goes for roles (privilege collections) – to work with them, create a role (#1), grant it privileges (#2), then assign that role to a specific user (#3) – make sure to not go overboard when assigning privileges and you should be good to go:
Roles are only available in MySQL 8 and above, so even if you decide to not use them, there are a couple of other things you can do as well – one of them is always keeping in mind that the primary user within MySQL will still reside inside of your databases, and to further the security of your database, it can be renamed by running a query like so:
Of course, think of a better name than „notroot“, but you get the idea. Now the root user will be obfuscated and less simple to enumerate by attackers – to further the security of your user, consider putting its password inside of my.cnf like so – such an approach will let you log in to MySQL via the CLI without providing the password for the user (the password, if provided via the CLI in a traditional fashion, can be observed by observing the command history):
Once that’s done, feel free to log in to mysql using this approach (note that there’s no password):
$ mysql -unotroot [database_name]
Those who want to dive even deeper should consider creating accounts that can only access some part of their servers (see example below) and use them for management instead while keeping the root account obfuscated – a query like the one below will create a new_user and let it connect from any host in the example.org domain:
Protect your users with a secure password and these tricks will take your database further than you could imagine!
Partial Revokes & Other Tricks
- 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. -
Accounts can be locked by specifying the ACCOUNT LOCK keyword as well:
ALTER USER ‘username’ IDENTIFIED BY ‘password’ ACCOUNT LOCKwill 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.” -
Some may elect to limit the users within MySQL – to use limits, add them when creating a user like so:
Limiting users within MySQL -
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):
Creating proxy users in MySQL - 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.
Top comments (0)