MySQL CREATE USER: create a user in MySQL

To be able to create a new user in MySQL, you must have, in addition to access to the database in question, sufficient administrator rights or user privileges. super user that allow you to create user accounts and manage permissions. You also need to know what type of access the new user needs: read rights, write rights, or even administrative rights.

When installing the database management system, MySQL creates a root account (root) that allows you to have full control over your databases, tables and users in order to manage them as an administrator. If you need help with the installation, you will find all the useful information on this subject in our tutorial dedicated to MySQL.

With your root account, you can create other user accounts or new MySQL users and assign them permissions. Note that on Ubuntu systems with MySQL 5.7 and later, the MySQL root user is configured by default to authenticate not with a password, but with the plugin auth_socket. To do this, the name of the system user that calls the MySQL client must match the name of the MySQL user specified in the command. This means that you must precede the command sudo to access your root account:

To create a new user in MySQL, use the command CREATE USER. 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 by the username of your choice. Then, indicate under host the respective name of the host from which the new user will connect. If the user should only have access from your local Ubuntu server, you can specify localhost.

When choosing the pluginauthenticationseveral possibilities are available to you: the plugin auth_socket provides high security, as users must enter a password to access the database. However, it prevents remote connections and can therefore cause more work if external programs need to interact with MySQL. You can also omit the part of the command WITH authentication_plugin for the user to authenticate via the standard MySQL plugin caching_sha2_password. In this case, the command is:

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

After creating a new user, you need to assign permissions to them.

Assigning User Rights with MySQL CREATE USER

Creating and managing user rights is essential for data security in MySQL. The general command for assigning user rights is:

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

The value PRIVILEGES determines what actions the user can perform in the specified database and table. You can override this value with the following commands:

• CREATE : Allows users to create a database or table.
• SELECT : allows users to recover 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 delete entire tables from the database.

Additionally, you can grant multiple privileges to the new user at once. To do this, they must be separated by a comma:

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

Permissions for all databases or tables can also be granted in a single command, by typing * instead of the database and table name. For example, the following command gives a user the right to query all databases and tables with SELECTto add new entries with INSERT and modify existing contributions with UPDATE.

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

After running the application GRANT Or CREATE USER In MySQL you can use the command FLUSH PRIVILEGES to update the database to reload the permission tables. This ensures that the new permissions are put into effect:

mysql> FLUSH PRIVILEGES;

However, you should only grant users the permissions they need. If you give a user full control, this can be a high security risk.

MySQL CREATE USER : remove user rights

To revoke user rights in MySQL, use the command REVOKE. The syntax is similar to that of the command GRANT. However, in this case you must use FROM instead of TO :

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

To view a user's current permissions, you can use the command SHOW GRANTS :

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

To delete a user, use the command DROP :

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

However, you should be careful when deleting users, especially if they are users with administrative privileges. Make sure not to make any mistakes in the deletion to avoid accidental data loss.

After creating a new MySQL user and granting it permissions, you can exit the MySQL client: