mysql權(quán)限控制

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

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

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

•All/All Privileges
該權(quán)限代表全局或者全數(shù)據(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)限代表允許修改或者刪除存儲過程、函數(shù)的權(quán)限
•Create
該權(quán)限代表允許創(chuàng)建新的數(shù)據(jù)庫和表的權(quán)限
•Create routine
該權(quán)限代表允許創(chuàng)建存儲過程、函數(shù)的權(quán)限
•Create tablespace
該權(quán)限代表允許創(chuàng)建、修改、刪除表空間和日志組的權(quán)限
•Create temporary tables
該權(quán)限代表允許創(chuàng)建臨時表的權(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í)行存儲過程和函數(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ù)，同時在執(zhí)行analyze table,optimize table,repair table語句的時候也需要insert權(quán)限
•Lock
該權(quán)限代表允許對擁有select權(quán)限的表進(jìn)行鎖定，以防止其他鏈接對此表的讀或?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)閉和重新開啟日志文件并刷新所有的表
•Replication client
該權(quán)限代表允許執(zhí)行show master status,show slave status,show binary logs命令
•Replication slave
該權(quán)限代表允許slave主機通過此用戶連接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ù)庫實例，執(zhí)行語句包括mysqladmin shutdown
•Super
該權(quán)限代表允許執(zhí)行一系列數(shù)據(jù)庫管理命令，包括kill強制關(guān)閉某個連接 命令，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)建一個用戶之后的默認(rèn)權(quán)限，其本身代表連接登錄權(quán)限

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

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

• User表:

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

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

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

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

存放存儲過程和函數(shù)級別的權(quán)限

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

• 執(zhí)行Grant,revoke,setpassword,renameuser命令修改權(quán)限之后，MySQL會自動將修改后的權(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級別的權(quán)限，則客戶端的下次操作新權(quán)限就會生效
• 如果是修改database級別的權(quán)限，則新權(quán)限在客戶端執(zhí)行use database命令后生效
• 如果是修改global級別的權(quán)限，則需要重新創(chuàng)建連接新權(quán)限才能生效
• --skip-grant-tables可以跳過所有系統(tǒng)權(quán)限表而允許所有用戶登錄，只在特殊情況下暫時使用

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

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

grant select on db_name.table_name to user@host;

其中，如果對一個賬號授予數(shù)據(jù)庫中的所有表的權(quán)限，需要將上述語句中的table_name改為*，如果對數(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。

一個特殊例子

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

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

    想到了mysql.proc表里面包含存儲過程的信息，于是通過下面的方法給了mysql.proc表一個只讀的權(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)存儲過程的內(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)限控制的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論