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: dba_admin@127.0.0.1
   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表一個只讀的權限：

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_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 權限控制的資料請關注腳本之家其它相關文章！