MySQL -- 权限

创建用户

1	CREATE USER 'ua'@'%' IDENTIFIED BY 'pa';

用户名+地址才表示一个用户，ua@ip1和ua@ip2代表的是两个不同的用户
在磁盘上，往mysql.user表里插入一行，由于没有指定权限，所有表示权限的字段都是N
在内存里，往数组acl_users里插入一个acl_user对象，该对象的access字段的值为0

mysql> SELECT * FROM mysql.user WHERE user = 'ua'\G;
*************************** 1. row ***************************
                  Host: %
                  User: ua
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: caching_sha2_password
      password_expired: N
 password_last_changed: 2019-03-17 14:33:56
     password_lifetime: NULL
        account_locked: N
      Create_role_priv: N
        Drop_role_priv: N
Password_reuse_history: NULL
   Password_reuse_time: NULL

权限范围

全局权限

作用于整个MySQL实例，权限信息保存在mysql.user，要给用户ua赋一个最高权限的语句如下

1	GRANT ALL PRIVILEGES ON . to 'ua'@'%' WITH GRANT OPTION;

在磁盘上，将mysql.user表里的用户'ua'@'%'这一行中所有表示权限的字段都修改为Y
在内存里，从数组acl_users中找到这个用户对应的对象，将access值（权限位）修改为二进制的全1
这个GRANT命令执行完成后，如果有新的客户端使用用户ua登录成功
- MySQL会为新连接维护一个线程对象
- 然后从acl_users数组里查到用户ua的权限，并将权限值拷贝到这个线程对象中
- 之后在这个连接中执行的语句，所有关于全局权限的判断，都直接使用线程对象内部保存的权限位
GRANT命令对于全局权限，同时更新了磁盘和内存，命令完成后即时生效
- 接下来新创建的连接会使用新的权限
- 对于一个已经存在的连接，它的全局权限不受影响（因为判断时采用的是线程对象内部的权限值）

回收权限

1	REVOKE ALL PRIVILEGES ON . FROM 'ua'@'%';

在磁盘上，将mysql.user表里的用户'ua'@'%'这一行中所有表示权限的字段都修改为N
在内存里，从数组acl_users中找到这个用户对应的acl_user对象，将access的值修改为0

DB权限

让用户'ua'@'%'拥有库db1的所有权限

1	GRANT ALL PRIVILEGES ON db1.* to 'ua'@'%' WITH GRANT OPTION;

基于库的权限记录保存在mysql.db中，在内存里则保存在数组acl_dbs中
在磁盘上，往mysql.db表中插入一行记录，所有权限位的字段设置为Y
在内存中，增加一个对象到数组acl_dbs，该对象的权限位为全1
每次需要判断一个用户对一个数据库读写权限的时候，都需要遍历一遍acl_dbs数组（多线程共享）
- 根据user、host和db找到匹配的对象，然后根据对象的权限位来判断
GRANT命令对于已经存在的连接的影响，全局权限和基于DB的权限是不一样的
- 全局权限：线程私有
- 基于DB的权限：线程共享

mysql> SELECT * FROM mysql.db WHERE user = 'ua'\G;
*************************** 1. row ***************************
                 Host: %
                   Db: db1
                 User: ua
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y

操作序列

时刻	session A	session B	session C
T1	CONNECT(root,root); CREATE DATABASE db1; CREATE USER ‘ua‘@’%’ IDENTIFIED BY ‘pa’; GRANT SUPER ON . TO ‘ua‘@’%’; GRANT ALL PRIVILEGES ON db1.* TO ‘ua‘@’%’;
T2		CONNECT(ua,pa) SET GLOBAL sync_binlog=1; (Query OK) CREATE TABLE db1.t(c INT); (Query OK)	CONNECT(ua,pa) USE db1;
T3	REVOKE SUPER ON . FROM ‘ua‘@’%’;
T4		SET GLOBAL sync_binlog=1; (Query OK) ALTER TABLE db1.t ENGINE=InnoDB; (Query OK)	ALTER TABLE t ENGINE=InnoDB; (Query OK)
T5	REVOKE ALL PRIVILEGES ON db1.* FROM ‘ua‘@’%’;
T6		SET GLOBAL sync_binlog=1; (Query OK) ALTER TABLE db1.t ENGINE=InnoDB; (ALTER command denied)	ALTER TABLE t ENGINE=InnoDB;(Query OK)

SET GLOBAL sync_binlog=1;这个操作需要SUPER权限
虽然用户ua的SUPER权限在T3时刻被回收了，但在T4时刻执行SET GLOBAL的时候，权限验证还是通过了
- 这是因为SUPER是全局权限，这个信息在线程对象中，而REVOKE操作影响不了线程对象
在T4时刻去掉用户ua对db1库的所有权限后，session B在T6时刻再操作db1的表时，就会报权限不足
- 这是因为acl_dbs是一个全局数组，所有线程判断db权限都会用该数组
- 因此REVOKE操作会立马影响到session
特殊逻辑
- 如果当前会话已经在某个db里面，之前use这个db时拿到的库权限就会保存在会话变量中
- session C在T2执行了USE db1，拿到这个库的权限，在切换出db1之前，一直对db1有权限

表权限和列权限

1
2
3

CREATE TABLE db1.t1(id INT, a INT);
GRANT ALL PRIVILEGES ON db1.t1 TO 'ua'@'%' WITH GRANT OPTION;
GRANT SELECT(id), INSERT(id,a) ON db1.t1 TO 'ua'@'%' WITH GRANT OPTION;

表权限定义在表mysql.tables_priv，列权限定义在表mysql.columns_priv
- 这两类权限组合起来存放在内存的hash结构：column_priv_hash
跟DB权限类似，这两个权限在每次GRANT的时候都会修改数据表，也会同步修改内存的hash结构
因此这两类权限的操作，也会立马影响到已经存在的连接

FLUSH PRIVILEGES

FLUSH PRIVILEGES命令会清空acl_users数组（_全局权限_）
- 然后从mysql.user表中读取数据重新加载，重新构造一个acl_users数组
- 以数据表中的数据为准，会将全局权限的内存数组重新加载一遍
同样的，对于DB权限、表权限和列权限，MySQL也做了同样的处理
如果内存的权限数据和磁盘数据表的权限数据相同的话，不需要执行FLUSH PRIVILEGES
- 如果都是用GRANT/REVOKE语句执行的话，内存和数据表的数据应该保持同步更新的
- 正常情况下，在执行GRANT命令之后，没有必要跟着执行FLUSH PRIVILEGES命令

使用场景

当数据表的权限数据与内存中的权限数据不一致，通过FLUSH PRIVILEGES来重建内存数据，达到一致状态
这种不一致的状态往往由于不规范的操作导致的，例如直接用DML语句操作系统权限表

不规范操作1

时刻	client A	client B
T1	CONNECT(root,root) CREATE USER ‘ua‘@’%’ IDENTIFIED BY ‘pa’;
T2		CONNECT(ua,pa) (Connect OK) DISCONNECT
T3	DELETE FROM mysql.user WHERE user=’ua’;
T4		CONNECT(ua,pa) (Connect OK) DISCONNECT
T5	FLUSH PRIVILEGES;
T6		CONNECT(ua,pa) (Access Denied)

T3时刻虽然使用了DELETE语句删除了用户ua，但在T4时刻，仍然可以用用户ua连接成功
- 因为内存中acl_users数组中还有这个用户，系统判断时认为用户还正常存在的
T5时刻执行过FLUSH PRIVILEGES命令后，内存更新，T6时刻就会报Access Denied错误
直接操作系统权限表是很不规范的操作

不规范操作2

时刻	client A
T1	CONNECT(root,root) CREATE USER ‘ua‘@’%’ IDENTIFIED BY ‘pa’;
T2	DELETE FROM mysql.user WHERE user=’ua’;
T3	GRANT SUPER ON . TO ‘ua‘@’%’ WITH GRANT OPTION; (ERROR 1133 (42000): Can’t find any matching row in the user table)
T4	CREATE USER ‘ua‘@’%’ IDENTIFIED BY ‘pa’; (ERROR 1396 (HY000): Operation CREATE USER failed for ‘ua‘@’%’)