用了mysql好多年，很少关注mysql自带库，自然也不知道这个库里存放些什么，在看《MySQL排错指南》时看到它用自带表在帮助定位问题，也想起前段时间用java自带命令查看性能问题，才想明白为什么一直寻找多快好省地工具来提升自身能力却无果的原因，产品的设计者们比任何人都了解自己的产品，也知道什么地方会出错，为了防范这些问题，必定会有预防措施和保护机制，也一定会有排错能力，会有地方记录产品的运行轨迹，我们想更好学习产品和使用产品时，了解它是怎么做出来，能做什么，不能做什么，配置在什么情况起效等就变的很重要。
想通这些，那接下来就来学习下mysql权限控制的设计，以Server version: 5.6.29 为例
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
其中权限相关的有6张表：
user： 用户账号、全局权限
db： 库级别权限
tables_priv： 表级别权限
colums_priv： 列级别权限
procs_priv： 存储过程和存储函数相关的权限
proxies_priv： 代理用户权限
- user 用户账号、全局权限
GRANT ALL ON .和REVOKE ALL ON .只授予和撤销全局权限。
mysql> desc 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 | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.01 sec)
查看某个用户有哪些权限，带有*_priv值为Y，表示具有相应的权限,值为N，表示不具有相应的权限。
mysql> select * from user where user='admin' \G;
*************************** 1. row ***************************
Host: %
User: admin
Password: *FE2514E62270CA5DC740A614263E6A37CA468E07
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: Y
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
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
- db 库级别权限
GRANT ALL ON db_name.和REVOKE ALL ON db_name.只授予和撤销数据库权限
mysql> desc 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 | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
少的权限较多，当执行如下命令报错时，请第一时间确认自身用户是否具有执行该命令的权限。
#查看自身权限
show grants;
#查看其他用户权限
show grants for test@'localhost';
Reload_priv 执行刷新和重新加载MySQL所用各种内部缓存的特定命令，包括日志、权限、主机、查询和表
Shutdown_priv 关闭MySQL服务器。非roo应谨慎处理
Process_priv 通过SHOW PROCESSLIST命令查看其他用户的进程
File_priv 执行SELECT INTO OUTFILE和LOAD DATA INFILE命令搜索
Show_db_priv 查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库。
Super_priv 执行某些强大的管理功能，例如KILL杀进程，使用SET GLOBAL修改全局MySQL变量，执行关于复制和日志的各种命令。
Repl_slave_priv 读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中，有利于主机和客户机之间的通信
Repl_client_priv 确定复制从服务器和主服务器的位置
Create_user_priv 执行CREATE USER命令
Create_tablespace_priv 执行Create tablespace 命令。独立的表空间
创建数据库，并创建用户test，创建表test、test1，赋予该用户访问test数据库的所有权限
#mysql -uroot -proot
create database test default character set 'utf8';
create table test (id int,name varchar(20));
create table test1 (id int,name varchar(20));
GRANT ALL ON test.* TO test@'localhost' IDENTIFIED BY 'test' WITH GRANT OPTION;
mysql> select * from db\G
*************************** 1. row ***************************
Host: localhost
Db: test
User: test
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
验证test账号的权限是否正确
# mysql -utest -ptest
mysql> show databases;((只能看到数据库test)
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
| test1 |
+----------------+
2 rows in set (0.00 sec)
- tables_priv 表级别权限
GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限
mysql> desc 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 | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
以root账号登陆，在数据库test,创建test1用户赋予对表test1所有权限。
#mysql -uroot -proot
GRANT ALL ON test.test1 TO test1@'localhost' IDENTIFIED BY 'test1' WITH GRANT OPTION;
mysql> select * from tables_priv;
+-----------+------+-------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+-----------+------+-------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| localhost | test | test1 | test1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger | |
+-----------+------+-------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)
验证用户test1的权限是否正确
#mysql -utest1 -ptest1
mysql> show tables;(只能看到test1表)
+----------------+
| Tables_in_test |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
mysql> create table test2 (id int,name varchar(20));
ERROR 1142 (42000): CREATE command denied to user 'test1'@'localhost' for table 'test6'
mysql> select * from test;
ERROR 1142 (42000): SELECT command denied to user 'test1'@'localhost' for table 'test'
mysql>
mysql> insert into test1(id,name) values (1,'a');
Query OK, 1 row affected (0.02 sec)
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
1 row in set (0.00 sec)
test1只对数据库test.test1表有操作权限。
- colums_priv 列级别权限
mysql> desc 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 | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)
以root账号登陆，创建test2用户赋予对表test1.id所有权限。
GRANT SELECT(id),UPDATE(id),Insert(id),References(id) ON test.test1 to test2@'localhost' IDENTIFIED BY 'test2' WITH GRANT OPTION;
验证test2用户权限是否正确
[root@chances123 ~]# mysql -utest2 -ptest2
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from test1;(查询表中所有字段的数据时报没有权限)
ERROR 1142 (42000): SELECT command denied to user 'test2'@'localhost' for table 'test1'
mysql>
mysql> select id from test1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
- procs_priv： 存储过程和存储函数相关的权限
mysql> desc 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 |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
MySQL 官网示例
GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';
- proxies_priv 代理用户权限
mysql> desc proxies_priv;
+--------------+------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Proxied_host | char(60) | NO | PRI | | |
| Proxied_user | char(16) | NO | PRI | | |
| With_grant | tinyint(1) | NO | | 0 | |
| Grantor | char(77) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)
MySQL官网示例
GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';
- 其他
问题1：去除权限(REVOKE)和删除(DROP)用户的区别
删除用户后，用户将不能登陆，
去除权限后，用户还是可以登录的，只不过没有相应的操作权限
DROP USER 'jeffrey'@'localhost';
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
问题2：为什么新建用户时@'*' 不包含localhost
%允许来自任何ip的连接
localhost允许本机的连接
问题3：为什么user表中匿名账户时其他账号无法登录
数据库新建好之后会创建两个账号一个root,一个匿名，匿名可用于本机访问数据库，但是当用其他用户访问时，由于匿名用户的Host列值比'user'@'%'账户更具体，在user表排序顺序中匿名排在前面，导致其他用户登录变成匿名登录，而导致登录失败
处理方式
1.UPDATE user set password=PASSWORD('your password') where user='';
FLUSH PRIVILEGES;
2.delete from user where USER='';
FLUSH PRIVILEGES;
整理到这里参考的文章
MySQL权限的架构体系
权限管理
MySQL官网
最后忍不住参考他人的图画了两张图
MySQL权限控制图-1.jpg
MySQL权限控制图-2.jpg
