前言
MySQL 的權限表在數據庫啟動的時候就載入內存，當用戶通過身份認證后，就在內存中進行相應權限的存取，這樣，此用戶就可以在數據庫中做權限范圍內的各種操作了。
下面話不多說了，來一起看看詳細的介紹吧
mysql 的權限體系大致分為5個層級:
全局層級
全局權限適用于一個給定服務器中的所有數據庫。這些權限存儲在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤銷全局權限。
數據庫層級
數據庫權限適用于一個給定數據庫中的所有目標。這些權限存儲在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤銷數據庫權限。
表層級
表權限適用于一個給定表中的所有列。這些權限存儲在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表權限。
列層級
列權限適用于一個給定表中的單一列。這些權限存儲在mysql.columns_priv表中。當使用REVOKE時，您必須指定與被授權列相同的列。
子程序層級
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT權限適用于已存儲的子程序。這些權限可以被授予為全局層級和數據庫層級。而且，除了CREATE ROUTINE外，這些權限可以被授予為子程序層級，并存儲在mysql.procs_priv表中。
這些權限信息存儲在下面的系統表中:
mysql.user
mysql.db
mysql.host
mysql.table_priv
mysql.column_priv
當用戶連接進來，mysqld會通過上面的這些表對用戶權限進行驗證！
一、權限表的存取
在權限存取的兩個過程中，系統會用到 “mysql” 數據庫(安裝 MySQL 時被創建，數據庫名稱叫“mysql”) 中 user、host 和 db 這3個最重要的權限表。
在這 3 個表中，最重要的表示 user 表，其次是 db 表，host 表在大多數情況下并不使用。
user 中的列主要分為 4 個部分：用戶列、權限列、安全列和資源控制列。
通常用的最多的是用戶列和權限列，其中權限列又分為普通權限和管理權限。普通權限用于數據庫的操作，比如 select_priv、super_priv 等。
當用戶進行連接時，權限表的存取過程有以下兩個過程：
先從 user 表中的 host、user 和 password 這 3 個字段中判斷連接的 IP、用戶名、和密碼是否存在于表中，如果存在，則通過身份驗證，否則拒絕連接。
如果通過身份驗證、則按照以下權限表的順序得到數據庫權限：user -> db -> tables_priv -> columns_priv。
在這幾個權限表中，權限范圍依次遞減，全局權限覆蓋局部權限。上面的第一階段好理解，下面以一個例子來詳細解釋一下第二階段。
為了方便測試，需要修改變量 sql_mode，不然會報錯，如下
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 默認值中有 NO_AUTO_CREATE_USER (防止GRANT自動創建新用戶，除非還指定了密碼)
SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
1. 創建用戶
xxx@localhost，并賦予所有數據庫上的所有表的 select 權限
先查看user表顯示的權限狀態
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表的權限狀態
MySQL [mysql]> select * from db where user="xxx" and host='localhost' \G;
Empty set (0.03 sec)
可以發現user表中Select_priv: Y，其他均為N
DB表中則無記錄
也就是說，對所有數據庫都具有相同的權限的用戶并不需要記錄到 db 表，而僅僅需要將 user 表中的 select_priv 改為 “Y” 即可。換句話說，user 表中的每個權限都代表了對所有數據庫都有權限。
2. 將 xxx@localhost 上的權限改為只對 db1 數據庫上所有表的 select 權限。
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
這時候發現，user 表中的 select_priv 變為 “N” ，而 db 表中增加了 db 為 xxx 的一條記錄。也就是說，當只授予部分數據庫某些權限時，user 表中的相應權限列保持 “N”，而將具體的數據庫權限寫入 db 表。table 和 column 的權限機制和 db 類似。
3. tables_priv記錄表權限
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三個表中沒有記錄
從上例可以看出，當用戶通過權限認證，進行權限分配時，將按照 ==user -> db -> tables_priv -> columns_priv== 的順序進行權限分配，即先檢查全局權限表 user，如果 user 中對應 權限為 “Y”，則此用戶對所有數據庫的權限都為“Y”，將不再檢查 db、tables_priv 和 columns_priv；如果為“N”，則到 db 表中檢查此用戶對應的具體數據庫，并得到 db 中為 “Y”的權限；如果 db 中相應權限為 “N”，則再依次檢查tables_priv 和 columns_priv 中的權限，如果所有的都為“N”，則判斷為不具備權限。
二. 賬戶管理
授權 grant
grant不僅可以用來授權，還可以用來創建用戶。
授權的語法：
grant 權限列表 on 庫名.表名 to 用戶@主機 identified by '密碼';
創建用戶 p1 ，權限為可以在所有數據庫上執行所有權限，只能從本地進行連接
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 權限外，所有權限在user 表里面都是 “Y”。
增加對 p1 的 grant 權限
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)
設置密碼賦予grant權限
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版本后的數據庫，密碼字段改為authentication_string
創建新用戶 p2，可以從任何 IP 連接，權限為對 db1 數據庫里的所有表進行 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 表中的權限都是“N”，db 表中增加的記錄權限則都是“Y”,這樣只授予用戶適當的權限，而不會授予過多的權限。
本例中的 IP 限制為所有 IP 都可以連接，因此設置為 “%”，mysql 數據庫中是通過 user 表的 host 字段來進行控制，host 可以是以下類型的賦值。
注意: mysql 數據庫的 user 表中 host 的值為 “%” 或者空，表示所有外部 IP 都可以連接，但是不包括本地服務器 localhost，因此，如果要包括本地服務器，必須單獨為 localhost 賦予權限。
授予 super、process、file 權限給用戶 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)
這幾個權限都是屬于管理權限，因此不能夠指定某個數據庫，on 后面必須跟 *.*,否則會提示錯誤，如上
那這幾個權限是干啥的？
process通過這個權限，用戶可以執行SHOW PROCESSLIST和KILL命令。默認情況下，每個用戶都可以執行SHOW PROCESSLIST命令，但是只能查詢本用戶的進程。
擁有file權限才可以執行 select ..into outfile和load data infile…操作，但是不要把file, process, super權限授予管理員以外的賬號，這樣存在嚴重的安全隱患。
super這個權限允許用戶終止任何查詢；修改全局變量的SET語句；使用CHANGE MASTER，PURGE MASTER LOGS
另外一個比較特殊的
usage權限
連接（登陸）權限，建立一個用戶，就會自動授予其usage權限（默認授予）。
該權限只能用于數據庫登陸，不能執行任何操作；且usage權限不能被回收，也即``REVOKE用戶并不能刪除用戶。
查看賬號權限
賬號創建好后，可以通過如下命令查看權限:
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)
更改賬戶權限
創建用戶賬號p4，權限為對db1所有表具有select權限
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權限
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 權限進行合并
刪除delete權限
revoke 語句可以回收已經賦予的權限
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 權限不能被回收，也就是說，revoke 用戶并不能刪除用戶。
要徹底的刪除賬號，可以使用 drop user
賬號資源限制
創建 MySQL 賬號時，還有一類選項稱為賬號資源限制，這類選項的作用是限制每個賬號實際具有的資源限制，這里的“資源”主要包括：
max_queries_per_hour count : 單個賬號每小時執行的查詢次數
max_upodates_per_hour count : 單個賬號每小時執行的更新次數
max_connections_per_hour count : 單個賬號每小時連接服務器的次數
max_user_connections count : 單個賬號并發連接服務器的次數
注意：
添加用戶或者權限，使用mysql> flush privileges; 刷新權限
具體權限可以參考官網文檔
https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
您可能感興趣的文章:- MySQL如何利用DCL管理用戶和控制權限
- MySQL創建用戶和權限管理的方法
- mysql用戶權限管理實例分析
- MySQL用戶與權限的管理詳解
- MySQL用戶權限驗證與管理方法詳解
- MySQL用戶權限管理詳解
- MySQL中基本的用戶和權限管理方法小結
- 詳解MySQL 用戶權限管理
