MySQL(9)：SQL之DCL 用户权限管理

SQL之DCL 用户权限管理

DCL：数据控制语言：grant、revoke

一、权限表

​ 安装 MySQL 后，有个默认的 mysql 数据库，其中存储的都是用户权限表。用户登录以后，MySQL 会根据这些权限表的内容为每个用户赋予相应的权限。

user 表

​ user 表是 MySQL 中最重要的一个权限表，用来记录允许连接到服务器的账号信息。需要注意的是，在 user 表里启用的所有权限都是全局的，适用于所有数据库。

​ user 表中的字段大致可以分为 4 类，分别是用户列、权限列、安全列和资源控制列。

db表

​ db 表比较常用，是 MySQL 数据库中非常重要的权限表，表中存储了用户对某个数据库的操作权限。表中的字段大致可以分为两类，分别是用户列和权限列。

​ db 表中的权限列和 user 表中的权限列大致相同，只是user 表中的权限是针对所有数据库的，而 db 表中的权限只针对指定的数据库。

​ 如果希望用户只对某个数据库有操作权限，可以先将 user 表中对应的权限设置为 N，然后在 db 表中设置对应数据库的操作权限。

tables_priv表和columns_priv表

​ tables_priv 表用来对单个表进行权限设置，columns_priv 表用来对单个数据列进行权限设置。

​ procs_priv 表可以对存储过程和存储函数进行权限设置。

二、DCL

(1)、创建用户

语法：

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

案例：

# 创建用户user01
CREATE USER 'user01'@'%' IDENTIFIED BY '123456';

在实际应用中，我们应避免明文指定密码，可以通过 PASSWORD 关键字使用密码的哈希值设置密码。

 # 创建用户user02
 SELECT PASSWORD(123456)
 CREATE USER 'user02'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

创建的用户信息，可以在mysql 数据库的 user 表中看到，密码是经过了加密的。

(2)、给用户授权

GRANT 语句是 MySQL 中一个非常重要的语句，它可以用来创建用户、修改用户密码和设置用户权限。

语法：

GRANT 权限1,权限2,...ON 数据库名.表名 TO '用户名'@'IP地址' [IDENTIFIED BY '密码'];

注意：所有的数据库和所有表就用*.*，所有的权限用all或者all privileges

案例：

# 授予world库中所有表的create,alter,insert,update,select权限给user01
GRANT create,alter,insert,update,select ON world.* to 'user01'@'%';

# 授予world库中所有表的所有权限给user02
GRANT all ON world.* to 'user02'@'%';

权限类型

补充创建用户：

​ 创建用户和赋予权限同时操作

GRANT all privileges ON world.* to 'user03'@'%' IDENTIFIED BY '123456';

(3)、查看用户权限

语法：

SHOW GRANTS FOR 用户名@'IP地址';

案例：

# 查看user02的权限
SHOW GRANTS FOR 'user02'@'%';

USAGE ON *.* 表示该用户对任何数据库和任何表都没有权限。

(4)、撤销权限

REVOKE 语句删除某个用户的某些权限，要使用 REVOKE 语句，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限。

语法：

REVOKE 权限1,权限2,...... ON 数据库名.表名 FROM 用户名@'IP地址' IDENTIFIED BY '密码';

注意：撤销权限后，账户要重新连接客户端才会生效

案例：

# 撤销user02在world库的所有表的所有权限
REVOKE ALL ON world.* to 'user02'@'%';

(5)、修改用户名和密码

1、修改普通用户名

语法：

RENAME USER 旧用户 TO 新用户;

案例：

# 修改user03名为test01
RENAME USER 'user03'@'%' TO 'test01'@'%';

2、修改普通用户密码

**语法一：**SET语句修改普通用户的密码

SET PASSWORD FOR '用户名'@'主机名' = password('新密码');

**语法二：**GRANT 语句修改普通用户密码

GRANT USAGE ON *.* TO '用户名'@'主机名' IDENTIFIED BY '新密码';

**语法三：**直接修改mysql.user表中的字段

UPDATE MySQL.user SET authentication_string = PASSWORD('新密码') WHERE User = '用户名' AND Host = "主机名";

案例一：

# 修改test01的密码为123123
SET PASSWORD FOR 'test01'@'%' = password('123123');

案例二：

# 修改test01的密码为123456
GRANT USAGE ON *.* TO 'test01'@'%' IDENTIFIED BY '123456';

案例三：

# 修改test01的密码为111111
UPDATE mysql.user SET authentication_string = PASSWORD('111111') WHERE User = 'test01' AND Host = '%';

3、修改root密码

以下三种方法：

SET PASSWORD = PASSWORD ('root新密码');

UPDATE mysql.user SET authentication_string = PASSWORD ('root新密码')
WHERE User = 'root' and Host = 'localhost';

# 独有
mysqladmin -uroot [-h hostname] -p password 新密码

(6)、删除用户

语法：

DROP USER '用户名'@'主机名';

案例：

# 删除test01用户,并撤销其权限。
DROP USER 'test01'@'%';