How to create users in MySQL

To create a new user in MySQL, you need sufficient administrator rights or superuser privileges that allow you to create user accounts and manage authorisations in addition to access to the respective database. You also need to know what type of access the new user requires, whether that’s read rights, write rights, or even administrative rights.

How to use MySQL’s CREATE USER command

When installing the database management system, MySQL automatically generates a root account. This account grants you comprehensive control over your databases, tables and users, allowing for efficient administration. If you need help with the installation process, our MySQL tutorial has all the essential information.

With your root account, you can create additional user accounts or new MySQL users and assign them authorisations. On Ubuntu systems with MySQL 5.7 or newer versions, the MySQL root user is configured by default to authenticate itself with the auth_socket plugin rather than a password. This means that if the name of the system user invoking the MySQL client differs from the name of the MySQL user specified in the command, you’ll need to prefix the command with sudo to gain access to your root account:

$ sudo mysql

To create a new user in MySQL, use the CREATE USER command. This allows you to create a user with a specific username and password:

mysql> CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

Replace username with a username of your choice. Under host, enter the name of the host where the new user can connect from. If the user should only be able to access the database from your local Ubuntu server, you can enter localhost.

When choosing the authentication plugin, you have several options. The auth_socket plugin offers high security by requiring users to enter a password for database access. However, it restricts remote connections, potentially requiring more effort for external programs to interact with MySQL. Alternatively, you can omit the WITH authentication_plugin part of the command to authenticate users using the MySQL standard plugin caching_sha2_password. This how the command would look like:

mysql> CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

Once you’ve created a new user, you need to assign authorisations to them.

How to assign rights to users in MySQL

The creation and management of user rights are essential for maintaining data security in MySQL. The general command for assigning user rights is:

mysql> GRANT PRIVILEGE ON database.table TO 'username'@'host';

The value PRIVILEGE determines which actions the user can perform in the specified database and table. You can replace this value with the following commands, among others:

• CREATE: Allows users to create a database or table
• SELECT: Allows users to retrieve data
• INSERT: Allows users to add new entries to tables
• UPDATE: Allows users to modify existing entries in tables
• DELETE: Allows users to delete table entries
• DROP: Allows users to drop entire database tables

You can also grant new users several privileges at once. When doing so, you need to separate the priveleges with a comma:

mysql> GRANT SELECT, INSERT, UPDATE ON database.table TO 'username'@'host';

Authorisations for all databases or tables can also be granted in a single command by entering * instead of the individual database and table names. For example, the following command gives a user the authorisation to query data (SELECT), to add new entries (INSERT) and to change existing entries (UPDATE) in all databases and tables.

mysql> GRANT SELECT, INSERT, UPDATE ON *.* TO 'username'@'host';

Once you’ve executed the CREATE USER or GRANT commands in MySQL, you can use the FLUSH PRIVILEGES command to update the database. This reloads the authorisation tables, ensuring that the new authorisations are put into effect:

mysql> FLUSH PRIVILEGES;

However, it’s important to only grant users the authorisations they need. If you give a user full control, this can pose a high security risk.

How to revoke user rights from users in MySQL

The REVOKE command is used to remove user rights in MySQL. The syntax is similar to that of the GRANT command. However, with this command, you need to use FROM instead of TO:

mysql> REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

To display the current authorisations that a user has, you can use the SHOW GRANTS command:

mysql> SHOW GRANTS FOR 'username'@'host';

You can use the DROP command to delete a user:

mysql> DROP USER 'username'@'localhost';

You should be extremely careful when deleting users, especially users with administrative privileges. Ensure you only remove users you really want to delete in order to avoid unintended data loss.

Once you’re done creating new MySQL users and granting them rights, you can exit the MySQL client:

mysql> exit