MySQL 管理用户与访问授权

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

-- 创建用户 + 授权
GRANT ALL PRIVILEGES ON _._ TO 'tom'@'%' IDENTIFIED BY 'pwd123' WITH GRANT OPTION;
-- 查询权限
SHOW GRANTS FOR 'tom'@'%';
-- 授权
GRANT SELECT ON `my_db`.* TO 'tom'@'%';
-- 撤权
REVOKE ALL PRIVILEGES ON `my_db`.* FROM  'tom'@'%';
REVOKE GRANT OPTION ON `my_db`.* from 'tom'@'%';

-- not necessary
FLUSH PRIVILEGES;

管理用户

创建用户

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 通配符值。

References

– EOF –