前言
MySQL 的權(quán)限表在數(shù)據(jù)庫啟動(dòng)的時(shí)候就載入內(nèi)存，當(dāng)用戶通過身份認(rèn)證后，就在內(nèi)存中進(jìn)行相應(yīng)權(quán)限的存取，這樣，此用戶就可以在數(shù)據(jù)庫中做權(quán)限范圍內(nèi)的各種操作了。
下面話不多說了，來一起看看詳細(xì)的介紹吧
mysql 的權(quán)限體系大致分為5個(gè)層級(jí):
全局層級(jí)
全局權(quán)限適用于一個(gè)給定服務(wù)器中的所有數(shù)據(jù)庫。這些權(quán)限存儲(chǔ)在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤銷全局權(quán)限。
數(shù)據(jù)庫層級(jí)
數(shù)據(jù)庫權(quán)限適用于一個(gè)給定數(shù)據(jù)庫中的所有目標(biāo)。這些權(quán)限存儲(chǔ)在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤銷數(shù)據(jù)庫權(quán)限。
表層級(jí)
表權(quán)限適用于一個(gè)給定表中的所有列。這些權(quán)限存儲(chǔ)在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表權(quán)限。
列層級(jí)
列權(quán)限適用于一個(gè)給定表中的單一列。這些權(quán)限存儲(chǔ)在mysql.columns_priv表中。當(dāng)使用REVOKE時(shí)，您必須指定與被授權(quán)列相同的列。
子程序?qū)蛹?jí)
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT權(quán)限適用于已存儲(chǔ)的子程序。這些權(quán)限可以被授予為全局層級(jí)和數(shù)據(jù)庫層級(jí)。而且，除了CREATE ROUTINE外，這些權(quán)限可以被授予為子程序?qū)蛹?jí)，并存儲(chǔ)在mysql.procs_priv表中。
這些權(quán)限信息存儲(chǔ)在下面的系統(tǒng)表中:
mysql.user
mysql.db
mysql.host
mysql.table_priv
mysql.column_priv
當(dāng)用戶連接進(jìn)來，mysqld會(huì)通過上面的這些表對(duì)用戶權(quán)限進(jìn)行驗(yàn)證！
一、權(quán)限表的存取
在權(quán)限存取的兩個(gè)過程中，系統(tǒng)會(huì)用到 “mysql” 數(shù)據(jù)庫(安裝 MySQL 時(shí)被創(chuàng)建，數(shù)據(jù)庫名稱叫“mysql”) 中 user、host 和 db 這3個(gè)最重要的權(quán)限表。
在這 3 個(gè)表中，最重要的表示 user 表，其次是 db 表，host 表在大多數(shù)情況下并不使用。
user 中的列主要分為 4 個(gè)部分：用戶列、權(quán)限列、安全列和資源控制列。
通常用的最多的是用戶列和權(quán)限列，其中權(quán)限列又分為普通權(quán)限和管理權(quán)限。普通權(quán)限用于數(shù)據(jù)庫的操作，比如 select_priv、super_priv 等。
當(dāng)用戶進(jìn)行連接時(shí)，權(quán)限表的存取過程有以下兩個(gè)過程：
先從 user 表中的 host、user 和 password 這 3 個(gè)字段中判斷連接的 IP、用戶名、和密碼是否存在于表中，如果存在，則通過身份驗(yàn)證，否則拒絕連接。
如果通過身份驗(yàn)證、則按照以下權(quán)限表的順序得到數(shù)據(jù)庫權(quán)限：user -> db -> tables_priv -> columns_priv。
在這幾個(gè)權(quán)限表中，權(quán)限范圍依次遞減，全局權(quán)限覆蓋局部權(quán)限。上面的第一階段好理解，下面以一個(gè)例子來詳細(xì)解釋一下第二階段。
為了方便測(cè)試，需要修改變量 sql_mode，不然會(huì)報(bào)錯(cuò)，如下
MySQL [(none)]> grant select on *.* to xxx@localhost; ERROR 1133 (42000): Can't find any matching row in the user table MySQL [(none)]> SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected, 2 warnings (0.07 sec) MySQL [(none)]> grant select on *.* to xxx@localhost; Query OK, 0 rows affected, 2 warnings (0.10 sec)
// sql_mode 默認(rèn)值中有 NO_AUTO_CREATE_USER (防止GRANT自動(dòng)創(chuàng)建新用戶，除非還指定了密碼) SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
1. 創(chuàng)建用戶
xxx@localhost，并賦予所有數(shù)據(jù)庫上的所有表的 select 權(quán)限
先查看user表顯示的權(quán)限狀態(tài)
MySQL [mysql]> select * from user where user="xxx" and host='localhost' \G; *************************** 1. row *************************** Host: localhost User: xxx Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N 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 password_last_changed: 2018-12-03 17:34:49 password_lifetime: NULL account_locked: N
再查看db表的權(quán)限狀態(tài)
MySQL [mysql]> select * from db where user="xxx" and host='localhost' \G; Empty set (0.03 sec)
可以發(fā)現(xiàn)user表中Select_priv: Y，其他均為N
DB表中則無記錄
也就是說，對(duì)所有數(shù)據(jù)庫都具有相同的權(quán)限的用戶并不需要記錄到 db 表，而僅僅需要將 user 表中的 select_priv 改為 “Y” 即可。換句話說，user 表中的每個(gè)權(quán)限都代表了對(duì)所有數(shù)據(jù)庫都有權(quán)限。
2. 將 xxx@localhost 上的權(quán)限改為只對(duì) db1 數(shù)據(jù)庫上所有表的 select 權(quán)限。
MySQL [mysql]> create database db1; Query OK, 1 row affected (0.01 sec) MySQL [mysql]> re^C MySQL [mysql]> revoke select on *.* from xxx@localhost; Query OK, 0 rows affected, 1 warning (0.06 sec) MySQL [mysql]> grant select on db1.* to xxx@localhost; Query OK, 0 rows affected, 1 warning (0.09 sec) MySQL [mysql]> select * from user where user='xxx'\G; *************************** 1. row *************************** Host: localhost User: xxx Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N
MySQL [mysql]> select * from db where user='xxx'\G; *************************** 1. row *************************** Host: localhost Db: db1 User: xxx Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N Event_priv: N Trigger_priv: N
這時(shí)候發(fā)現(xiàn)，user 表中的 select_priv 變?yōu)?“N” ，而 db 表中增加了 db 為 xxx 的一條記錄。也就是說，當(dāng)只授予部分?jǐn)?shù)據(jù)庫某些權(quán)限時(shí)，user 表中的相應(yīng)權(quán)限列保持 “N”，而將具體的數(shù)據(jù)庫權(quán)限寫入 db 表。table 和 column 的權(quán)限機(jī)制和 db 類似。
3. tables_priv記錄表權(quán)限
MySQL [db1]> create table t1(id int(10),name char(10)); Query OK, 0 rows affected (0.83 sec) MySQL [db1]> grant select on db1.t1 to mmm@localhost; Query OK, 0 rows affected, 2 warnings (0.06 sec) MySQL [mysql]> select * from user where user='mmm'\G; *************************** 1. row *************************** Host: localhost User: mmm Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N ... MySQL [mysql]> select * from db where user='mmm'\G; Empty set (0.00 sec) MySQL [mysql]> select * from tables_priv where user='mmm'\G; *************************** 1. row *************************** Host: localhost Db: db1 User: mmm Table_name: t1 Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select Column_priv: 1 row in set (0.00 sec) ERROR: No query specified MySQL [mysql]> select * from columns_priv where user='mmm'\G; Empty set (0.00 sec)
可以看見tables_priv表中增加了一條記錄，而在user db columns_priv三個(gè)表中沒有記錄
從上例可以看出，當(dāng)用戶通過權(quán)限認(rèn)證，進(jìn)行權(quán)限分配時(shí)，將按照 ==user -> db -> tables_priv -> columns_priv== 的順序進(jìn)行權(quán)限分配，即先檢查全局權(quán)限表 user，如果 user 中對(duì)應(yīng) 權(quán)限為 “Y”，則此用戶對(duì)所有數(shù)據(jù)庫的權(quán)限都為“Y”，將不再檢查 db、tables_priv 和 columns_priv；如果為“N”，則到 db 表中檢查此用戶對(duì)應(yīng)的具體數(shù)據(jù)庫，并得到 db 中為 “Y”的權(quán)限；如果 db 中相應(yīng)權(quán)限為 “N”，則再依次檢查tables_priv 和 columns_priv 中的權(quán)限，如果所有的都為“N”，則判斷為不具備權(quán)限。
二. 賬戶管理
授權(quán) grant
grant不僅可以用來授權(quán)，還可以用來創(chuàng)建用戶。
授權(quán)的語法：
grant 權(quán)限列表 on 庫名.表名 to 用戶@主機(jī) identified by '密碼';
創(chuàng)建用戶 p1 ，權(quán)限為可以在所有數(shù)據(jù)庫上執(zhí)行所有權(quán)限，只能從本地進(jìn)行連接
MySQL [mysql]> grant all privileges on *.* to p1@localhost; Query OK, 0 rows affected, 2 warnings (0.03 sec) MySQL [mysql]> select * from user where user='p1'\G *************************** 1. row *************************** Host: localhost User: p1 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: N 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 password_last_changed: 2018-12-03 18:11:01 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec)
除了 grant_priv 權(quán)限外，所有權(quán)限在user 表里面都是 “Y”。
增加對(duì) p1 的 grant 權(quán)限
MySQL [mysql]> grant all privileges on *.* to p1@localhost with grant option; Query OK, 0 rows affected, 1 warning (0.03 sec) MySQL [mysql]> select * from user where user='p1'\G *************************** 1. row *************************** Host: localhost User: p1 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 password_last_changed: 2018-12-03 18:11:01 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec)
設(shè)置密碼賦予grant權(quán)限
MySQL [mysql]> grant all privileges on *.* to p1@localhost identified by '123' with grant option; Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql]> select * from user where user='p1'\G *************************** 1. row *************************** Host: localhost User: p1 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: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2018-12-03 18:14:40 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec)
在5.7版本后的數(shù)據(jù)庫，密碼字段改為authentication_string
創(chuàng)建新用戶 p2，可以從任何 IP 連接，權(quán)限為對(duì) db1 數(shù)據(jù)庫里的所有表進(jìn)行 select 、update、insert 和 delete 操作，初始密碼為“123”
MySQL [mysql]> grant select,insert,update,delete on db1.* to 'p2'@'%' identified by '123'; Query OK, 0 rows affected, 1 warning (0.01 sec) MySQL [mysql]> select * from user where user='p2'\G; *************************** 1. row *************************** Host: % User: p2 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N ... Create_tablespace_priv: N 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: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2018-12-03 18:20:44 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec) ERROR: No query specified MySQL [mysql]> select * from db where user='p2'\G; *************************** 1. row *************************** Host: % Db: db1 User: p2 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N Event_priv: N Trigger_priv: N 1 row in set (0.00 sec)
user 表中的權(quán)限都是“N”，db 表中增加的記錄權(quán)限則都是“Y”,這樣只授予用戶適當(dāng)?shù)臋?quán)限，而不會(huì)授予過多的權(quán)限。
本例中的 IP 限制為所有 IP 都可以連接，因此設(shè)置為 “%”，mysql 數(shù)據(jù)庫中是通過 user 表的 host 字段來進(jìn)行控制，host 可以是以下類型的賦值。
注意: mysql 數(shù)據(jù)庫的 user 表中 host 的值為 “%” 或者空，表示所有外部 IP 都可以連接，但是不包括本地服務(wù)器 localhost，因此，如果要包括本地服務(wù)器，必須單獨(dú)為 localhost 賦予權(quán)限。
授予 super、process、file 權(quán)限給用戶 p3@%
MySQL [mysql]> grant super,process,file on db1.* to 'p3'@'%'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES MySQL [mysql]> grant super,process,file on *.* to 'p3'@'%'; Query OK, 0 rows affected (0.03 sec)
這幾個(gè)權(quán)限都是屬于管理權(quán)限，因此不能夠指定某個(gè)數(shù)據(jù)庫，on 后面必須跟 *.*,否則會(huì)提示錯(cuò)誤，如上
那這幾個(gè)權(quán)限是干啥的？
process通過這個(gè)權(quán)限，用戶可以執(zhí)行SHOW PROCESSLIST和KILL命令。默認(rèn)情況下，每個(gè)用戶都可以執(zhí)行SHOW PROCESSLIST命令，但是只能查詢本用戶的進(jìn)程。
擁有file權(quán)限才可以執(zhí)行 select ..into outfile和load data infile…操作，但是不要把file, process, super權(quán)限授予管理員以外的賬號(hào)，這樣存在嚴(yán)重的安全隱患。
super這個(gè)權(quán)限允許用戶終止任何查詢；修改全局變量的SET語句；使用CHANGE MASTER，PURGE MASTER LOGS
另外一個(gè)比較特殊的
usage權(quán)限
連接（登陸）權(quán)限，建立一個(gè)用戶，就會(huì)自動(dòng)授予其usage權(quán)限（默認(rèn)授予）。
該權(quán)限只能用于數(shù)據(jù)庫登陸，不能執(zhí)行任何操作；且usage權(quán)限不能被回收，也即``REVOKE用戶并不能刪除用戶。
查看賬號(hào)權(quán)限
賬號(hào)創(chuàng)建好后，可以通過如下命令查看權(quán)限:
show grants for user@host; MySQL [mysql]> show grants for p2@'%'; +-------------------------------------------------------------+ | Grants for p2@% | +-------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'p2'@'%' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `db1`.* TO 'p2'@'%' | +-------------------------------------------------------------+ 2 rows in set (0.00 sec)
更改賬戶權(quán)限
創(chuàng)建用戶賬號(hào)p4，權(quán)限為對(duì)db1所有表具有select權(quán)限
MySQL [mysql]> grant select on db1.* to p4@'%'; Query OK, 0 rows affected, 1 warning (0.01 sec) MySQL [mysql]> show grants for p4@'%'; +-------------------------------------+ | Grants for p4@% | +-------------------------------------+ | GRANT USAGE ON *.* TO 'p4'@'%' | | GRANT SELECT ON `db1`.* TO 'p4'@'%' | +-------------------------------------+ 2 rows in set (0.00 sec)
增加delete權(quán)限
MySQL [mysql]> grant delete on db1.* to p4@'%'; Query OK, 0 rows affected (0.01 sec) MySQL [mysql]> show grants for p4@'%'; +---------------------------------------------+ | Grants for p4@% | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'p4'@'%' | | GRANT SELECT, DELETE ON `db1`.* TO 'p4'@'%' | +---------------------------------------------+ 2 rows in set (0.00 sec)
和已有的 select 權(quán)限進(jìn)行合并
刪除delete權(quán)限
revoke 語句可以回收已經(jīng)賦予的權(quán)限
MySQL [mysql]> show grants for p4@'%'; +---------------------------------------------+ | Grants for p4@% | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'p4'@'%' | | GRANT SELECT, DELETE ON `db1`.* TO 'p4'@'%' | +---------------------------------------------+ 2 rows in set (0.00 sec) MySQL [mysql]> revoke delete on db1.* from p4@'%'; Query OK, 0 rows affected (0.01 sec) MySQL [mysql]> show grants for p4@'%'; +-------------------------------------+ | Grants for p4@% | +-------------------------------------+ | GRANT USAGE ON *.* TO 'p4'@'%' | | GRANT SELECT ON `db1`.* TO 'p4'@'%' | +-------------------------------------+ 2 rows in set (0.00 sec)
usage能revoke?
MySQL [mysql]> revoke select on db1.* from p4@'%'; Query OK, 0 rows affected (0.02 sec) MySQL [mysql]> show grants for p4@'%'; +--------------------------------+ | Grants for p4@% | +--------------------------------+ | GRANT USAGE ON *.* TO 'p4'@'%' | +--------------------------------+ 1 row in set (0.00 sec) MySQL [mysql]> revoke usage on db1.* from p4@'%'; ERROR 1141 (42000): There is no such grant defined for user 'p4' on host '%'
usage 權(quán)限不能被回收，也就是說，revoke 用戶并不能刪除用戶。
要徹底的刪除賬號(hào)，可以使用 drop user
drop user p4@'%';
賬號(hào)資源限制
創(chuàng)建 MySQL 賬號(hào)時(shí)，還有一類選項(xiàng)稱為賬號(hào)資源限制，這類選項(xiàng)的作用是限制每個(gè)賬號(hào)實(shí)際具有的資源限制，這里的“資源”主要包括：
max_queries_per_hour count : 單個(gè)賬號(hào)每小時(shí)執(zhí)行的查詢次數(shù)
max_upodates_per_hour count : 單個(gè)賬號(hào)每小時(shí)執(zhí)行的更新次數(shù)
max_connections_per_hour count : 單個(gè)賬號(hào)每小時(shí)連接服務(wù)器的次數(shù)
max_user_connections count : 單個(gè)賬號(hào)并發(fā)連接服務(wù)器的次數(shù)
注意：
添加用戶或者權(quán)限，使用mysql> flush privileges; 刷新權(quán)限
具體權(quán)限可以參考官網(wǎng)文檔
https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。
標(biāo)簽：黃山 蘭州 南充 馬鞍山 賀州 黔南 佛山 宿遷
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL用戶賬戶管理和權(quán)限管理深入講解》，本文關(guān)鍵詞 MySQL,用戶,賬戶,管理,和,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
