Understanding MySQL/MariaDB Users And Privileges: Part 1

MySQL and its fork MariaDB are powerful relational database engines that are used to run many of the websites around the world. Their complexity is increased by the seemingly confusing manner by which users are handled within them. This can be difficult for new users to understand. Therefore, in this article, we’ll have a look at user accounts to make them easier to understand.

Simplifying Users and Privileges

When it comes to user accounts, MySQL and MariaDB users consist of both the username and the host from which they will be connecting. Referencing a user in the database is done in the format of the username followed by the at symbol (@), and then the host they connect from. For example, when the database is installed, a default master user account is created with the username root. This user is restricted to only being able to access the database from the local computer. This is then referenced as ‘root’@’localhost’. On a Debian system. For example, the default installation makes 4 accounts:
* ‘root’@’localhost’
* ‘root’@’127.0.0.1’
* ‘root’@’::1′
* ‘root’@'<system-hostname>’
Obviously “<system-hostname>” in the last one will be replaced with the hostname you gave the system at the time that MySQL/MariaDB was installed. The reasons for the multiple accounts are due to the way
In which the database may be accessed. “localhost” references a UNIX socket connection on the local machine, “127.0.0.1” references a connection using the IPv4 loopback address, “::1” is for a connection using the IPv6 loopback address, and finally the system hostname would be used for a connection over a public interface where the hostname should resolve to the server’s public IP address.
The important thing to note here is that all four of these accounts are different in the eyes of the database. This is due to the fact that while the usernames are all the same, they would be connecting from different sources. Changing the password for ‘root’@’localhost’ would change the password for that one account, but none of the other three, their passwords would remain the same as before.

Creating Users

So with this in mind, when creating users for your own databases you will need to take into account the server from which you will be connecting to the database. Normally when your web server and the
database are on the same server, the usual method would be to create the users @’localhost’. If you have your database is on a different server to your web server, then your user would need to be @’the-web-servers-ip’.
Alternatively, if you wanted to create a single user that could connect from anywhere then you could use @’%’, which is a wildcard to match anything. It is worth noting here that this is a large security hole and will rely on you using your firewall to adequately protect your database. The host part of the user account is optional, and if it isn’t specified then the default option is to use ‘%’.
To manage your database users you’ll first need to log into the database, which can be done with the following command:
“`
mysql -u root -p
“`
This will prompt you for the password for the root user and leave you with a prompt similar to:
“`
mysql>
“`
Now we can run commands on the database, let’s look at creating a user on the database. This is done with the following command:
“`
CREATE USER ‘user’@’localhost’ IDENTIFIED BY ‘password’;
“`
As with the “user” and “localhost” parts for the user account, you can set “password” to be the password you want the user to log in with. If you want the password to auto-expire this can be done as follows:
“`
CREATE USER ‘user’@’localhost’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE INTERVAL 90 DAY;
“`
This would cause the user’s password to expire every 90 days and require them to set a new one. Note: use caution when using this with an account used by a web application, because if you forget to update the password you may find users complaining that the site doesn’t work when it can’t access the database.
Finally, the DROP command can be used to remove a user once the account is no longer needed:
“`
DROP USER ‘user’@’localhost’;
“`
That covers most of what you’ll need to know for the user’s side of things. Next time we’ll look at how the privileges work and how you can set them for a user.