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 AAADatabase changedmysql> show procedure statusG*************************** 1. row *************************** Db: CVS Name: ba_get_grow Type: PROCEDURE Definer: dba_admin@127.0.0.1 Modified: 2019-02-26 :: Created: 2019-02-26 :: Security_type: DEFINER Comment:character_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_cimysql> show create procedure ba_get_growG*************************** 1. row *************************** Procedure: ba_get_grow sql_mode: Create Procedure: NULLcharacter_set_client: utf8collation_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表一個只讀的權限：

mysql@127.0.0.1:(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_growG*************************** 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 XXXXXXXXXENDcharacter_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci row in set (0.00 sec)

以上就是MySQL 權限控制詳解的詳細內容，更多關于MySQL 權限控制的資料請關注好吧啦網其它相關文章！