[MySQL] 探索权限表

MySQL权限表是指在mysql数据库下的5张表:user, db, tables_priv, columns_priv, procs_priv,这5张表记录了所有的用户及其权限信息,MySQL就是通过这5张表控制用户访问的。本文将探索这5张权限表。


MySQL权限表的结构和内容

1、user:记录账号、密码、全局性权限信息等。

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       | 
| User                   | char(16)                          | NO   | PRI |         |       | 
| Password               | char(41)                          | NO   |     |         |       | 
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       | 
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| File_priv              | enum('N','Y')                     | NO   |     | N       |       | 
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| References_priv        | enum('N','Y')                     | NO   |     | N       |       | 
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       | 
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       | 
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       | 
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       | 
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       | 
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       | 
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       | 
| ssl_cipher             | blob                              | NO   |     | NULL    |       | 
| x509_issuer            | blob                              | NO   |     | NULL    |       | 
| x509_subject           | blob                              | NO   |     | NULL    |       | 
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       | 
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       | 
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       | 
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       | 
| plugin                 | char(64)                          | YES  |     |         |       | 
| authentication_string  | text                              | YES  |     | NULL    |       | 
+------------------------+-----------------------------------+------+-----+---------+-------+
1)*_priv:适用MySQL服务器全局性的权限,假设某个账号拥有Delete_priv的全局性权限,则表示它可以对任何表进行删除数据的操作,这非常危险,所有一般只有超级用户root有这样的权限,其它普通用户没有。

2)max_*:资源管理列,用于规定账号的资源使用上限,其中:

    max_questions:每小时发出的语句数上限

    max_updates:每小时发出的修改类语句数上限

    max_connections:每小时连接数上限

    max_user_connections:允许保有的连接数上限

3)SSL相关列:

    ssl_type,ssl_cipher,x509_isuser, x509_subject

2、db:记录数据库相关权限

mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       | 
| Db                    | char(64)      | NO   | PRI |         |       | 
| User                  | char(16)      | NO   | PRI |         |       | 
| Select_priv           | enum('N','Y') | NO   |     | N       |       | 
| Insert_priv           | enum('N','Y') | NO   |     | N       |       | 
| Update_priv           | enum('N','Y') | NO   |     | N       |       | 
| Delete_priv           | enum('N','Y') | NO   |     | N       |       | 
| Create_priv           | enum('N','Y') | NO   |     | N       |       | 
| Drop_priv             | enum('N','Y') | NO   |     | N       |       | 
| Grant_priv            | enum('N','Y') | NO   |     | N       |       | 
| References_priv       | enum('N','Y') | NO   |     | N       |       | 
| Index_priv            | enum('N','Y') | NO   |     | N       |       | 
| Alter_priv            | enum('N','Y') | NO   |     | N       |       | 
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       | 
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       | 
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       | 
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       | 
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       | 
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       | 
| Execute_priv          | enum('N','Y') | NO   |     | N       |       | 
| Event_priv            | enum('N','Y') | NO   |     | N       |       | 
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       | 
+-----------------------+---------------+------+-----+---------+-------+
1)*_priv:适用于某个数据库的权限
3、tables_priv:表级别的权限

mysql> desc mysql.tables_priv; 
+-------------+--------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                                               | Null | Key | Default           | Extra                       |
+-------------+--------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                                           | NO   | PRI |                   |                             | 
| Db          | char(64)                                                           | NO   | PRI |                   |                             | 
| User        | char(16)                                                           | NO   | PRI |                   |                             | 
| Table_name  | char(64)                                                           | NO   | PRI |                   |                             | 
| Grantor     | char(77)                                                           | NO   | MUL |                   |                             | 
| Timestamp   | timestamp                                                          | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | 
| Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant',
                'References','Index','Alter','Create View','Show view','Trigger')  | NO   |     |                   |                             | 
| Column_priv | set('Select','Insert','Update','References')                       | NO   |     |                   |                             | 
+-------------+--------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
上面的Column_priv比较奇怪,因为照理说tables_priv只显示表级别的权限,列级别的权限应该在columns_priv里显示才对。后来查了资料才知道,原来这是为了提高权限检查时的性能,试想一下,权限检查时,如果发现tables_priv.Column_priv为空,就不需要再检查columns_priv表了,这种情况在现实中往往占大多数。

4、columns_priv:列级别的权限

mysql> desc mysql.columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                       |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                     | NO   | PRI |                   |                             | 
| Db          | char(64)                                     | NO   | PRI |                   |                             | 
| User        | char(16)                                     | NO   | PRI |                   |                             | 
| Table_name  | char(64)                                     | NO   | PRI |                   |                             | 
| Column_name | char(64)                                     | NO   | PRI |                   |                             | 
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | 
| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                             | 
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
5、procs_priv:存储过程和函数的权限
mysql> desc mysql.procs_priv; 
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                                   | Null | Key | Default           | Extra                       |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Host         | char(60)                               | NO   | PRI |                   |                             | 
| Db           | char(64)                               | NO   | PRI |                   |                             | 
| User         | char(16)                               | NO   | PRI |                   |                             | 
| Routine_name | char(64)                               | NO   | PRI |                   |                             | 
| Routine_type | enum('FUNCTION','PROCEDURE')           | NO   | PRI | NULL              |                             | 
| Grantor      | char(77)                               | NO   | MUL |                   |                             | 
| Proc_priv    | set('Execute','Alter Routine','Grant') | NO   |     |                   |                             | 
| Timestamp    | timestamp                              | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | 
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+

如何控制客户访问?

下面讲讲MySQL服务器如何通过以上介绍的5张权限表控制客户访问。

1、用户连接时的检查

1)当用户连接时,MySQL服务器首先从user表里匹配host, user, password,匹配不到则拒绝该连接

2)接着检查user表的max_connections和max_user_connections,如果超过上限则拒绝连接

3)检查user表的SSL安全连接,如果有配置SSL,则需确认用户提供的证书是否合法

只有上面3个检查都通过后,服务器才建立连接,连接建立后,当用户执行SQL语句时,需要如下检查。

2、执行SQL语句时的检查

1)从user表里检查max_questions和max_updates,如果超过上限则拒绝执行SQL

下面几步是进行权限检查:

2)首先检查user表,看是否具有相应的全局性权限,如果有,则执行,没有则继续下一步检查

3)接着到db表,看是否具有数据库级别的权限,如果有,则执行,没有则继续下一步检查

4)最后到tables_priv, columns_priv, procs_priv表里查看是否具有相应对象的权限

从以上的过程我们可以知道,MySQL检查权限是一个比较复杂的过程,所以为了提高性能,MySQL的启动时就会把这5张权限表加载到内存。


注意事项

1、尽量使用create user, grant等语句,而不要直接修改权限表。

虽然create user, grant等语句底层也是修改权限表,和直接修改权限表的效果是一样的,但是,对于非高手来说,采用封装好的语句肯定不会出错,而如果直接修改权限表,难免会漏掉某些表。而且,修改完权限表之后,还需要执行flush privileges重新加载到内存,否则不会生效。

2、把匿名用户删除掉。

匿名用户没有密码,不但不安全,还会产生一些莫名其妙的问题,强烈建议删除。


没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试

关闭