用户与权限管理

1. 用户管理

用户表存放在 mysql 数据库下的 user 表

主键是 host 和 user 复合主键 (两个字段非空且唯一即可)

1.2 创建用户

CREATE USER 用户名 [IDENTIFIED BY '密码'];

• 用户名参数，由用户(User) 和 主机名(Host) 构成
• 若没有指定 host 则默认为 %
  举例：
  create user 'zhang3'@'localhost' identified by '123456';

创建出的新用户默认只有登录权限

1.3 修改用户

修改用户名：

UPDATE mysql.user SET USER = 'wang5' WHERE USER = 'li4';

FLUSH PRIVILEGES;

1.4 删除用户

方式一：DROP 方式(推荐)

使用 DROP USER 方式时，必须要用 DROP USER 权限
DROP USER 用户名[,用户名...];

举例:
DROP USER li4;#若用户名相同，则默认删除 host 为 % 的用户
DROP USER 'wang5'@'localhost'

方式二：使用DELETE 方式删除(不推荐)
DELETE FROM mysql.user WHERE Host = '主机名' AND User = '用户名'
执行命令后，需要刷新权限
FLUSH PRIVILEGES;

举例：

DELETE FROM mysql.user WHERE Host='localhost' AND User='Emily';
FLUSH PRIVILEGES;

使用 DELETE 命令删除，系统会用残留信息，所以不推荐使用

1.5 设置当前用户密码

推荐写法

1. 使用ALTER USER 命令进行修改当前用户密码
   ALTER USER USER() IDENTIFIED BY '新密码';

其中 USER() 代表获取当前用户

2. 使用 SET 语句修改当前用户密码
   SET PASSWORD = '新密码';

不推荐写法

SET PASSWORD = PASSWORD('新密码')

1.6 修改其他用户密码

1. 使用 ALTER USER 修改普通用户的密码
   ALTER USER 用户名 IDENTIFIED BY '新密码';

2. 使用 SET 命令修改
   SET PASSWORD FOR '用户名'@'主机名' = '新密码'

3. 使用 UPDATE 语句修改(不推荐)

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

2. 权限管理

2.1 权限列表

查看权限
show privileges;

1. CREATE 、DROP、ALTER
2. SELECT 、INSERT、UPDATE、DELETE
3. INDEX ：允许创建或删除索引
4. CREATE ROUTINE 权限：创建保存的函数，EXECUTE权限：执行函数
5. GRANT权限：允许授权给其他用户
6. FILE权限：读/写 MySQL 服务器的所有文件

2.2 授权原则

1. 只授予能满足需要的最小权限
2. 创建用户时限制用户的登录主机
3. 为每个用户设置满足密码复杂度的密码
4. 定期清理不需要的用户

2.3 授予权限

授权有两种方式。通过角色赋予用户给用户授权和直接给用户授权

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

• 该权限如果发现没有该用户，则会直接新建一个用户。

示例：
给li4用户用本地命令行方式，授予xyzdb这个库下的所有表的插删改查的权限。
GRANT SELECT,INSERT,DELETE,UPDATE ON xyzdb.* TO li4@localhost ;

我们在开发应用的时候，经常会遇到一种需求，就是要根据用户的不同，对数据进行横向和纵向的分组。

• 所谓横向的分组，就是指用户可以接触到的数据的范围，比如可以看到哪些表的数据；
• 所谓纵向的分组，就是指用户对接触到的数据能访问到什么程度，比如能看、能改，甚至是删除。

2.4 查看权限

• 查看当前用户权限

SHOW GRANTS;
或
SHOW GRANTS FOR CURRENT_USER;
或
SHOW GRANTS FOR CURRENT_USER();

• 查看某用户的全局权限
  SHOW GRANTS FOR 'user'@'主机地址';

2.5 收回权限

收回用户不必要的权限可以在一定程度上保证系统的安全性。

注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。

• 收回权限命令
  REVOKE 权限1,权限2,... ON 数据库名.表名 FROM '用户名'@'用户地址';
• 举例

收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

• 注意： 须用户重新登录后才能生效

3. 权限表

3.1 user 表

user表 是 MySQL 中的一个权限表，记录用户账号和权限信息，有 49 个字段

这些字段可以分为 4 类，范围列(或用户列)、权限列、安全列和资源控制列

1. 范围列(或用户列)

• host：表示连接类型
  • %表示所有远程通过 TCP 方式的连接
  • IP 地址：通过指定 ip 地址进行 TCP 方式的连接
  • 机器名 通过指定网络中的机器名进行的TCP方式的连接
  • ::1 IPv6的本地ip地址，等同于IPv4的 127.0.0.1
  • localhost 本地方式通过命令行方式的连接 ，比如mysql -u xxx -p xxx 方式的连接。
• user：表示用户名，同一用户通过不同的 host 连接权限是不一致的
• password：密码
  • 5.7 通过password 生成的密文字符串。
  • 8.0 密码保存到 authentication_string字段中，不再使用 password字段，密码加密方式 由 SHA1 改为 SHA2 ，不可逆。

2. 权限列

• Grant_priv 字段
  • 是否拥有 GRANT 权限
• Shutdown_priv 字段
  • 是否拥有停止 MySQL 服务的权限
• Super_priv 字段
  • 是否拥有超级权限
• Execute_priv 字段
  • 是否拥有 EXECUTE 权限。有则可以执行存储过程和函数
• Select_priv ，Insert_priv 等
  • 为该用户所拥有的权限

3. 安全列
   安全列只有6个字段，其中两个是ssl相关的（ssl_type、ssl_cipher），用于 加密 ；两个是x509相关的（x509_issuer、x509_subject），用于 标识用户 ；另外两个Plugin字段用于 验证用户身份 的插件，该字段不能为空。如果该字段为空，服务器就使用内建授权验证机制验证用户身份。

4. 资源控制列
   资源控制列的字段用来 限制用户使用的资源 ，包含4个字段，分别为：
   ①max_questions，用户每小时允许执行的查询操作次数； ②max_updates，用户每小时允许执行的更新操作次数； ③max_connections，用户每小时允许执行的连接操作次数； ④max_user_connections，用户允许同时建立的连接次数。

3.2 db表

DESC mysql.db：查看 db 表的基本结构

1. 用户列
   Host、User、Db(数据库名)。这三个字段组合构成了 db 表的主键
   表示某个主机连接的某个用户对某个数据库的操作权限

2. 权限列
   Create_routine_priv 和 Alter_routine_priv 决定用户是否有创建和修改存储过程的权限

3.3 tables_priv 表 和 columns_priv 表

tables_priv表用来 对表设置操作权限 ，columns_priv表用来对表的 某一列设置权限 。

tables_priv表有8个字段，分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和 Column_priv，各个字段说明如下：

• Host 、 Db 、 User 和 Table_name 四个字段分别表示主机名、数据库名、用户名和表名。
• Grantor表示修改该记录的用户。
• Timestamp表示修改该记录的时间。
• Table_priv 表示对象的操作权限。包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter。

Column_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References。

3.4 procs_priv 表

procs_priv 表可以对 存储过程和存储函数设置操作权限

DESC mysql.procs_priv;

4.访问控制(了解)

4.1 连接核实阶段

当用户试图连接 MySQL 服务器的过程。MySQL 服务器收到请求后，会使用 user 表中的 host、user和authentication_string这3个字段匹配客户端提供信息。

服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求。

4.2 请求核实阶段

成功建立了连接，服务器就会进入请求核实阶段。确认每个请求，用户是否有足够的权限去操作。

确认权限时，MySQL首先 检查user表 ，如果指定的权限没有在user表中被授予，那么MySQL就会继续 检查db表 ，db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则MySQL继续 检查tables_priv表 以及 columns_priv表 ，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将 返回错误信息 ，用户请求的操作不能执行，操作失败。

提示： MySQL通过向下层级的顺序（从user表到columns_priv表）检查权限表，但并不是所有的权限都要执行该过程。例如，一个用户登录到MySQL服务器之后只执行对MySQL的管理操作，此时只涉及管理权限，因此MySQL只检查user表。另外，如果请求的权限操作不被允许，MySQL也不会继续检查下一层级的表。

5. 角色管理

5.1 角色理解

为了管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

5.2 创建角色

CREATE ROLE '角色名'[@'主机名']...;

角色名称的命名规则和用户名类似。如果 主机名省略，默认为% ， 角色名不可省略 ，不可为空。

5.3 给角色赋予权限

GRANT 权限名1,权限名2,.. ON 数据库名.表名 TO '角色名'[@'主机名'];

使用 show 语句可以查询权限名称

SHOW PRIVILEGES\G

举例：
GRANT SELECT ON demo.settlement TO 'manager';
GRANT SELECT ON demo.goodsmaster TO 'manager';
GRANT SELECT ON demo.invcount TO 'manager';

5.4 查看角色权限

SHOW GANTS FOR '角色名'

mysql> SHOW GRANTS FOR 'manager';
+-------------------------------------------------------+
| Grants for manager@%                                  |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%`                   |
| GRANT SELECT ON `demo`.`goodsmaster` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`invcount` TO `manager`@`%`    |
| GRANT SELECT ON `demo`.`settlement` TO `manager`@`%`  |
+-------------------------------------------------------+

注意：只要创建了一个角色，系统默认为给一个 USAGE 权限，意思是 连接登录数据库的权限

5.5 回收角色的权限

REVOKE 权限名 ON 表名 FROM '角色名';

5.6 删除角色

DROP ROLE '角色名'

注意：若删除了角色，则用户就会失去通过该角色获得的所有权限

5.7 给用户赋予角色

GRANT 角色名 TO 用户名;

注意：将角色赋予给用户，还必须要激活用户才拥有该角色的权限

查看用户的权限(角色会包含在列表中)
SHOW GRANTS FOR '用户名'@'主机地址'

查看用户的角色（若无则显示 NONE）
SELECT CURRENT_ROLE()

5.8 激活角色

方式一：使用 set default role 命令
SET DEAULT ROLE ALL TO '用户名'@'主机地址';

举例：使用 SET DEFAULT ROLE 为下面4个用户默认激活所有已拥有的角色如下：

SET DEFAULT ROLE ALL TO
'dev1'@'localhost',
'read_user1'@'localhost',
'read_user2'@'localhost',
'rw_user1'@'localhost';

方式二：activate_all_roles_on_login设置为ON

SET GLOBAL activate_all_roles_on_login=ON;

• 默认情况

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

这条 SQL 语句的意思是，对 所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有权限。

5.9 收回用户的角色

REVOKE 角色名 FROM 用户名

5.10 设置默认角色

方式一：服务启动前设置

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'

方式二：运行时设置

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; 
系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; 
系统重启后失效

6. 配置文件的使用

6.1 配置文件格式

配置文件中的启动项被划分为几个分组，每个组有一个组名，用中括号 [] 括起来

[server]
(具体的启动项...)
[mysqld]
(具体的启动项...)
[mysqld_safe]
(具体的启动项...)
[client]
(具体的启动项...)
[mysql]
(具体的启动项...)
[mysqladmin]
(具体的启动项...)

具体启动项：

[server]
option1             这是option1，该选项不需要选项值
option2=value1      这是option2，该选项需要选项值

6.2 启动命令与选项组

• [server] 组下的启动项作用于 所有的服务器程序
• [client] 组下的启动项作用于 所有的客户端 程序

6.4 同一个配置文件中多个组的优先级

以最后声明的作为启动项

[server]
default-storage-engine=InnoDB

[mysqld]
default-storage-engine=MyISAM

6.5 命令行和配置文件中启动选项的区别

若一个启动项既在配置文件中出现，又在命令行中数据，则以命令行启动项为准
类似于覆盖

举例

[server]
default-storage-engine=InnoDB

启动命令
mysql.server start --default-storage-engine=MyISAM

最后以 default-storage-engine 的值为 MyISAM