How to create users in MySQL

To create a new user in MySQL, you need suf­fi­cient ad­min­is­tra­tor rights or superuser priv­i­leges that allow you to create user accounts and manage au­tho­riza­tions in addition to access to the re­spec­tive database. You also need to know what type of access the new user requires, whether that’s read rights, write rights, or even ad­min­is­tra­tive rights.

How to use MySQL’s CREATE USER command

When in­stalling the database man­age­ment system, MySQL au­to­mat­i­cal­ly generates a root account. This account grants you com­pre­hen­sive control over your databases, tables and users, allowing for efficient ad­min­is­tra­tion. If you need help with the in­stal­la­tion process, our MySQL tutorial has all the essential in­for­ma­tion.

With your root account, you can create ad­di­tion­al user accounts or new MySQL users and assign them au­tho­riza­tions. On Ubuntu systems with MySQL 5.7 or newer versions, the MySQL root user is con­fig­ured by default to au­then­ti­cate 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 au­then­ti­ca­tion 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 con­nec­tions, po­ten­tial­ly requiring more effort for external programs to interact with MySQL. Al­ter­na­tive­ly, you can omit the WITH authentication_plugin part of the command to au­then­ti­cate 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 au­tho­riza­tions to them.

How to assign rights to users in MySQL

The creation and man­age­ment of user rights are essential for main­tain­ing data security in MySQL. The general command for assigning user rights is:

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

The value PRIVILEGE de­ter­mines 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 priv­i­leges at once. When doing so, you need to separate the priv­eleges with a comma:

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

Au­tho­riza­tions for all databases or tables can also be granted in a single command by entering * instead of the in­di­vid­ual database and table names. For example, the following command gives a user the au­tho­riza­tion 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 au­tho­riza­tion tables, ensuring that the new au­tho­riza­tions are put into effect:

mysql> FLUSH PRIVILEGES;

However, it’s important to only grant users the au­tho­riza­tions 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 au­tho­riza­tions 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, es­pe­cial­ly users with ad­min­is­tra­tive priv­i­leges. Ensure you only remove users you really want to delete in order to avoid un­in­tend­ed data loss.

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

mysql> exit