前言
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 tableMySQL [(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: localhostUser: xxxSelect_priv: YInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NReload_priv: NShutdown_priv: NProcess_priv: NFile_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NShow_db_priv: NSuper_priv: NCreate_tmp_table_priv: NLock_tables_priv: NExecute_priv: NRepl_slave_priv: NRepl_client_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NCreate_user_priv: NEvent_priv: NTrigger_priv: NCreate_tablespace_priv: Nssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: password_expired: Npassword_last_changed: 2018-12-03 17:34:49password_lifetime: NULLaccount_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^CMySQL [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: localhostUser: xxxSelect_priv: NInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NReload_priv: N
MySQL [mysql]> select * from db where user='xxx'/G;*************************** 1. row ***************************Host: localhostDb: db1User: xxxSelect_priv: YInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NCreate_tmp_table_priv: NLock_tables_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NExecute_priv: NEvent_priv: NTrigger_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: localhostUser: mmmSelect_priv: NInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NReload_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: localhostDb: db1User: mmmTable_name: t1Grantor: root@localhostTimestamp: 0000-00-00 00:00:00Table_priv: SelectColumn_priv: 1 row in set (0.00 sec)ERROR: No query specifiedMySQL [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: localhostUser: p1Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_priv: YFile_priv: YGrant_priv: NReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: YCreate_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: YCreate_tablespace_priv: Yssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: password_expired: Npassword_last_changed: 2018-12-03 18:11:01password_lifetime: NULLaccount_locked: N1 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: localhostUser: p1Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_priv: YFile_priv: YGrant_priv: YReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: YCreate_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: YCreate_tablespace_priv: Yssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: password_expired: Npassword_last_changed: 2018-12-03 18:11:01password_lifetime: NULLaccount_locked: N1 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: localhostUser: p1Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_priv: YFile_priv: YGrant_priv: YReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: YCreate_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: YCreate_tablespace_priv: Yssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257password_expired: Npassword_last_changed: 2018-12-03 18:14:40password_lifetime: NULLaccount_locked: N1 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: p2Select_priv: NInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NReload_priv: NShutdown_priv: N...Create_tablespace_priv: Nssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257password_expired: Npassword_last_changed: 2018-12-03 18:20:44password_lifetime: NULLaccount_locked: N1 row in set (0.00 sec)ERROR: No query specifiedMySQL [mysql]> select * from db where user='p2'/G;*************************** 1. row ***************************Host: %Db: db1User: p2Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: NDrop_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NCreate_tmp_table_priv: NLock_tables_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NExecute_priv: NEvent_priv: NTrigger_priv: N1 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 PRIVILEGESMySQL [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é)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)VeVb武林網(wǎng)的支持。
新聞熱點(diǎn)
疑難解答
圖片精選
