MariaDB/Mysql 权限管理

本人工作使用并没有多少权限管理场景，仅有以下几种
1）创建多个库（或表），给不同用户访问不同库（或表）的权限
2）创建多个库（或表），给指定用户在指定ip上访问某个库（或表）的权限（类似临时授权）
3）限制某个用户对某个库（或表）的不同操作权限

本文为学习笔记，是在MariaDB上进行操作的，对mysql可能有稍许出入。

一、添加用户

创建用户的语句：
create user newuser1@localhost identified by '123456';
create user newuser2@'%' identified by '123456';
也可以：
insert into mysql.user(user,host,password) values('newuser3','localhost',password('123456'));
insert into mysql.user(user,host,password) values('newuser4','%',password('123456'));
注意：MariaDB 10.4及其高版本，mysql.global_priv表取代了mysql.user表。mysql.user则成为了一个视图，好像无法使用insert了。

创建用户必须要输入的两个字段：
Host	char(60)	不可为空	主键字段	地址，和user一起使用，提供帐号唯一标识	 
User	char(80)	不可为空	主键字段 	用户，和host一起使用，提供帐号唯一标识	 
由上可知MariaDB/MySQL中的user由用户名和主机名构成，如"root@localhost"，同用户名但不同主机名对MySQL/MariaDB来讲是不同的，也就是说"root@localhost"和"root@127.0.0.1"是不同的用户，尽管它们都是本机的root。

至于密码Password为哈希密码，由PASSWORD（str）函数生成，可以创建的时候不设置，然后单独设置：
create user newuser5@'%';
SET PASSWORD FOR 'newuser5'@'%' = PASSWORD('123456');

创建了用户后就可以给用户分配权限了查看用户的权限：
MariaDB [(none)]> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*A688FA07B69781589218D3D7391FA680195BCD5F' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

　　

二、MariaDB/Mysql的权限实现逻辑

1.权限验证

本部分来自大牛的博客https://www.cnblogs.com/f-ck-need-u/p/8994220.html，感谢大牛的分享

在MariaDB/MySQL服务器启动后会载入权限表到内存中，当用户要连接服务器，会读取权限表来验证和分配权限，即在内存中进行权限的读取和写入。
MariaDB/MySQL中的权限系统经过两步验证：
合法性验证：验证user是否合法，合法者允许连接服务器，否则拒绝连接。
权限验证和分配：对通过合法性验证的用户分配对数据库中各对象的操作权限。

2.权限表

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数据库。

需要说明的是，从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;

在服务器启动时读取权限表到内存中，从此时开始权限表生效。
之后使用grant、revoke、set password 等命令也会隐含的刷新权限表到内存中。
另外，使用显式的命令flush privileges或mysqladmin flush-privileges或mysqladmin reolad也会将上述几张权限表重新刷到内存中以供后续的身份验证和权限验证、分配。

三、对用户进行权限管理

1）使用grant对用户进行授权

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 

查看权限：

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

 

2）使用REVOKE回收权限

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user]　

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';

 故，进行revoke前要先查看权限是什么样的。