【必知必會的MySQL知識】④DCL語言

(DCL語言) 一、概述數據控制語言，用來定義訪問許可權和安全級別。主要包含包括grant，revoke 關鍵字 grant 授予許可權 revoke 回收許可權二、授權 2.1 語法格式 GRANT priv_type [(column_list)] [, priv_type [(column_l ...

一、概述
二、授權
三、回收許可權
四、實踐操作

一、概述

數據控制語言，用來定義訪問許可權和安全級別。主要包含包括grant，revoke 關鍵字

grant 授予許可權

revoke 回收許可權

二、授權

2.1 語法格式

GRANT priv_type [(column_list)]  [, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user[IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH with_option [with_option]...]

2.2 語法說明

參數	參數說明
`priv_type`	參數表示許可權類型
`columns_list`	參數表示許可權作用於哪些列上，省略該參數時，表示作用於整個表
`object_type`	參數用於指定許可權對象
`priv_level`	參數用於指定許可權級別
`user`	參數表示用戶賬戶；由用戶名和主機名構成，格式是“'username'@'hostname'”
`IDENTIFIED BY`	參數用來為用戶設置密碼
`password`	參數是用戶的新密碼
`WITH`	一個或多個with_option 參數

with_option 參數一共5個選項

with_option參數	參數說明
`GRANT OPTION`	被授權的用戶可以將這些許可權賦予給別的用戶
`MAX_QUERIES_PER_HOUR count`	設置每個小時可以允許執行 count 次查詢
`MAX_UPDATES_PER_HOUR count`	設置每個小時可以允許執行 count 次更新
`MAX_CONNECTIONS_PER_HOUR count`	設置每小時可以建立 count 個連接
`MAX_USER_CONNECTIONS count`	設置單個用戶可以同時具有的 count 個連接

2.3 許可權類型

許可權類型分類

許可權類型	說明
`資料庫許可權`	和一個具體的資料庫中的所有表相關。例如，可以在已有的資料庫 mytest 中創建新表的許可權
`表許可權`	和一個具體表中的所有數據相關。例如，可以使用 SELECT 語句查詢表 students 的所有數據的許可權
`列許可權`	和表中的一個具體列相關。例如，可以使用 UPDATE 語句更新表 students 中 name 列的值的許可權
`用戶許可權`	和 MySQL 中所有的資料庫相關。例如，可以刪除已有的資料庫或者創建一個新的資料庫的許可權

① 授予數據庫許可權時，<許可權類型>可以指定為以下值

許可權名稱	對應user表中的欄位	說明
`SELECT`	Select_priv	表示授予用戶可以使用 SELECT 語句訪問特定資料庫中所有表和視圖的許可權。
`INSERT`	Insert_priv	表示授予用戶可以使用 INSERT 語句向特定資料庫中所有表添加數據行的許可權。
`DELETE`	Delete_priv	表示授予用戶可以使用 DELETE 語句刪除特定資料庫中所有表的數據行的許可權。
`UPDATE`	Update_priv	表示授予用戶可以使用 UPDATE 語句更新特定資料庫中所有數據表的值的許可權。
`REFERENCES`	References_priv	表示授予用戶可以創建指向特定的資料庫中的表外鍵的許可權。
`CREATE`	Create_priv	表示授權用戶可以使用 CREATE TABLE 語句在特定資料庫中創建新表的許可權。
`ALTER`	Alter_priv	表示授予用戶可以使用 ALTER TABLE 語句修改特定資料庫中所有數據表的許可權。
`SHOW VIEW`	Show_view_priv	表示授予用戶可以查看特定資料庫中已有視圖的視圖定義的許可權。
`CREATE ROUTINE`	Create_routine_priv	表示授予用戶可以為特定的資料庫創建存儲過程和存儲函數的許可權。
`ALTER ROUTINE`	Alter_routine_priv	表示授予用戶可以更新和刪除資料庫中已有的存儲過程和存儲函數的許可權。
`INDEX`	Index_priv	表示授予用戶可以在特定資料庫中的所有數據表上定義和刪除索引的許可權。
`DROP`	Drop_priv	表示授予用戶可以刪除特定資料庫中所有表和視圖的許可權。
`CREATE TEMPORARY TABLES`	Create_tmp_table_priv	表示授予用戶可以在特定資料庫中創建臨時表的許可權。
`CREATE VIEW`	Create_view_priv	表示授予用戶可以在特定資料庫中創建新的視圖的許可權。
`EXECUTE ROUTINE`	Execute_priv	表示授予用戶可以調用特定資料庫的存儲過程和存儲函數的許可權。
`LOCK TABLES`	Lock_tables_priv	表示授予用戶可以鎖定特定資料庫的已有數據表的許可權。
`ALL 或 ALL PRIVILEGES 或 SUPER`	Super_priv	表示以上所有許可權/超級許可權

② 授予表許可權時，<許可權類型>可以指定為以下值：

許可權名稱	對應user表中的欄位	說明
`SELECT`	Select_priv	授予用戶可以使用 SELECT 語句進行訪問特定表的許可權
`INSERT`	Insert_priv	授予用戶可以使用 INSERT 語句向一個特定表中添加數據行的許可權
`DELETE`	Delete_priv	授予用戶可以使用 DELETE 語句從一個特定表中刪除數據行的許可權
`DROP`	Drop_priv	授予用戶可以刪除數據表的許可權
`UPDATE`	Update_priv	授予用戶可以使用 UPDATE 語句更新特定數據表的許可權
`ALTER`	Alter_priv	授予用戶可以使用 ALTER TABLE 語句修改數據表的許可權
`REFERENCES`	References_priv	授予用戶可以創建一個外鍵來參照特定數據表的許可權
`CREATE`	Create_priv	授予用戶可以使用特定的名字創建一個數據表的許可權
`INDEX`	Index_priv	授予用戶可以在表上定義索引的許可權
`ALL 或 ALL PRIVILEGES 或 SUPER`	Super_priv	所有的許可權名

③ 授予列許可權時，<許可權類型>可以指定以下值

許可權名稱	對應user表中的欄位	說明
`SELECT`	Select_priv	授予用戶可以使用 SELECT 語句進行訪問特定表的許可權
`INSERT`	Insert_priv	授予用戶可以使用 INSERT 語句向一個特定表中添加數據行的許可權
`UPDATE`	Update_priv	授予用戶可以使用 UPDATE 語句更新特定數據表的許可權

④授予用戶許可權時，<許可權類型> 除了可以指定為授予資料庫許可權，還可以指定以下值

許可權名稱	說明
`CREATE USER`	表示授予用戶可以創建和刪除新用戶的許可權
`SHOW DATABASES`	表示授予用戶可以使用 SHOW DATABASES 語句查看所有已有的資料庫的定義的許可權

2.4 許可權級別

許可權級別	說明
`*`	表示當前資料庫中的所有表
`.`	表示所有資料庫中的所有表
`db_name.*`	表示某個資料庫中的所有表，db_name 指定資料庫名
`db_name.tbl_name`	表示某個資料庫中的某個表或視圖，db_name 指定資料庫名，tbl_name 指定表名或視圖名
`db_name.routine_name`	表示某個資料庫中的某個存儲過程或函數，routine_name 指定存儲過程名或函數名
`TO 子句`	如果許可權被授予給一個不存在的用戶，MySQL 會自動執行一條 CREATE USER 語句來創建這個用戶，但同時必須為該用戶設置密碼

三、回收許可權

3.1 語法格式

REVOKE priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user]...

3.2 語法說明

參數	參數說明
`priv_type`	參數表示許可權的類型
`column_list`	參數表示許可權作用於哪些列上，沒有該參數時作用於整個表上
`user`	參數由用戶名和主機名構成，格式為“username'@'hostname'”

3.3 註意事項

REVOKE 語法和 GRANT 語句的語法格式相似，但具有相反的效果。
要使用 REVOKE 語句，必須擁有 MySQL 資料庫的全局 CREATE USER 許可權或 UPDATE 許可權

四、實踐操作

① 運維童鞋想要查詢所有用戶的許可權

select * FROM mysql.user;

在這裡插入圖片描述
②運維童鞋想要創建一個新的用戶testXiezhr,密碼為testXiezhr。並授予test用戶所有的數據查詢、插入許可權

# 創建用戶並授權
mysql> GRANT SELECT,INSERT ON *.*
    ->      TO 'testXiezhr'@'localhost'
    ->      IDENTIFIED BY 'testXiezhr'
    ->       WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)
# 使用 SHOW GRANTS 語句查詢用戶 testXiezhr的許可權
mysql> show grants for 'testXiezhr'@localhost;
+---------------------------------------------------------------------------+
| Grants for testXiezhr@localhost                                           |
+---------------------------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'testXiezhr'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

③ 運維童鞋想要收回testXiezhr用戶的插入許可權

# 使用revoke關鍵字收回insert 許可權
mysql> REVOKE INSERT ON *.* FROM 'testXiezhr'@'localhost';
Query OK, 0 rows affected (0.00 sec)
#使用 SHOW GRANTS 語句查詢用戶 testXiezhr的許可權，我們發現insert 許可權沒有了
mysql> show grants for 'testXiezhr'@localhost;
+-------------------------------------------------------------------+
| Grants for testXiezhr@localhost                                   |
+-------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'testXiezhr'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)