用了mysql好多年沦补，很少關(guān)注mysql自帶庫(kù)鸟悴，自然也不知道這個(gè)庫(kù)里存放些什么掏击，在看《MySQL排錯(cuò)指南》時(shí)看到它用自帶表在幫助定位問(wèn)題棍厌，也想起前段時(shí)間用java自帶命令查看性能問(wèn)題，才想明白為什么一直尋找多快好省地工具來(lái)提升自身能力卻無(wú)果的原因掀淘，產(chǎn)品的設(shè)計(jì)者們比任何人都了解自己的產(chǎn)品旬蟋，也知道什么地方會(huì)出錯(cuò)，為了防范這些問(wèn)題革娄，必定會(huì)有預(yù)防措施和保護(hù)機(jī)制咖为，也一定會(huì)有排錯(cuò)能力秕狰，會(huì)有地方記錄產(chǎn)品的運(yùn)行軌跡，我們想更好學(xué)習(xí)產(chǎn)品和使用產(chǎn)品時(shí)躁染，了解它是怎么做出來(lái)鸣哀，能做什么，不能做什么吞彤，配置在什么情況起效等就變的很重要我衬。
想通這些，那接下來(lái)就來(lái)學(xué)習(xí)下mysql權(quán)限控制的設(shè)計(jì)饰恕，以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 |
+---------------------------+
其中權(quán)限相關(guān)的有6張表：
user： 用戶賬號(hào)挠羔、全局權(quán)限
db： 庫(kù)級(jí)別權(quán)限
tables_priv： 表級(jí)別權(quán)限
colums_priv： 列級(jí)別權(quán)限
procs_priv： 存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)相關(guān)的權(quán)限
proxies_priv： 代理用戶權(quán)限
- user 用戶賬號(hào)、全局權(quán)限
GRANT ALL ON .和REVOKE ALL ON .只授予和撤銷全局權(quán)限埋嵌。
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)
查看某個(gè)用戶有哪些權(quán)限破加，帶有*_priv值為Y，表示具有相應(yīng)的權(quán)限,值為N雹嗦，表示不具有相應(yīng)的權(quá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 庫(kù)級(jí)別權(quán)限
GRANT ALL ON db_name.和REVOKE ALL ON db_name.只授予和撤銷數(shù)據(jù)庫(kù)權(quán)限
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)
少的權(quán)限較多，當(dāng)執(zhí)行如下命令報(bào)錯(cuò)時(shí)了罪，請(qǐng)第一時(shí)間確認(rèn)自身用戶是否具有執(zhí)行該命令的權(quán)限锭环。
#查看自身權(quán)限
show grants;
#查看其他用戶權(quán)限
show grants for test@'localhost';
Reload_priv 執(zhí)行刷新和重新加載MySQL所用各種內(nèi)部緩存的特定命令，包括日志泊藕、權(quán)限辅辩、主機(jī)、查詢和表
Shutdown_priv 關(guān)閉MySQL服務(wù)器娃圆。非roo應(yīng)謹(jǐn)慎處理
Process_priv 通過(guò)SHOW PROCESSLIST命令查看其他用戶的進(jìn)程
File_priv 執(zhí)行SELECT INTO OUTFILE和LOAD DATA INFILE命令搜索
Show_db_priv 查看服務(wù)器上所有數(shù)據(jù)庫(kù)的名字玫锋，包括用戶擁有足夠訪問(wèn)權(quán)限的數(shù)據(jù)庫(kù)。
Super_priv 執(zhí)行某些強(qiáng)大的管理功能讼呢，例如KILL殺進(jìn)程撩鹿，使用SET GLOBAL修改全局MySQL變量，執(zhí)行關(guān)于復(fù)制和日志的各種命令吝岭。
Repl_slave_priv 讀取用于維護(hù)復(fù)制數(shù)據(jù)庫(kù)環(huán)境的二進(jìn)制日志文件三痰。此用戶位于主系統(tǒng)中吧寺，有利于主機(jī)和客戶機(jī)之間的通信
Repl_client_priv 確定復(fù)制從服務(wù)器和主服務(wù)器的位置
Create_user_priv 執(zhí)行CREATE USER命令
Create_tablespace_priv 執(zhí)行Create tablespace 命令窜管。獨(dú)立的表空間
創(chuàng)建數(shù)據(jù)庫(kù)，并創(chuàng)建用戶test稚机，創(chuàng)建表test幕帆、test1，賦予該用戶訪問(wèn)test數(shù)據(jù)庫(kù)的所有權(quán)限
#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
驗(yàn)證test賬號(hào)的權(quán)限是否正確
# mysql -utest -ptest
mysql> show databases;((只能看到數(shù)據(jù)庫(kù)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 表級(jí)別權(quán)限
GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表權(quán)限
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賬號(hào)登陸赖条，在數(shù)據(jù)庫(kù)test,創(chuàng)建test1用戶賦予對(duì)表test1所有權(quán)限失乾。
#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)
驗(yàn)證用戶test1的權(quán)限是否正確
#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只對(duì)數(shù)據(jù)庫(kù)test.test1表有操作權(quán)限常熙。
- colums_priv 列級(jí)別權(quán)限
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賬號(hào)登陸，創(chuàng)建test2用戶賦予對(duì)表test1.id所有權(quán)限碱茁。
GRANT SELECT(id),UPDATE(id),Insert(id),References(id) ON test.test1 to test2@'localhost' IDENTIFIED BY 'test2' WITH GRANT OPTION;
驗(yàn)證test2用戶權(quán)限是否正確
[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;(查詢表中所有字段的數(shù)據(jù)時(shí)報(bào)沒(méi)有權(quán)限)
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： 存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)相關(guān)的權(quán)限
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 官網(wǎng)示例
GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';
- proxies_priv 代理用戶權(quán)限
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官網(wǎng)示例
GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';
- 其他
問(wèn)題1：去除權(quán)限(REVOKE)和刪除(DROP)用戶的區(qū)別
刪除用戶后裸卫，用戶將不能登陸，
去除權(quán)限后纽竣，用戶還是可以登錄的墓贿，只不過(guò)沒(méi)有相應(yīng)的操作權(quán)限
DROP USER 'jeffrey'@'localhost';
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
問(wèn)題2：為什么新建用戶時(shí)@'*' 不包含localhost
%允許來(lái)自任何ip的連接
localhost允許本機(jī)的連接
問(wèn)題3：為什么user表中匿名賬戶時(shí)其他賬號(hào)無(wú)法登錄
數(shù)據(jù)庫(kù)新建好之后會(huì)創(chuàng)建兩個(gè)賬號(hào)一個(gè)root,一個(gè)匿名，匿名可用于本機(jī)訪問(wèn)數(shù)據(jù)庫(kù)蜓氨，但是當(dāng)用其他用戶訪問(wèn)時(shí)聋袋，由于匿名用戶的Host列值比'user'@'%'賬戶更具體，在user表排序順序中匿名排在前面穴吹，導(dǎo)致其他用戶登錄變成匿名登錄幽勒，而導(dǎo)致登錄失敗
處理方式
1.UPDATE user set password=PASSWORD('your password') where user='';
FLUSH PRIVILEGES;
2.delete from user where USER='';
FLUSH PRIVILEGES;
整理到這里參考的文章
MySQL權(quán)限的架構(gòu)體系
權(quán)限管理
MySQL官網(wǎng)
最后忍不住參考他人的圖畫(huà)了兩張圖
MySQL權(quán)限控制圖-1.jpg
MySQL權(quán)限控制圖-2.jpg
