MySQL权限表探索

MySQL权限表探索2014-05-15 Zhu_Julian 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 | | +-----------------------+---------------+------+-----+---------+-------+