背景
- OS Version : rhel 6.4
- MySQL Version : 5.6.31
权限
权限列表
| Privilege | Column | Context |
|---|---|---|
| CREATE | Create_priv | databases, tables, or indexes |
| DROP | Drop_priv | database, tables, or views |
| GRANT OPTION | Grant_priv | database, tables, or stored routines |
| LOCK TABLES | lock_tables_priv | databases |
| REFERENCES | References_priv | databases or tables, columns |
| EVENT | Event_priv | databases |
| ALTER | Alter_priv | tables |
| DELETE | Delete_priv | tables |
| INDEX | Index_priv | tables |
| INSERT | Insert_priv | tables or columns |
| SELECT | Select_priv | tables or columns |
| UPDATE | Update_priv | tables or columns |
| CREATE TEMPORARY TABLES | Create_tmp_table_priv | tables |
| TRIGGER | trigger_priv | tables |
| CREATE VIEW | Create_view_priv | views |
| SHOW VIEW | Show_view_priv | views |
| ALTER ROUTINE | alter_routine_priv | stored routines |
| CREATE ROUTINE | create_routine_priv | stored routines |
| EXECUTE | execute_priv | stored routines |
| FILE | file_priv | file access on server host (Global) |
| CREATE TABLESPACE | create_tablespace_priv | server administration (Global) |
| CREATE USER | create_user_priv | server administration (Global) |
| PROCESS | process_priv | server administration (Global) |
| PROXY | proxy_priv | server administration (user to user) |
| RELOAD | reload_priv | server administration (Global) |
| REPLICATION CLIENT | repl_client_priv | server administration (Global) |
| REPLICATION SLAVE | repl_slave_priv | server administration (Global) |
| SHOW DATABASES | show_db_priv | server administration (Global) |
| SHUTDOWN | shutdown_priv | server administration (Global) |
| SUPER | super_priv | server administration (Global) |
| ALL [PRIVILEGES] | server administration | |
| USAGE | server administration |
按权限的种类分类
- 数据访问相关权限
- 库表结构相关权限
- 服务相关权限
权限相关表单
MySQL权限信息存储在“mysql”数据中
其中:
- “user”表负责存储用户信息，及用户本身所拥有的权限；
- “db”表负责存储用户与db的权限关系;
- “tables_priv”负责存在用户与table的权限关系;
- “columns_priv”负责存储用户与column的权限关系;
user表结构
| Field | Type | Null | Key | Default |
|---|---|---|---|---|
| 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 | mysql_native_password | |
| authentication_string | text | YES | NULL | |
| password_expired | enum(‘N’,’Y’) | NO | N |
db表结构
| Field | Type | Null | Key | Default |
|---|---|---|---|---|
| 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 |
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 |
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 |
验证流程
