/

MySQL 管理用户与访问授权

MySQL 创建用户、修改密码、删除用户;查看、授予、撤销用户权限;对 MySQL 远程访问的新理解。

管理用户

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
  • username:创建的用户名
  • host:该用户在哪个主机上可以登陆。如果是本地用户可用 localhost;如果想让该用户可以从任意远程主机登陆,可以使用通配符 %
  • password:该用户的登陆密码。密码可以为空,如果为空则该用户可以不需要密码登陆服务器

例子:

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.%' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

修改用户密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");

如果修改当前登陆用户密码:

SET PASSWORD = PASSWORD("newpassword");

删除用户

DROP USER 'username'@'host';

访问授权

查看用户权限

SHOW GRANTS FOR 'pig'@'%';

授予用户权限

GRANT privileges ON databasename.tablename TO 'username'@'host'

GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
  • privileges:用户的操作权限,如 SELECT INSERT UPDATE 等,如果要授予所的权限则使用 ALL
  • databasename:数据库名
  • tablename:表名,如果要授予该用户对所有数据库和表的相应操作权限则可用 * 表示

注意: 用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用 WITH GRANT OPTION 命令:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

撤销用户权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

REVOKE SELECT ON *.* FROM 'pig'@'%';

注意: 假如你在给用户‘pig‘@’%’授权的时候是这样的(或类似的):

GRANT SELECT ON test.user TO 'pig'@'%';

则在使用:

REVOKE SELECT ON _._ FROM 'pig'@'%';

命令并 不能 撤销该用户对 test 数据库中 user 表的 SELECT 操作。

相反,如果授权使用的是:

GRANT SELECT ON _._ TO 'pig'@'%';

则:

REVOKE SELECT ON test.user FROM 'pig'@'%';

命令 也不能 撤销该用户对 test 数据库中 user 表的 SELECT 权限。

具体信息可以用查看命令:

SHOW GRANTS FOR 'pig'@'%';

刷新权限

flush privileges;

开启远程访问

通过上面的配置,可以发现:开启 MySQL 远程访问,其实就是修改用户权限:

-- 只允许 192.168.1.100 连接
GRANT ALL PRIVILEGES ON _._ TO 'root'@'192.168.1.100' IDENTIFIED BY 'pwd123' WITH GRANT OPTION;

-- 允许所有 ip 访问
GRANT ALL PRIVILEGES ON _._ TO 'root'@'%' IDENTIFIED BY 'pwd123' WITH GRANT OPTION;

注意: Ubuntu 上还需要修改 MySQL 配置文件

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address = 127.0.0.1 这一行注释掉, 即:

#bind-address = 127.0.0.1

重启 MySQL

补充

查询当前用户:

mysql> SELECT CURRENT_USER();
HOST User 被条目匹配的连接
‘thomas.loc.gov’ ‘fred’ fred, 从 thomas.loc.gov 连接
‘thomas.loc.gov’ ‘’ 任何用户, 从 thomas.loc.gov 连接
‘%’ ‘fred’ fred, 从任何主机连接
‘%’ ‘’ 任何用户, 从任何主机连接
‘%.loc.gov’ ‘fred’ fred, 从在 loc.gov 域的任何主机连接
‘x.y.%’ ‘fred’ fred, 从 x.y.net、x.y.com,x.y.edu 等联接(这或许无用)
‘144.155.166.177’ ‘fred’ fred, 从有 144.155.166.177 的主机连接
‘144.155.166.%’ ‘fred’ fred, 从 144.155.166 C 类子网的任何主机连接

可以在 Host 字段使用 IP 通配符值(例如,’144.155.166.%’匹配在一个子网上的每台主机),有可能某人可能企图探究这种能力,通过命名一台主机为 144.155.166.somewhere.com。为了阻止这样的企图,MySQL 不允许匹配以数字和一个点起始的主机名,这样,如果你用一个命名为类似 1.2.foo.com 的主机,它的名字决不会匹配授权表中的 Host 列。只有一个 IP 数字能匹配 IP 通配符值。

Reference: