mysql權(quán)限控制

    作為一名DBA，想必大家對(duì)MySQL中的權(quán)限都不陌生，MySQL中對(duì)于權(quán)限的控制分為三個(gè)層面：

• 全局性的管理權(quán)限，作用于整個(gè)MySQL實(shí)例級(jí)別
• 數(shù)據(jù)庫(kù)級(jí)別的權(quán)限，作用于某個(gè)指定的數(shù)據(jù)庫(kù)上或者所有的數(shù)據(jù)庫(kù)上
• 數(shù)據(jù)庫(kù)對(duì)象級(jí)別的權(quán)限，作用于指定的數(shù)據(jù)庫(kù)對(duì)象上(表、視圖等)或 者所有的數(shù)據(jù)庫(kù)對(duì)象上
  

    這里，我們將mysql中的所有權(quán)限列出來(lái)，最后給出一個(gè)特殊的案例來(lái)反應(yīng)mysql權(quán)限控制中的一個(gè)小bug。首先來(lái)看權(quán)限列表，權(quán)限的順序按照首字母的順序進(jìn)行排列：

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

權(quán)限系統(tǒng)表

    權(quán)限存儲(chǔ)在mysql庫(kù)的user,db, tables_priv, columns_priv, and procs_priv這幾個(gè)系統(tǒng)表中，待MySQL實(shí)例啟動(dòng)后就加載到內(nèi)存中

• User表:

存放用戶(hù)賬戶(hù)信息以及全局級(jí)別(所有數(shù)據(jù)庫(kù))權(quán)限，決定了 來(lái)自哪些主機(jī)的哪些用戶(hù)可以訪問(wèn)數(shù)據(jù)庫(kù)實(shí)例，如果有全局權(quán)限則意味
著對(duì)所有數(shù)據(jù)庫(kù)都有此權(quán)限
• Db表:

存放數(shù)據(jù)庫(kù)級(jí)別的權(quán)限，決定了來(lái)自哪些主機(jī)的哪些用戶(hù)可以訪 問(wèn)此數(shù)據(jù)庫(kù)
• Tables_priv表:

存放表級(jí)別的權(quán)限，決定了來(lái)自哪些主機(jī)的哪些用戶(hù)可以 訪問(wèn)數(shù)據(jù)庫(kù)的這個(gè)表
• Columns_priv表:

存放列級(jí)別的權(quán)限，決定了來(lái)自哪些主機(jī)的哪些用戶(hù)可 以訪問(wèn)數(shù)據(jù)庫(kù)表的這個(gè)字段
• Procs_priv表:

存放存儲(chǔ)過(guò)程和函數(shù)級(jí)別的權(quán)限

MySQL修改權(quán)限之后的生效方法

• 執(zhí)行Grant,revoke,setpassword,renameuser命令修改權(quán)限之后，MySQL會(huì)自動(dòng)將修改后的權(quán)限信息同步加載到系統(tǒng)內(nèi)存中
• 如果執(zhí)行insert/update/delete操作上述的系統(tǒng)權(quán)限表之后，則必須再執(zhí)行刷 新權(quán)限命令才能同步到系統(tǒng)內(nèi)存中，刷新權(quán)限命令包括:flush privileges/mysqladmin flush-privileges/mysqladmin reload
• 如果是修改tables和columns級(jí)別的權(quán)限，則客戶(hù)端的下次操作新權(quán)限就會(huì)生效
• 如果是修改database級(jí)別的權(quán)限，則新權(quán)限在客戶(hù)端執(zhí)行use database命令后生效
• 如果是修改global級(jí)別的權(quán)限，則需要重新創(chuàng)建連接新權(quán)限才能生效
• --skip-grant-tables可以跳過(guò)所有系統(tǒng)權(quán)限表而允許所有用戶(hù)登錄，只在特殊情況下暫時(shí)使用

賦予權(quán)限與回收權(quán)限

    賦予權(quán)限的時(shí)候通常使用grant語(yǔ)句，以查詢(xún)?yōu)槔唧w如下:

grant select on db_name.table_name to user@host;

其中，如果對(duì)一個(gè)賬號(hào)授予數(shù)據(jù)庫(kù)中的所有表的權(quán)限，需要將上述語(yǔ)句中的table_name改為*，如果對(duì)數(shù)據(jù)庫(kù)中所有數(shù)據(jù)庫(kù)中的所有表授予權(quán)限，則需要將db_name和table_name都改為*

     回收權(quán)限通常使用revoke語(yǔ)法，它的使用方法如下，以查詢(xún)?yōu)槔?/p>

revoke select on db_name.table_name from user@host;

需要注意的是，它與grant不同之處在于語(yǔ)句最后面由to改為了from。

一個(gè)特殊例子

    今天在處理線上業(yè)務(wù)的時(shí)候，有這樣一個(gè)開(kāi)權(quán)限的需求，想從workbench客戶(hù)端查看存儲(chǔ)過(guò)程，我當(dāng)時(shí)的第一反應(yīng)就是直接給execute權(quán)限，結(jié)果給了execute權(quán)限之后，業(yè)務(wù)方反應(yīng)還是無(wú)法查看存儲(chǔ)過(guò)程，我過(guò)去看了下，連接上數(shù)據(jù)庫(kù)之后，顯示如下：

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，而沒(méi)有顯示出來(lái)procedure的內(nèi)容，當(dāng)然也就無(wú)法查看procedure了，這種情況怎么辦呢？

    想到了mysql.proc表里面包含存儲(chǔ)過(guò)程的信息，于是通過(guò)下面的方法給了mysql.proc表一個(gè)只讀的權(quán)限：

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)

    再次登錄客戶(hù)端，可以發(fā)現(xiàn)存儲(chǔ)過(guò)程的內(nèi)容已經(jīng)可以看到了，如下：

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 權(quán)限控制詳解的詳細(xì)內(nèi)容，更多關(guān)于MySQL 權(quán)限控制的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！