SQL - MySQL 权限管理分配详解

2022年7月14日

MySQL

数据类型

大约 7 分钟

SQL - MySQL 权限管理分配详解

之前一直忽视了MySQL的权限这一块的内容，以为一般般的知识点，随时用随时学就好了，导致自己这方面稍微有点不太明白的地方，总是踩坑，所以后来就总结一下：

1、MySQL权限系统的工作原理

对连接的用户进行身份验证，合法的用户通过认证并建立连接。不合法的用户拒绝连接.
对通过认证的合法用户赋予相应的权限，用户可以在这些权限范围内对数据库做相应的操作。

注意两点：

MySQL通过IP地址和用户名联合进行确认的，同样的一个用户名如果来自不同的IP地址，MySQL则视为不同的用户。
MySQL的权限在数据库启动的时候就载入内存了，当用户通过身份认证后，就在内存中进行相应权限的操作。

2、权限表的存取

”mysql“数据库中有三个重要的权限表：

这三个表：user、host、db中，最重要的权限表是user表，其次是db表，要掌握这两个。user标准一共分为四部分：用户列、权限列、安全列、资源控制列。

用的最多的就是用户列和权限列，权限列又分为普通权限和管理权限。普通权限指对数据库的操作：如select_priv、create_priv。而管理权限指对数据库的管理：process_priv、super_priv等。

3、用户通过权限认证、进行权限分配的流程

权限分配按照user->db->tables_priv->columns_priv的顺序进行权限分配，即先检查全局权限表user，如果user中对应权限为‘Y’，则此用户对所有的数据库的权限都为‘Y’，此时不再检查db、tables_priv和columns_priv这些表。
如果user中对应权限为‘N’，则到db表中检查此用户对应的具体数据库，并得到db中为‘Y’的权限；
如果db中相应的权限为‘N’，则检查table_priv中此数据库对应的具体表，取得表中为‘Y’的权限；
如果tables_priv中相应权限为'N'，则检查columns_priv中此表对应的具体列，取得列中为'Y'的权限。

我们可以看个例子：

4、账号管理

1. 我们常用的授权all privileges到底有哪些权限呢？以及带来的安全隐患有哪些？

mysql> grant all privileges on *.* to 'test'@'%' identified by '123456' ;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> select * from user where user='test'G
*************************** 1. row ***************************
Host: %
User: test
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
account_locked: N

看得出来当授予all privileges权限的时候，除了没有grant_priv权限和account_locked以为，其他的权限全部都有，这是非常危险的。并且还能从任意主机来登陆MySQL数据库，在安全方面做得相当差，所以一般情况下我们最好不要这样子设置。如果有必要的话才可以这样做，此外我们也会见到这样子的操作：

mysql> grant all privileges on *.* to 'test'@'%' identified by '123456' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

后面加上了”with grant option“参数，表示赋予grant_priv权限。那么此时这个用户真的是拥有了超级用户的管理权限了。在生产环境中最好慎用。

2. 创建账户的时候最好分配指定的权限，这样子安全也高

就像这样子的，让某个用户仅对某个数据库拥有一部分权限即可。本例中的权限适合于大多数应用账号。不过本例中的IP是设置为所有的主机都可连接，建议还是指定特定的主机进行连接。

注意：MySQL数据库的user表中host的值为”%“或为空，表示所有外部IP都可以连接，但是不宝库哦本地服务器localhost，因此要包括本地服务器，必须单独为localhost赋予权限。

3. 管理权限SUPER、PROCESS、FILE权限给用户

这三个权限要慎用给一般用户。最好是不要把这三个权限授权给管理员以外的用户。

我们来看一下这三个权限的作用是什么：

1）FILE权限

将数据库的信息通过SELECT...INTO OUTFILE...写到服务器上有写权限的目录下，作为文本格式存放，具有权限的目录也就是启动MYSQL时的用户权限目录。
可以将有读权限的文本文件通过LOAD DATA INFILE...命令写入数据库表，如果这些表中存放了很重要的信息，将对系统造成很大的安全隐患。

2）PROCESS权限

PROCESS权限能被用来执行“show processlist”命令，查看当前所有用户执行的查询的明文文本，包括设定或改变密码的查询。在默认情况下，每个用户都可以执行“show processlist”命令，但是只能查询本用户的进程。因此，对PROCESS权限管理不当，有可能会使得普通用户能够看到管理员执行的命令。

3）SUPER权限

SUPER权限能执行kill命令，终止其他用户进程。在下面的例子中，普通用户拥有了SUPER权限后，便可以任意kill任何用户的进程。

4. 除root外，任何用户不要有mysql库user表的操作权限

由于MySQL中可以通过更改mysql数据库的user表进行权限的增加、删除、变更等操作，因此，除了root以外，任何用户都不应该拥有对user表的存取权限（SELECT、UPDATE、INSERT、DELETE等），否则容易造成系统的安全隐患。下例中对普通用户z3授予了user表的存取权限，看看会对系统产生了怎样的安全隐患。

5. 权限外流

DROP表的时候，其他用户对此表的权限并灭有被收回，这样导致重新创建同名的表时，以前其他用户对此表的权限会自动赋予，进而产生权限外流。因此，在删除表时，要同时取消其他用户在此表上的相应权限。

6. revoke命令的漏洞

这个是说用户被多次赋予权限，由于各种原因，需要将此用户的权限全部取消，此时revoke命令可能并不会按照我们的意愿执行。

mysql> show grants for 'test'@'%';
+-------------------------------------------------------------+
| Grants for test@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `haha`.* TO 'test'@'%'              |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

此时取消这个用户的全部权限

mysql> revoke all privileges on *.* from 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test'@'%';
+----------------------------------------------------+
| Grants for test@%                                  |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `haha`.* TO 'test'@'%'     |
+----------------------------------------------------+
2 rows in set (0.00 sec)

现在我们使用这个test用户登陆MySQL查看一下是否对haha这个数据库有操作权限。

mysql> select user();
+----------------+
| user()         |
+----------------+
| test@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> use haha
Database changed

mysql> insert into hehe values (1,'chaofeng');
Query OK, 1 row affected (0.00 sec)

真是没想到居然还能操作。

这个是MySQL权限机制造成的隐患，在一个数据库上多次赋予权限，权限会自动合并；但是在多个数据库上多次赋予权限，每个数据库上都会认为是单独的一组权限，必须在此数据库上用REVOKE命令来单独进行权限收回，而REVOKE ALL PRIVILEGES ON *.*并不会替用户自动完成这个过程。

所以说我们要亲自取消这个才行。

mysql> revoke all ON haha.* from 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test'@'%';
+----------------------------------------------------+
| Grants for test@%                                  |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' WITH GRANT OPTION |
+----------------------------------------------------+
1 row in set (0.00 sec)