MySQL allow us to restrict users to certain scopes or domains.
For example, by default root user can only be used if you attempt to connect to the server using the localhost host name. If you attempt to use the remote IP you will probably see your connection denied.
In the guide I’ll quickly explain you the basic of MySQL user administration.
Step 1) Connect to MySQL
$ sudo mysql
In certain cases you might have to connect as follow:
$ mysql -u root -p
The root password will be requested. Write it and confirm.
Step 2) Create a new user
The syntax:
CREATE USER '{{user}}'@'{{host}}' IDENTIFIED WITH {mysql_native_password|} BY 'password';
Option 1: (mysql_native_password) Using the default encryption
CREATE USER '[user_name]'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
Option 2: (caching_sha2_password) Using the new (recommended) encryption system.
CREATE USER '[user_name]'@'%' IDENTIFIED WITH caching_sha2_password BY 'password';
Note, although this is a more robust/secure plugin, some systems do not fully support it. For that reason, you might end up using the option 1.
There is a known issue with some versions of PHP that causes problems with
caching_sha2_password. If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure,
mysql_native_password plugin instead
Step 3) Grant privileges
GRANT ALL PRIVILEGES ON *.* TO '[user_name]'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Note: The way I granted privileges below is probably not the way you should be doing it but it’s also the fastest way.
“ALL” can be replaced by one or more privileges separated by comma.
Then in the expression “
.” the first * stands for all databases and the second for all tables.
I suggest you to grant privileges wisely.
Also, note that ‘localhost’ restricts that user to programs running on the same server than your database. If you are running
