mysql grant 命令是什么意思?

2022-01-24 00:00:00 development-environment debian mysql

我正在创建自己的 PHP 开发环境并设置 mysql.

I am creating my own PHP development environtment and I am setting mysql.

我需要授予我的 root 用户所有权限,所以我使用:

I need to grant to my root user all the privileges so I use:

GRANT ALL ON *.* TO 'root'@'%';

为什么它使用'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?

谢谢.

推荐答案

'%' 是通配符后备.登录尝试尝试最具体的,然后转向通配符(转向更一般的),直到找到用户/主机组合帐户(如果您要进行身份验证).如果找不到这样的帐户,则登录失败.

'%' 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.

因此,当您拥有 'root'@'localhost'(称为帐户或用户 1)时,当您坐在 localhost 时,一切都很好.但是当您从不同的客户端附加时,例如192.168.10.103",您不再是本地主机".您现在需要一个显式的 'root'@'192.168.10.103'(用户或帐户,如果你愿意),或者为了理智,我们使用 '%' 的通配符.为什么,因为你不可能在你的 mysql.user 中乱扔所有的排列.

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.

可以看到您拥有的用户

select user,host from mysql.user;

拥有 3 个 root 用户的情况并不少见

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.

这就是为什么 'root'@'localhost' 被推荐为系统中唯一的 root 用户的原因.您可以通过坐在那个盒子上来获得 root 权限,或者如果愿意的话,使用带有安全连接的 ssh 来成为 localhost.

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.

您需要了解这一切都与安全有关,以及 MySQL 选择实现用户的方式,无论好坏.用户是用户名,加上主机名进来.主机名可以是 IP 地址,或像 casper.comcastbusiness.net 这样的 DNS 主机名.当 MySQL 收到连接请求时,它会从 most-specific 向 '%' 冒泡,直到它可以或不能验证请求.

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.

作为一个用例,我有一台服务器,我想以 root 身份进行身份验证,但 没有 在系统中有一个 'root'@'%' 用户以确保安全原因.所以我明确地创建了一个像 'root'@'casper.comcastbusiness.net' 这样的用户.另一层当然是防火墙,但这是一个单独的问题.但要轻描淡写,AWS ec2 有一个安全组区域,只有特定的 IP 地址才能连接到端口 3306.所以防火墙是另一层,我要说的是整个事情是关于保护您的服务器.

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.

相关文章