DbSchema | Create New User in MySQL & Grant Access

Publish on DbSchema Blog >>>

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 __localhostmentions 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.