To create a new user in MySQL, you need suf­fi­cient ad­min­is­trat­or rights or superuser priv­ileges that allow you to create user accounts and manage au­thor­isa­tions in addition to access to the re­spect­ive 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­trat­ive rights.

How to use MySQL’s CREATE USER command

When in­stalling the database man­age­ment system, MySQL auto­mat­ic­ally generates a root account. This account grants you com­pre­hens­ive control over your databases, tables and users, allowing for efficient ad­min­is­tra­tion. If you need help with the in­stall­a­tion process, our MySQL tutorial has all the essential in­form­a­tion.

With your root account, you can create ad­di­tion­al user accounts or new MySQL users and assign them au­thor­isa­tions. On Ubuntu systems with MySQL 5.7 or newer versions, the MySQL root user is con­figured by default to au­then­tic­ate 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
bash

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';
bash

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­tic­a­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­tially requiring more effort for external programs to interact with MySQL. Al­tern­at­ively, you can omit the WITH authentication_plugin part of the command to au­then­tic­ate users using the MySQL standard plugin caching_sha2_password. This how the command would look like:

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

Once you’ve created a new user, you need to assign au­thor­isa­tions to them.

Cheap domain names – buy yours now
  • Free website pro­tec­tion with SSL Wildcard included
  • Free private re­gis­tra­tion for greater privacy
  • Free Domain Connect for easy DNS setup

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';
bash

The value PRIVILEGE de­term­ines 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­ileges 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';
bash

Au­thor­isa­tions for all databases or tables can also be granted in a single command by entering * instead of the in­di­vidu­al database and table names. For example, the following command gives a user the au­thor­isa­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';
bash

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­thor­isa­tion tables, ensuring that the new au­thor­isa­tions are put into effect:

mysql> FLUSH PRIVILEGES;
bash

However, it’s important to only grant users the au­thor­isa­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';
bash

To display the current au­thor­isa­tions that a user has, you can use the SHOW GRANTS command:

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

You can use the DROP command to delete a user:

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

You should be extremely careful when deleting users, es­pe­cially users with ad­min­is­trat­ive priv­ileges. Ensure you only remove users you really want to delete in order to avoid un­in­ten­ded data loss.

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

mysql> exit
bash

45d969e266677cabb44ceaf95418e879

bf812055a1ab3935cb15b43974c56856

01524320532809020958f4b5d042b02d

1b67cfdd89611352c3472ca463d3de9f

26285f4721b20b07e60196b9449d21e7

b7f8469a4592c8d2235cdd9e51f76293

Go to Main Menu