Create New User in MySQL & Grant Access - Complete Guide



Creating a new user in MySQL and granting privileges is very easy. First, you have to open the MySQL command line.

1.Open MySQL

You can do this by reaching the bin directory from Program Files.

cd\Program Files\MySQL\MySQL Server 8.0\bin

This is the usual path for the MySQL bin folder. After you reach the bin folder, execute the next command:

mysql -uroot -p

This will require the password set at the installation.

2.What are MySQL users?

In MySQL, users are a combination of username and hostname identified by a password. The hostname can be
replaced with % to allow connections from any host.

3.Create a user for ‘localhost’

Now, from inside the MySQL shell, let’s create a new user for localhost by executing the next command:

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

To grant access to the user, execute:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

Finally, we will reload the grant table by executing:

FLUSH PRIVILEGES;

Now let’s break down the commands to understand them better:

  • CREATE USER 'user'@'localhost' - pretty straight forward, this command will tell MySQL to create a new user. Replace user with
    the name you want to attribute. @'localhost' specifies that the user can connect only from localhost;

  • IDENTIFIED BY 'password' - here we specify the password attributed to that user;

  • GRANT ALL PRIVILEGES - this will give the user rights to modify the tables, columns, etc. The ALL PRIVILEGES part specifies
    that the user will have standard privileges;

  • ON *.* - this part specifies the databases on which the user will have access. The first * means that the user will
    have access to all databases. If you want to grant access on a specific database, replace it with the name. For example,
    ON dbtest.*;

  • TO 'user'@'localhost' - identifies the user;

  • FLUSH PRIVILEGES - this will tell the server to refresh the grant tables.

4.Create a user that can connect from any host

To give a user the right to connect from any host, we have to replace the localhost mentions from the previous commands with
%. They will look like this:

CREATE USER 'user'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';

FLUSH PRIVILEGES;

5.Delete a user from database

To delete a user from the database, we will execute the next command:

DROP USER 'user'@'localhost'

Now you are ready to manage your users in MySQL.