I am creating my own PHP development environtment and I am setting mysql.
I need to grant to my root user all the privileges so I use:
GRANT ALL ON *.* TO 'root'@'%';
Why It use 'root'@'%'?
I understand that these are user and host, but in this case that I am in a development environment why I have to put a host?
In case of a production server I understand that it is to grant only the database hosted by an specified host, right?
Thanks.
'%' is a wildcard fall-back. The login attempt tries the most specific and then moves toward the wildcard (moving toward more general) until it finds a user/host combo account if you will to authenticate. If it can't find such an account, the login fails.
So when you have 'root'@'localhost' (call it acct or user 1), that is all fine and dandy when you are sitting at localhost. But when you attach from a different client, say '192.168.10.103', you are no longer 'localhost'. You now need either an explicit 'root'@'192.168.10.103' (user or acct if you will), or for sanity we swing for the wildcard with '%'. Why, because there is no way you want to litter your mysql.user with all the permutations.
What users you have can be seen with
select user,host from mysql.user;
It is not uncommon to have 3 users for root
'root'@'localhost'
'root'@'127.0.0.1'
'root'@'%'
Giving them all the same rights. The 3rd one above is a security risk, naturally. That is so because '%' means from any host anywhere.
And that is why 'root'@'localhost' is recommended to be the only root user in your system. You achieve root by either sitting at that box, or using ssh with a secure connection to become localhost if you will.
You need to understand that it is all about security, and the way, for better or worse, MySQL chose to implement what a user is. A user is the username, plus the hostname coming in as. The hostname could be an IP Address, or a DNS hostname like casper.comcastbusiness.net. When MySQL receives the connection request, it bubbles up from most-specific toward '%' until it can or cannot authenticate the request.
As one use case, I have a server that I want to authenticate as root but not have a 'root'@'%' user in the system for security reasons. So I explicitly created a user like 'root'@'casper.comcastbusiness.net'. Another layer of course is the firewall but that is a separate issue. But to touch on it lightly, AWS ec2 has a Security Groups area to have it such that only certain IP Addresses are able to connect to port 3306. So the firewall is another layer, of what I am saying is that the whole thing is about securing your server.
Administrating it is a pain. But it is what is and we have to live with it.
