One of the most commonly asked, MySQL-related questions according to Google, is: “How to create a user?” We would like to answer this question in this short blog post. So, how to do it? Of course, there are many software tools that can make it easier for you, for example MySQL Workbench, we will focus on the command line client.
Users in MySQL
Let’s start with a quick introduction of users in MySQL 8.0. To access the database a user has to be defined. Initially it’s a root user, which has full access to everything. For the application you want to create another user with as limited set of privileges as possible. Users can have granted different privileges. Users can be allowed to log in from a particular IP address or hostname. Users can also be limited to a particular subnet, although it is not granular as it should be. Users can have roles assigned to them – a role is a set of privileges that can be assigned to multiple users while privileges can be modified in one place (role) and have effect on every user that has such a role assigned. Users can have resource limits assigned to them – queries per hour, updates per hour etc.
How to create a user in MySQL 8.0?
This is a two-step process. First, you have to create a user by deciding on its name, host from where it can connect and password.
mysql> CREATE USER someuser@'10.0.0.1' IDENTIFIED BY 'somepassword';
Query OK, 0 rows affected (0.11 sec)
In this case we have created a user ‘someuser’ that can connect from host 10.0.0.1 using ‘somepassword’ as a password.
As a second step we have to assign required privileges to the user:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO someuser@'10.0.0.1';
Query OK, 0 rows affected (0.04 sec)
Privileges may be granted on all tables or on a particular schema or table:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON myschema.* TO someuser@'10.0.0.1';
Query OK, 0 rows affected (0.04 sec)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON myschema.mytable TO someuser@'10.0.0.1';
Query OK, 0 rows affected (0.04 sec)
For cases where multiple users should have the same privileges you can create and then assign roles:
mysql> CREATE ROLE 'dbsre';
Query OK, 0 rows affected (0.09 sec)
mysql> GRANT ALL ON *.* to dbsre;
Query OK, 0 rows affected (0.07 sec)
Such roles can be assigned to users, in that case role’s privileges will be assigned to the user.
mysql> CREATE USER roleuser@'localhost' IDENTIFIED BY 'somepassword';
Query OK, 0 rows affected (0.06 sec)
mysql> GRANT 'dbsre' TO roleuser@localhost;
Query OK, 0 rows affected (0.05 sec)
Changing password format to mysql_native_password
MySQL 8.0 comes with a new safer password format, caching_sha2_password. It is not backwards compatible and some old applications or libraries may have troubles using the new format. In that case you can create a new user and explicitly ask to use old password hashing mechanism:
mysql> CREATE USER 'oldpassuser'@'somehost' IDENTIFIED WITH mysql_native_password BY 'oldpassword';
Query OK, 0 rows affected (0.06 sec)
If needed, you can always modify existing user to use different password stored in a format of your choice:
mysql> ALTER USER 'oldpassuser'@'somehost' IDENTIFIED WITH caching_sha2_password BY 'newpassword';
Query OK, 0 rows affected (0.06 sec)
As you can see, creating users in MySQL is not complex, although if you are looking for advanced options, you can find some like random password generation, password expiration, account locking after unsuccessful login attempts, resource limiting and so on. If you are interested in more details, MySQL documentation can be of a great help.
