SQL - MySQL 权限管理分配详解

2022年7月14日
大约 7 分钟

SQL - MySQL 权限管理分配详解

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

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

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

注意两点:

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

2、权限表的存取

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

图

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

用的最多的就是用户列和权限列,权限列又分为普通权限和管理权限。普通权限指对数据库的操作:如select_privcreate_priv。而管理权限指对数据库的管理:process_privsuper_priv等。

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

  1. 权限分配按照user->db->tables_priv->columns_priv的顺序进行权限分配,即先检查全局权限表user,如果user中对应权限为‘Y’,则此用户对所有的数据库的权限都为‘Y’,此时不再检查db、tables_priv和columns_priv这些表。
  2. 如果user中对应权限为‘N’,则到db表中检查此用户对应的具体数据库,并得到db中为‘Y’的权限;
  3. 如果db中相应的权限为‘N’,则检查table_priv中此数据库对应的具体表,取得表中为‘Y’的权限;
  4. 如果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)

这个时候就行了。

引用资料

  • http://t.zoukankan.com/FengGeBlog-p-10254569.html