MySQL How To Create a New User and Grant Permissions

Creating a New User

SSH to your Ubuntu system and login to MYSQL instance using root user

mysql -u root -p

Once you have gained access to the MYSQL, add your user using CREATE USER command

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

Run the following command to create a user that authenticates with caching_sha2_password. Be sure to change sammy to your preferred username and password to a strong password of your choosing:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Granting a User Permissions

general syntax for granting user privileges

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

You can find the full list of available privileges in the official MySQL documentation.

Some users might choose to grant the ALL Access privilege to their MySQL user, which will give them extensive superuser privileges similar to those of the root user, as in the following example:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

Many guides suggest running the FLUSH PRIVILEGES command immediately after a CREATE USER or GRANT statement in order to reload the grant tables to ensure that the new privileges are put into effect:

FLUSH PRIVILEGES;

The FLUSH PRIVILEGES command isn’t required in our situation since, in accordance with the official MySQL documentation, the database will immediately reload the grant tables into memory when you indirectly edit the grant tables using an account management statement like GRANT. On the other side, running it won’t harm the system in any way.

The format for revoking a permit is quite similar to giving one:

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

You can review a user’s current permissions by running the SHOW GRANTS command:

SHOW GRANTS FOR 'username'@'host';

Deleting a user from MYSQL Database

DROP USER 'username'@'localhost';