共计 8244 个字符，预计需要花费 21 分钟才能阅读完成。
MariaDB/MySQL 中的 user 由用户名和主机名构成，如 ”root@localhost”，同用户名但不同主机名对 MySQL/MariaDB 来讲是不同的，也就是说 ”root@localhost” 和 ”root@127.0.0.1″ 是不同的用户，尽管它们都是本机的 root。
1. 权限验证
在 MariaDB/MySQL 服务器启动后会载入权限表到内存中，当用户要连接服务器，会读取权限表来验证和分配权限，即在内存中进行权限的读取和写入。
MariaDB/MySQL 中的权限系统经过两步验证：
1. 合法性验证：验证 user 是否合法，合法者允许连接服务器，否则拒绝连接。
2. 权限验证和分配：对通过合法性验证的用户分配对数据库中各对象的操作权限。
1.1 权限表
MariaDB/MySQL 中的权限表都存放在 mysql 数据库中。MySQL5.6 以前，权限相关的表有 user 表、db 表、host 表、tables_priv 表、columns_priv 表、procs_priv 表(存储过程和函数相关的权限)。从 MySQL5.6 开始，host 表已经没有了。MariaDB 中虽然有 host 表，但却不用。
这几个表用的最多的是 user 表。user 表主要分为几个部分：用户列、权限列、安全列、资源控制列以及杂项列，最需要关注的是用户列和权限列。其中权限列又分为普通权限 (上表中红色字体) 和管理权限列，如 select 类的为普通权限，super 权限为管理权限。且可以看到，db 表中的权限全都是普通权限，user 表中除了 db 表中具有的普通权限还有 show_db_pirv 和 create_tablespace_priv，除此之外还有几个管理员权限。也就是说，db 中没有的权限是无法授予到指定数据库的。例如不能授予 super 权限给 test 数据库。
另外，usage 权限在上表中没有列出，因为该权限是所有用户都有的权限，它只用来表示能否登录数据库，它的一个特殊功能是 grant 仅指定该权限的时候不会影响现有权限，也就是说可以拿 grant 来修改密码而不影响现有权限。
需要说明的是，从 user 表到 db 表再到 tables_priv 表最后是 columns_priv 表，它们的权限是逐层细化的。user 表中的普通权限是针对所有数据库的，例如在 user 表中的 select_priv 为 Y，则对所有数据库都有 select 权限；db 表是针对特定数据库中所有表的，如果只有 test 数据库中有 select 权限，那么 db 表中就有一条记录 test 数据库的 select 权限为 Y，这样对 test 数据库中的所有表都有 select 权限，而此时 user 表中的 select 权限就为 N(因为为 Y 的时候是所有数据库都有权限)；同理 tables_priv 表也一样，是针对特定表中所有列的权限；columns_priv 则是针对特定列的权限。
所以对于已经通过身份合法性验证的用户的权限读取和分配的机制如下：
•1. 读取 uesr 表，看看 user 表是否有对应为 Y 的权限列，有则分配。
•2. 读取 db 表，看看 db 表中是否有哪个数据库分配了对应的权限。
•3. 读取 tables_priv 表，看看哪些表中有对应的权限。
•4. 读取 columns_priv 表，看看对哪些具体的列有什么权限。
例如，为某一用户授予 test 数据库的 select 权限。可以看到 user 表中的 select_priv 为 N，而 db 表中的 select 为 Y。
GRANT SELECT ON test.* TO ‘long’@’192.168.100.1’ IDENTIFIED BY ‘123456’;
SELECT host,user,select_priv FROM mysql.user;
SELECT * FROM mysql.db;
1.2 图解认证和权限分配的两个阶段
1.3 权限生效时机
在服务器启动时读取权限表到内存中，从此时开始权限表生效。
之后使用 grant、revoke、set password 等命令也会隐含的刷新权限表到内存中。
另外，使用显式的命令 flush privileges 或 mysqladmin flush-privileges 或 mysqladmin reolad 也会将上述几张权限表重新刷到内存中以供后续的身份验证和权限验证、分配。
2. 用户管理
用户管理分为几个方面，创建用户、对用户授权、修改和删除用户。
2.1 创建用户
创建账号有几种方法。
•1. 使用 grant 直接对账号授权，账号不存在则会创建；
•2. 向 mysql.user 表中插入记录；
•3. 使用 create user 命令。
后两种方法创建的用户初始时没有任何权限(只有 usage 登录数据库的权限)，并且修改权限后要使用 FLUSH PRIVILEGES 语句或执行 mysqladmin flush-privileges 或 mysqladmin reload 命令刷新权限表到内存中，而第一种方法简便的多，创建用户后会自动刷新权限表。
grant 和 revoke 语法：
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] …
ON [object_type] priv_level
TO user [IDENTIFIED [BY [PASSWORD] ‘password’][WITH with_option [with_option]
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
| MAX_STATEMENT_TIME time
grant 可以在库、表、函数、存储过程、特定列上授权，且一次性可以为多个用户授予多个对象的权限。其中 with grant option 表示拥有该权限后的用户可以给别的用户授予自身所拥有的权限。
revoke 表示收回权限，注意 revoke 无法收回 usage 权限。
其中 user 的表示方法是 ‘ 用户名 ’@’ 主机名 ’，主机名部分可以是主机名，可以是 IP 地址，可以是 localhost，可以是通配符组成的主机名(空的 host 值也表示所有 host，等价于 ’user_name’@’%’)。如下示例：
对于网段地址，可以指定掩码来表示，如 192.168.100.1/255.255.255.0，不能使用 cidr 格式的掩码记录方式，也不能指定非 8、16、24、32 位的掩码，如 192.168.100.1/255.255.255.240 是不允许的。
如果在 user 表中的用户有交叉部分，如 root 既可以从 localhost 登录，也可以从 127.0.0.1 登录，还可以从本机 IP192.168.100.61 登录，还可以从网段地址 192.168.100.% 登录，那么到底会从哪个登录？
在读取权限表 user 到内存中的时候，首先会根据 host 列的具体性进行排序，然后再根据 user 列进行具体性排序(即理解为 order by host,user)，然后从上到下扫描，首次扫描到符合的记录就使用该记录登录。具体性的意思是越具体的 user 优先级越高，通配符范围越宽的 user 优先级越低。例如 root@localhost 的具体性比 root@’%’ 的具体性高，后者又比 ’%’@’%’ 的具体性高。
2.2 create user 和 alter user
在 MySQL 5.6.7 之前，不要使用这两个命令创建用户和修改用户，因为它们会在 mysql.user 表的 password 列设置空串。到 mysql5.6.7 解决了这个问题。MariaDB 可随意使用。
语法：
CREATE [OR REPLACE] USER [IF NOT EXISTS]
user_specification [,user_specification] …
[WITH resource_option [resource_option] …]
user_specification:
username [authentication_option]
authentication_option:
IDENTIFIED BY ‘authentication_string’
resource_option:
MAX_QUERIES_PER_HOUR count
| MAX_UPDATE_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
例如：
create user ‘longshuai’@’127.0.0.1’ identified by ‘123456’;
alter user 和 create user 语法基本一致，但在 MySQL 中有让密码过期的功能，而在 MariaDB 中不支持该功能。
ALTER USER user_specification [, user_specification] …
user_specification:
user PASSWORD EXPIRE
例如，让刚才创建的用户过期。
alter user ‘longshuai’@’127.0.0.1’ password expire;
2.3 记录创建用户的时间
MariaDB/MySQL 中 user 的元数据信息都存放在 mysql.user 表中，但是在这个表中的信息分类很少，常用的就只有用户类列和权限类列，没有用户的创建时间。
可以通过新增一列来记录用户的创建时间。
alter table mysql.user add column create_time timestamp default current_timestamp;
这样以后新建用户都会记录创建时间。但是显然，对于已有的用户是没有记录时间的，它们的值都为 ’0000-00-00 00:00:00’。
MariaDB [mysql]> select host,user,create_time from mysql.user;
+———————+———–+———————+
| host | user | create_time |
+———————+———–+———————+
| localhost | root | 2018-04-21 05:58:19 |
| 127.0.0.1 | root | 2018-04-21 05:58:19 |
| ::1 | root | 2018-04-21 05:58:19 |
| localhost | | 2018-04-21 05:58:19 |
| 192.168.100.% | root | 2018-04-21 05:58:19 |
| 192.168.100.1 | long | 2018-04-21 05:58:19 |
| 127.0.0.1 | longshuai | 2018-04-21 05:58:19 |
| 192.168.100.1 | longshuai | 0000-00-00 00:00:00 |
+———————+———–+———————+
2.4 查看用户权限
可以使用 show grants 语句查看某个 user 的权限信息。
例如：
MariaDB [mysql]> show grants for ‘root’@’localhost’;
Grants for root@localhost
—————————————————————————————————————–
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ WITH GRANT OPTION
GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT OPTION
MariaDB [mysql]>SHOW GRANTS FOR ‘long’@’192.168.100.1’;
Grants for long@192.168.100.1
———————————————————————————————————–
GRANT USAGE ON *.* TO ‘long’@’192.168.100.1’ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’
GRANT SELECT ON `test`.* TO ‘long’@’192.168.100.1’
2.5 revoke 命令的严格性
revoke 命令回收权限时必须要明确指定回收的数据库对象以及用户名，其中 usage 权限无法回收。特别要说明的是 revoke all，当你以为它会回收所有权限的时候，它可能一点权限都没有回收。也就是说 revoke 命令的书写非常严格。
用户 ‘long’@’192.168.100.1’ 在 *.* 上具有 usage 权限，在 test.* 上具有 select 权限。
MariaDB [mysql]> SHOW GRANTS FOR ‘long’@’192.168.100.1’;
Grants for long@192.168.100.1
———————————————————————————————————–
GRANT USAGE ON *.* TO ‘long’@’192.168.100.1’ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’
GRANT SELECT ON `test`.* TO ‘long’@’192.168.100.1’
对该用户在 *.* 上进行 revoke all，再次查看权限，发现权限根本一点变化都没有。因为 usage 权限无法回收，而 select 权限是在 test.* 上而非 *.* 上。
REVOKE ALL ON *.* FROM ‘long’@’192.168.100.1’;
要回收 test.* 上的 select 权限，必须在 revoke 中指定 test.*，而不能是 *.*。以下两个语句都能回收。
revoke select on test.* from ‘long’@’192.168.100.1’;
revoke all on test.* from ‘long’@’192.168.100.1’;
2.6 删除用户
直接使用 drop user 命令或者从 mysql.user 表中删除对应记录。
drop user user_name1,username2…
注意，删除表中用户记录的时候不会从现有用户中回收对该表的权限，当下次再创建同名表的时候，会自动为用户授予该表的权限造成权限外流。
因此，建议使用 drop user 语句来删除用户。
3. 设置密码和恢复 root 密码
3.1 设置密码
(1)grant all on *.* to ‘root’@’localhost’ identified by ‘123456’ with grant option;
(2)grant usage on *.* to ‘root’@’localhost’ identified by ‘123456’ with grant option;
使用 usage 权限表示在不影响现有权限的情况下使用 grant 来修改密码。
(3)set password [for ‘root’@’localhost’] =password(‘123456’);
password 函数中必须加引号，不写 user 时是为当前用户修改。
(4)alter user root@localhost identified by ‘123456’;
(5)mysqladmin -uroot -h localhost -p’old_password’ password ‘new_password’;
(6)update mysql.user set password=password(‘123456′) where user=’root’ and host=’localhost’;
其中 grant 和 set password 语句可以直接刷新权限表，其他语句需要使用 flush privileges 或其他刷新语句。
3.2 恢复 root 密码
可以在启动 mysql 服务时使用 mysqld_safe 服务程序并指定 ”–skip-grant-tables” 选项表示跳过授权表，这样登陆 mysql 服务器将不需要任何权限，包括密码认证也不需要，但是同样受限的是不能操作任何权限相关的内容，比如修改权限，刷新授权表等。这通常是 mysql 管理员密码忘记的时候使用的选项。由于跳过授权表使得 mysql 服务器极不安全，任何用户都能直接登录服务器，所以通常和 ”–skip-networking” 选项一起使用来禁止来自网络的服务器连接请求，这样只能使用 localhost 或者 127.0.0.1 作为 host 来登录。
另外，使用 mysqld_safe 启动无授权表的服务前要停止已有的 MySQL 实例。由于跳过授权表无法操作权限相关内容，所以修改 mysql.user 表中的管理员账号的密码字段是唯一修改方法。修改密码后记得重启 MySQL 服务。
步骤如下：
[root@linuxidc mysql]# service mysqld stop
[root@linuxidc mysql]# mysqld_safe –skip-grant-tables –skip-networking &
[root@linuxidc mysql]# mysql
mysql> update mysql.user set password=password(“123456”) where user=’root’ and host=’localhost’;
mysql> flush privileges;
mysql> select user,host,password from mysql.user where user=’root’ and host=’localhost’;
+——+———–+——————————————-+
| user | host | password |
+——+———–+——————————————-+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+——+———–+——————————————-+
1 row in set
mysql> \q
[root@linuxidc mysql]# service mysqld stop
[root@linuxidc mysql]# service mysqld start
[root@linuxidc mysql]# mysql -uroot -p123456
mysql> \q
如果要找回多实例的密码，则在 mysqld_safe 命令中使用 –defaults-file 指定对应的配置文件即可。
