MySQL 權限控制詳解

Posted on 2021-03-27 by WalkonNet

mysql權限控制

作為一名DBA，想必大傢對MySQL中的權限都不陌生，MySQL中對於權限的控制分為三個層面：

全局性的管理權限，作用於整個MySQL實例級別
數據庫級別的權限，作用於某個指定的數據庫上或者所有的數據庫上
數據庫對象級別的權限，作用於指定的數據庫對象上(表、視圖等)或者所有的數據庫對象上

這裡，我們將mysql中的所有權限列出來，最後給出一個特殊的案例來反應mysql權限控制中的一個小bug。首先來看權限列表，權限的順序按照首字母的順序進行排列：

•All/All Privileges
該權限代表全局或者全數據庫對象級別的所有權限
•Alter
該權限代表允許修改表結構的權限，但必須要求有create和insert權限配合。
如果是rename表名，則要求有alter和drop原表，create和insert新表的權限
•Alter routine
該權限代表允許修改或者刪除存儲過程、函數的權限
•Create
該權限代表允許創建新的數據庫和表的權限
•Create routine
該權限代表允許創建存儲過程、函數的權限
•Create tablespace
該權限代表允許創建、修改、刪除表空間和日志組的權限
•Create temporary tables
該權限代表允許創建臨時表的權限
•Create user
該權限代表允許創建、修改、刪除、重命名user的權限
•Create view
該權限代表允許創建視圖的權限
•Delete
該權限代表允許刪除行數據的權限
•Drop
該權限代表允許刪除數據庫、表、視圖的權限，包括truncate table命令
•Event
該權限代表允許查詢，創建，修改，刪除MySQL事件
•Execute
該權限代表允許執行存儲過程和函數的權限
•File
該權限代表允許在MySQL可以訪問的目錄進行讀寫磁盤文件操作，
可使用的命令包括load data infile,select … into outfile,load file()函數
•Grant option
該權限代表是否允許此用戶授權或者收回給其他用戶你給予的權限
•Index
該權限代表是否允許創建和刪除索引
•Insert
該權限代表是否允許在表裡插入數據，同時在執行analyze table,optimize table,repair table語句的時候也需要insert權限
•Lock
該權限代表允許對擁有select權限的表進行鎖定，以防止其他鏈接對此表的讀或寫
•Process
該權限代表允許查看MySQL中的進程信息，比如執行showprocesslist,
•Reference
該權限是在5.7.6版本之後引入，代表是否允許創建外鍵
•Reload
該權限代表允許執行flush命令，指明重新加載權限表到系統內存中，refresh命令代表關閉和重新開啟日志文件並刷新所有的表
•Replication client
該權限代表允許執行show master status,show slave status,show binary logs命令
•Replication slave
該權限代表允許slave主機通過此用戶連接master以便建立主從復制關系
•Select
該權限代表允許從表中查看數據，而且select權限在執行update/delete 語句中含有where條件的情況下也是需要的
•Show databases
該權限代表通過執行show databases命令查看所有的數據庫名
•Show view
該權限代表通過執行show create view命令查看視圖創建的語句mysqladmin processlist, show engine等命令
•Shutdown
該權限代表允許關閉數據庫實例，執行語句包括mysqladmin shutdown
•Super
該權限代表允許執行一系列數據庫管理命令，包括kill強制關閉某個連接命令，change master to創建復制關系命令，以及create/alter/drop server等命令
•Trigger
該權限代表允許創建，刪除，執行，顯示觸發器的權限
•Update
該權限代表允許修改表中的數據的權限
•Usage
該權限是創建一個用戶之後的默認權限，其本身代表連接登錄權限

權限系統表

權限存儲在mysql庫的user,db, tables_priv, columns_priv, and procs_priv這幾個系統表中，待MySQL實例啟動後就加載到內存中

• User表:

存放用戶賬戶信息以及全局級別(所有數據庫)權限，決定瞭來自哪些主機的哪些用戶可以訪問數據庫實例，如果有全局權限則意味
著對所有數據庫都有此權限
• Db表:

存放數據庫級別的權限，決定瞭來自哪些主機的哪些用戶可以訪問此數據庫
• Tables_priv表:

存放表級別的權限，決定瞭來自哪些主機的哪些用戶可以訪問數據庫的這個表
• Columns_priv表:

存放列級別的權限，決定瞭來自哪些主機的哪些用戶可以訪問數據庫表的這個字段
• Procs_priv表:

存放存儲過程和函數級別的權限

MySQL修改權限之後的生效方法

• 執行Grant,revoke,setpassword,renameuser命令修改權限之後，MySQL會自動將修改後的權限信息同步加載到系統內存中
• 如果執行insert/update/delete操作上述的系統權限表之後，則必須再執行刷新權限命令才能同步到系統內存中，刷新權限命令包括:flush privileges/mysqladmin flush-privileges/mysqladmin reload
• 如果是修改tables和columns級別的權限，則客戶端的下次操作新權限就會生效
• 如果是修改database級別的權限，則新權限在客戶端執行use database命令後生效
• 如果是修改global級別的權限，則需要重新創建連接新權限才能生效
• –skip-grant-tables可以跳過所有系統權限表而允許所有用戶登錄，隻在特殊情況下暫時使用

賦予權限與回收權限

賦予權限的時候通常使用grant語句，以查詢為例，具體如下:

grant select on db_name.table_name to user@host;

其中，如果對一個賬號授予數據庫中的所有表的權限，需要將上述語句中的table_name改為*，如果對數據庫中所有數據庫中的所有表授予權限，則需要將db_name和table_name都改為*

回收權限通常使用revoke語法，它的使用方法如下，以查詢為例：

revoke select on db_name.table_name from user@host;

需要註意的是，它與grant不同之處在於語句最後面由to改為瞭from。

一個特殊例子

今天在處理線上業務的時候，有這樣一個開權限的需求，想從workbench客戶端查看存儲過程，我當時的第一反應就是直接給execute權限，結果給瞭execute權限之後，業務方反應還是無法查看存儲過程，我過去看瞭下，連接上數據庫之後，顯示如下：

mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| AAA    |
+--------------------+
2 rows in set (0.00 sec)
mysql> use AAA
Database changed
mysql> show procedure status\G
*************************** 1. row ***************************
     Db: CVS
    Name: ba_get_grow
    Type: PROCEDURE
    Definer: [email protected]
   Modified: 2019-02-26 ::
    Created: 2019-02-26 ::
  Security_type: DEFINER
    Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: utf8_general_ci

mysql> show create procedure ba_get_grow\G
*************************** 1. row ***************************
   Procedure: ba_get_grow
   sql_mode:
 Create Procedure: NULL
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: utf8_general_ci
 row in set (0.00 sec)

可以看到，第29行的create procedure的值是NULL，而沒有顯示出來procedure的內容，當然也就無法查看procedure瞭，這種情況怎麼辦呢？

想到瞭mysql.proc表裡面包含存儲過程的信息，於是通過下面的方法給瞭mysql.proc表一個隻讀的權限：

[email protected]:(none) 22:35:07>>grant select on `mysql`.proc to 'user'@'host';
Query OK, 0 rows affected (0.00 sec)

再次登錄客戶端，可以發現存儲過程的內容已經可以看到瞭，如下：

mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| AAA    |
+--------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| AAA    |
| mysql    |
+--------------------+
3 rows in set (0.00 sec)

mysql> show create procedure ba_get_grow\G
*************************** 1. row ***************************
   Procedure: ba_get_grow
   sql_mode:
 Create Procedure: CREATE DEFINER=`dba_admin`@`127.0.0.1` PROCEDURE `ba_get_grow`(
 in p_table_info varchar(),
 out out_retmsg varchar()
)
BEGIN
 XXXXXXXXX
 XXXXXXXXX
 XXXXXXXXX
 XXXXXXXXX
END
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: utf8_general_ci
 row in set (0.00 sec)

以上就是MySQL 權限控制詳解的詳細內容，更多關於MySQL 權限控制的資料請關註WalkonNet其它相關文章！

MySQL 權限控制詳解