mysql權(quán)限控制

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

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

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

•All/All Privileges該權(quán)限代表全局或者全數(shù)據(jù)庫對(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ǔ)過程、函數(shù)的權(quán)限•Create該權(quán)限代表允許創(chuàng)建新的數(shù)據(jù)庫和表的權(quán)限•Create routine該權(quán)限代表允許創(chuàng)建存儲(chǔ)過程、函數(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ù)庫、表、視圖的權(quán)限，包括truncate table命令•Event該權(quán)限代表允許查詢，創(chuàng)建，修改，刪除MySQL事件•Execute該權(quán)限代表允許執(zhí)行存儲(chǔ)過程和函數(shù)的權(quán)限•File該權(quán)限代表允許在MySQL可以訪問的目錄進(jìn)行讀寫磁盤文件操作，可使用的命令包括load data infile,select ... into outfile,load file()函數(shù)•Grant option該權(quán)限代表是否允許此用戶授權(quán)或者收回給其他用戶你給予的權(quán) 限•Index該權(quán)限代表是否允許創(chuàng)建和刪除索引•Insert該權(quán)限代表是否允許在表里插入數(shù)據(jù)，同時(shí)在執(zhí)行analyze table,optimize table,repair table語句的時(shí)候也需要insert權(quán)限•Lock該權(quán)限代表允許對(duì)擁有select權(quán)限的表進(jìn)行鎖定，以防止其他鏈接對(duì)此表的讀或?qū)?#8226;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)閉和重新開啟日志文件并刷新所有的表•Replication client該權(quán)限代表允許執(zhí)行show master status,show slave status,show binary logs命令•Replication slave該權(quán)限代表允許slave主機(jī)通過此用戶連接master以便建立主從復(fù)制關(guān)系•Select該權(quán)限代表允許從表中查看數(shù)據(jù)，而且select權(quán)限在執(zhí)行update/delete 語句中含有where條件的情況下也是需要的•Show databases該權(quán)限代表通過執(zhí)行show databases命令查看所有的數(shù)據(jù)庫名•Show view該權(quán)限代表通過執(zhí)行show create view命令查看視圖創(chuàng)建的語句mysqladmin processlist, show engine等命令•Shutdown該權(quán)限代表允許關(guān)閉數(shù)據(jù)庫實(shí)例，執(zhí)行語句包括mysqladmin shutdown•Super該權(quán)限代表允許執(zhí)行一系列數(shù)據(jù)庫管理命令，包括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è)用戶之后的默認(rèn)權(quán)限，其本身代表連接登錄權(quán)限

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

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

• User表:

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

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

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

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

存放存儲(chǔ)過程和函數(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)限，則客戶端的下次操作新權(quán)限就會(huì)生效• 如果是修改database級(jí)別的權(quán)限，則新權(quán)限在客戶端執(zhí)行use database命令后生效• 如果是修改global級(jí)別的權(quán)限，則需要重新創(chuàng)建連接新權(quán)限才能生效• --skip-grant-tables可以跳過所有系統(tǒng)權(quán)限表而允許所有用戶登錄，只在特殊情況下暫時(shí)使用

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

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

grant select on db_name.table_name to user@host;

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

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

revoke select on db_name.table_name from user@host;

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

一個(gè)特殊例子

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

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的內(nèi)容，當(dāng)然也就無法查看procedure了，這種情況怎么辦呢？

想到了mysql.proc表里面包含存儲(chǔ)過程的信息，于是通過下面的方法給了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)

再次登錄客戶端，可以發(fā)現(xiàn)存儲(chǔ)過程的內(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_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 權(quán)限控制詳解的詳細(xì)內(nèi)容，更多關(guān)于MySQL 權(quán)限控制的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！