MySQL-03.使用者管理和許可權管理

长名06發表於2024-03-17
MySql

1.使用者管理

MySQL使用者可以分為普通使用者和root使用者。root使用者是超級管理員，擁有所有許可權，包括建立使用者、刪除使用者和修改使用者的密碼等管理許可權。普通使用者只擁有被授予的各種許可權。

MySQL提供了很多語句用來管理使用者賬號，這些語句可以用來管理包括登入和退出MySQL伺服器，建立使用者，刪除使用者、密碼管理和許可權管理等內容。

MySQL資料庫的安全性需要透過賬戶管理來保證。

1.1 登入MySQL伺服器

啟動MySQL服務後，可以透過mysql命令來登入MySQL伺服器，命令如下：

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL語句"

下面詳細介紹命令中的引數：

  • -h引數後面接主機名或者主機IP，hostname為主機名，hostIP為主機IP。
  • -P引數後面接MySQL服務的埠，透過該引數連線到指定的埠。MySQL服務的預設埠是3306，不使用該引數時自動連線到3306埠，port為連線的埠號。
  • -u引數後面接使用者名稱，username為使用者名稱。
  • -p引數會提示輸入密碼。
  • DatabaseName引數指明登入到哪一個資料庫中。如果沒有該引數，就會直接登入到MySQL資料庫中，然後可以使用USE命令來選擇資料庫。
  • -e引數後面可以直接加SQL語句。登入MySQL伺服器以後即可執行這個SQL語句，然後退出MySQL伺服器。

舉例

mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"

1.2 建立使用者

CREATE USER語句的基本語法如下

CREATE USER 使用者名稱 [IDENTIFIED BY '密碼'][,使用者名稱 [IDENTIFIED BY '密碼']];
  • 使用者名稱參數列示新建使用者的賬戶，由使用者（User）和主機名（Host）構成；
  • “[ ]”表示可選，也就是說，可以指定使用者登入時需要密碼驗證，也可以不指定密碼驗證，這樣使用者可以直接登入。不過，不指定密碼的方式不安全，不推薦使用。如果指定密碼值，這裡需要使用IDENTIFIED BY指定明文密碼值。
  • CREATE USER語句可以同時建立多個使用者。

舉例：

CREATE USER zhang3 IDENTIFIED BY 'abc12345';# 預設host是%


CREATE USER 'li4'@'localhost' IDENTIFIED BY '123456';

1.3 修改使用者

privileges n.許可權

修改使用者名稱:

UPDATE mysql.user set user = 'wang5' where user='zhang3';

flush privileges;

1.4 刪除使用者

方式1：使用DROP方式刪除(推薦)

使用DROP USER語句來刪除使用者時，操作使用者，必須擁有DROP USER許可權。DROP USER 語句的基本語法如下

DROP USER user[,user]..;#user值 'user'@'host' user是mysql.user欄位值，host是mysql.user的host欄位值

舉例

DROP USER wang5;#預設刪除host為%的使用者，這是因為在mysql.user表的定義，host和user列構成了複合主鍵
#等價於DROP USER 'wang5'@'%' 

DROP USER 'li4'@'localhost';

方式2：使用delete方式刪除

DELETE FROM mysql.user WHERE host='hostname' AND user='username';

#執行完DELETE命令後要使用FLUSH命令來使使用者生效
flush privileges;

舉例

DELETE FROM mysql.user WHERE host='localhost' AND user='tom';

flush privileges;

注意：不推薦透過 DELETE FROM USER u WHERE USER='li4' 進行刪除，系統會有殘留資訊保留。而drop user命令會刪除使用者以及對應的許可權，執行命令後你會發現mysql.user表和mysql.db表的相應記錄都消失了。

1.5 設定當前使用者密碼

舊的寫法如下

# 修改當前使用者的密碼：（MySQL5.7測試有效）
SET PASSWORD = PASSWORD('123456');

這裡介紹推薦的寫法：

1.使用ALTER USER命令來修改當前使用者密碼 使用者可以使用ALTER命令來修改自身密碼，如下語句代表修改當前登入使用者的密碼。基本語法如下：

ALTER USER USER() IDENTIFIED BY 'new_password';

2.使用set語句來修改當前使用者密碼 使用root使用者登入MySQL後，可以使用SET語句來修改密碼，具體SQL語句如下：

SET PASSWORD='new_password';

該語句會自動將密碼加密後再賦給使用者。

1.6 修改其他使用者密碼

1.使用ALTER語句來修改普通使用者的密碼 可以使用ALTER USER語句來修改普通使用者的密碼。基本語法形式如下：

ALTER USER user [IDENTIFIED BY '新密碼'] [,user[IDENTIFIED BY '新密碼']]…;#user值 'user'@'host'

2.使用SET命令來修改普通使用者的密碼 使用root使用者登入到MySQL伺服器後，可以使用SET語句來修改普通使用者的密碼。SET語句的程式碼如下：

SET PASSWORD FOR 'username'@'hostname'='new_password';

3.使用UPDATE語句修改普通使用者的密碼（不推薦）

UPDATE MySQL.user SET authentication_string=PASSWORD("123456")
WHERE User = "username" AND Host = "hostname";

1.7 MySQL8密碼管理(瞭解)

1.7.1 密碼過期策略
  • 在MySQL中，資料庫管理員可以手動設定賬號密碼過期，也可以建立一個自動密碼過期策略。
  • 過期策略可以是全域性的，也可以為每個賬號設定單獨的過期策略。
ALTER USER user password EXPIRE;#expire vi.失效，終止

舉例

ALTER USER 'wang5'@'%' password EXPIRE;#手動設定user為wang5且host地址為%的使用者密碼過期
  • 方式1：使用SQL語句更改該變數的值並持久化
SET PERSIST default_password_lifetime = 180; # 建立全域性策略，設定密碼每隔180天過期
  • 方式2：配置檔案my.cnf中進行維護
[mysqld]
default_password_lifetime=180 #建立全域性策略，設定密碼每隔180天過期

手動設定指定時間過期方式2：單獨設定

每個賬號既可延用全域性密碼過期策略，也可單獨設定策略。在CREATE USER和ALTER USER語句上加入PASSWORD EXPIRE選項可實現單獨設定策略。下面是一些語句示例。

#設定kangshifu賬號密碼每90天過期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

#設定密碼永不過期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;

#延用全域性密碼過期策略：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
1.7.2 密碼重用策略

手動設定密碼重用方式1：全域性

  • 方式1，使用SQL
SET PERSIST password_history = 6; #設定不能選擇最近使用過的6個密碼

SET PERSIST password_reuse_interval = 365; #設定不能選擇最近一年內的密碼
  • 方式2：my.cnf配置檔案
[mysqld]
password_history=6
password_reuse_interval=365

手動設定密碼重用方式2：單獨設定

#不能使用最近5個密碼：
CREATE USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
ALTER USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;

#不能使用最近365天內的密碼：
CREATE USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;

#既不能使用最近5個密碼，也不能使用365天內的密碼
CREATE USER 'kangshifu'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;

ALTER USER 'kangshifu'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;

2.使用者(賬號)許可權管理

關於MySQL的許可權簡單的理解就是MYSQL允許你做你權力以內的事情，不可以越界,比如只允許你執行 SELECT操作，那麼你就不能執行 UPDATE操作，只允許你從某臺機器上連線 MYSQL，那麼你就不能從除那臺機器以外的其他機器連線MySQL。

2.1 許可權列表

MySQL到底都有那些許可權呢?

mysql> show privileges;#有62條許可權

常用的許可權

  • 1.CREATE和DROP許可權 可以建立新的資料庫和表，或刪除（移掉）已有的資料庫和表。如果將MySQL資料庫中的DROP許可權授予某使用者，使用者就可以刪除MySQL訪問許可權儲存的資料庫。
  • 2.SELECT、INSERT、UPDATE和DELETE許可權 允許在一個資料庫現有的表上實施操作。
  • 3.SELECT許可權 只有在它們真正從一個表中檢索行時才被用到。
  • 4.INDEX許可權 允許建立或刪除索引，INDEX適用於已有的表。如果具有某個表的CREATE許可權，就可以在CREATE TABLE語句中包括索引定義。
  • 5.ALTER許可權 可以使用ALTER TABLE來更改表的結構和重新命名錶。
  • 6.CREATE ROUTINE許可權 用來建立儲存的程式（函式和程式），ALTER ROUTINE許可權用來更改和刪除儲存的程式， EXECUTE許可權 用來執行儲存的程式。
  • 7.GRANT許可權 允許授權給其他使用者，可用於資料庫、表和儲存的程式。
  • 8.FILE許可權 使使用者可以使用LOAD DATA INFILE和SELECT ... INTO OUTFILE語句讀或寫伺服器上的檔案，任何被授予FILE許可權的使用者都能讀或寫MySQL伺服器上的任何檔案（說明使用者可以讀任何資料庫目錄下的檔案，因為伺服器可以訪問這些檔案）。

MySQL的許可權分佈

許可權分佈 可以設定的許可權
表許可權 Select , Insert , Update , Delete，Create , Drop , Grant , References，Index，Alter
列許可權 Select , Insert , Update , References
過程許可權 Execute , Alter Routine , Grant

2.2 授予許可權的原則

許可權控制主要是出於安全因素，因此需要遵循以下幾個經驗原則：

1、只授予能滿足需要的最小許可權，防止使用者幹壞事。比如使用者只是需要查詢，那就只給select許可權就可以了，不要給使用者賦予update、insert或者delete許可權。

2、建立使用者的時候限制使用者的登入主機，一般是限制成指定IP或者內網IP段。

3、為每個使用者設定滿足密碼複雜度的密碼。

4、定期清理不需要的使用者，回收許可權或者刪除使用者。

2.3 授予許可權

給使用者授權的方式有 2 種，分別是透過把角色賦予使用者給使用者授權和直接給使用者授權。使用者是資料庫的使用者，我們可以透過給使用者授

予訪問資料庫中資源的許可權，來控制使用者對資料庫的訪問，消除安全隱患。

授權命令格式

#使用者名稱@使用者地址需要 'user'@'host'格式 user是msyql.user表中的user欄位值 host是host字元值
GRANT 許可權1,許可權2,... ON 資料庫名稱.表名 TO 使用者名稱@使用者地址 [IDENTIFIED BY '密碼口令']
  • 如果發現賦予許可權的使用者，還未建立，會新建該使用者。(注意在MySQL 8.0.11版本之後，不允許使用GRANT命令建立使用者了，也就是說在8.0.11之後的版本，必須先建立使用者，再賦予許可權)
  • 給li4使用者授予dbtest1庫下的所有表的增刪改查許可權
grant select,insert,update,delete on datest1.* to 'li4'@'%';
  • 授予透過網路方式登入的joe使用者 ，對所有庫所有表的全部許可權，注意這裡唯獨不包括grant的許可權
grant all privileges on *.* to 'joe'@'%' IDENTIFIED BY '密碼';

  • ALL PRIVILEGES是表示所有許可權。你也可以使用SELECT、UPDATE等許可權。

  • ON用來指定許可權針對哪些庫和表。

  • .中前面的號用來指定資料庫名，後面的號用來指定表名。這裡的×表示所有的。

  • TO表示將許可權賦予某個使用者。

  • li4@'localhost'表示li⒁4使用者，@後面接限制的主機，可以是IP、IP段、域名以及%，%表示任何地方。

注意:這裡%有的版本不包括本地，以前碰到過給某個使用者設定了%允許任何地方登入，但是在本地登入不了，這個和版本有關係。遇到這個問題再加一個localhost的使用者就可以了。

  • IDENTIFIED BY指定使用者的登入密碼。

如果需要賦予包括GRANT的許可權，新增引數"WITH GRANT OPTION"這個選項即可，表示該使用者可以將自己擁有的許可權授權給別人。經常有人在建立操作使用者的時候不指定WITH GRANT OPTION選項導致後來該使用者不能使用GRANT命令建立使用者或者給其它使用者授權。

可以使用GRANT重複給使用者新增許可權，許可權疊加，比如你先給使用者新增一個SELECT許可權，然後又給使用者新增一個INSERT許可權。那麼該使用者就同時擁有了SELECT和INSERT許可權。

我們在開發應用的時候，經常會遇到一種需求，就是要根據使用者的不同，對資料進行橫向和縱向的分組。

  • 所謂橫向的分組，就是指使用者可以接觸到的資料的範圍，比如可以看到哪些表的資料；
  • 所謂縱向的分組，就是指使用者對接觸到的資料能訪問到什麼程度，比如能看、能改，甚至是刪除。

2.4 檢視許可權

  • 檢視當前使用者許可權
show grants;

#或
show grants for current_user;

#或
show grants for current_user();
  • 檢視某個使用者的全域性許可權
show grants for 'user'@'host';

2.5 收回許可權

收回許可權就是取消已經賦予使用者的某些許可權。收回使用者不必要的許可權可以在一定程度上保證系統的安全性。MySQL中使用REVOKE語句

取消使用者的某些許可權。使用REVOKE收回許可權之後，使用者賬戶的記錄將從db、host、tables_priv和columns_priv表中刪除，但是使用者賬戶

記錄仍然在user表中儲存（刪除user表中的賬戶記錄使用DROP USER語句）。

注意：在將使用者賬戶從user表刪除之前，應該收回相應使用者的所有許可權。

  • 收回許可權命令
#使用者名稱@使用者地址需要 'user'@'host'格式 user是msyql.user表中的user欄位值 host是host字元值
REVOKE 許可權1,許可權2,.... ON 資料庫名.表名 FROM 使用者名稱@使用者地址;
  • 舉例
#收回全庫全表的所有許可權
REVOKE ALL PRIVILEGES ON *.* FROM 'joe'@'%';

#收回mysql庫下的所有表的增刪改查許可權
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM 'joe'@'localhost';
  • 注意: 有些許可權可能需要被操作的使用者重新登入後才生效。

總結
有一些程式設計師喜歡使用Root超級使用者來訪問資料庫，完全把許可權控制放在應用層面實現。這樣當然也是可以的。但建議大家，儘量使用資料庫自己的角色和使用者機制來控制訪問許可權，不要輕易用Root賬號。因為Root賬號密碼放在配置檔案裡面不安全，一旦洩露，資料庫就會完全失去保護。
而且，MySQL的許可權控制功能十分完善，應該儘量利用，可以提高效率，而且安全可靠。

3.許可權表

MySQL伺服器透過許可權表來控制使用者對資料庫的訪問，許可權表存放在mysql資料庫中。MySQL資料庫系統會根據這些許可權表的內容為每個使用者賦予相應的許可權。這些許可權表中最重要的是user表、db表。除此之外，還有table_priv表、column_priv表和proc_priv表等。在MySQL啟動時，伺服器將這些資料庫表中許可權資訊的內容讀入記憶體。

3.1 user表

user表是MySQL中最重要的一個許可權表，記錄使用者賬號和許可權資訊，有51個欄位(Linux mysql 8.0.25版本)，有45個欄位(Windows mysql 5.7.19)，不同小版本可能不同，沒測試過。

DESC mysql.user\G#在命令列下檢視user表的表結構
3.1.1 範圍列(使用者列)

  • host列: 表示連線型別

    • %表示所有遠端透過 TCP方式的連線

    • IP 地址如 (192.168.1.2、127.0.0.1) 透過制定ip地址進行的TCP方式的連線

    • 機器名透過制定網路中的機器名進行的TCP方式的連線

    • ::1IPv6的本地ip地址，等同於IPv4的 127.0.0.1

    • localhost本地方式透過命令列方式的連線 ，比如mysql -u xxx -p xxx 方式的連線。

  • user列：表示使用者名稱，同一使用者透過不同方式連結的許可權是不一樣的。

  • password ： 密碼

  • 所有密碼串透過 password(明文字串) 生成的密文字串。MySQL 8.0 在使用者管理方面增加了角色管理，預設的密碼加密方式也做了調整，由之前的SHA1改為了SHA2，不可逆 。同時加上 MySQL 5.7 的禁用使用者和使用者過期的功能，MySQL 在使用者管理方面的功能和安全性都較之前版本大大的增強了。

  • mysql 5.7 及之後版本的密碼儲存到 authentication_string 欄位中不再使用password 欄位。

3.1.2 許可權列
  • Grant_priv欄位
    • 表示是否擁有GRANT許可權
  • Shutdown_priv欄位
    • 表示是否擁有停止MySQL服務的許可權
  • Super_priv欄位
    • 表示是否擁有超級許可權
  • Execute_priv欄位
    • 表示是否擁有EXECUTE許可權。擁有EXECUTE許可權，可以執行儲存過程和函式。
  • Select_priv , Insert_priv等
    • 表示是否擁有SELECT，INSERT許可權。
3.1.3 安全列

安全列只有6個欄位，其中兩個是ssl相關的（ssl_type、ssl_cipher），用於加密 ；兩個是x509相關的（x509_issuer、x509_subject），用於標識使用者；另外Plugin欄位用於驗證使用者身份的外掛，該欄位不能為空。如果該欄位為空，伺服器就使用內建授權驗證機制驗證使用者身份。

3.1.4 資源控制列

資源控制列的欄位用來限制使用者使用的資源，包含4個欄位，分別為：

  • max_questions，使用者每小時允許執行的查詢操作次數；
  • max_updates，使用者每小時允許執行的更新操作次數；
  • max_connections，使用者每小時允許執行的連線操作次數；
  • max_user_connections，使用者允許同時建立的連線次數。

小結，user表和建立的表一樣，有許可權的前提下，可以進行增刪改查操作。

SELECT * FROM mysql.user \G;#檢視使用者, 以列的方式顯示資料

#檢視一部分欄位
SELECT 
	host,user,authentication_string,select_priv,insert_priv,drop_priv
FROM 
	mysql.user;

3.2 db表

使用DESC檢視db表的基本結構：

DESC mysql.db;

1. 使用者列

db表使用者列有3個欄位，分別是Host、User、Db。這3個欄位分別表示主機名、使用者名稱和資料庫名。表示從某個主機連線某個使用者對某個資料庫的操作許可權，這3個欄位的組合構成了db表的主鍵。

2. 許可權列

Create_routine_priv和Alter_routine_priv這兩個欄位決定使用者是否具有建立和修改儲存過程的許可權。

3.3 tables_priv表和columns_priv表

tables_priv表用來對表設定操作許可權，columns_priv表用來對錶的某一列設定許可權。

desc tables_priv;

desc columns_priv;
3.3.1 tables_priv表

tables_priv表有8個欄位，分別是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和Column_priv，各個欄位說明如下：

  • Host 、 Db 、 User 和 Table_name 四個欄位分別表示主機名、資料庫名、使用者名稱和表名。這四個欄位組合成了tables_priv的主鍵。
  • Grantor表示修改該記錄的使用者。
  • Timestamp表示修改該記錄的時間。
  • Table_priv 表示物件的操作許可權。包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter。
  • Column_priv欄位表示對錶中的列的操作許可權，包括Select、Insert、Update和References。
3.3.2 columns_priv表

columns_priv表有7個欄位，分別是Host、Db、User、Table_name、Column_name、Timestamp、Column_priv，各個欄位說明如下：

  • Host 、 Db 、 User 、Table_name和 Column_name 四個欄位分別表示主機名、資料庫名、使用者名稱、表名和列名。這五個欄位組合成了columns_priv的主鍵。
  • Timestamp表示修改該記錄的時間。
  • Column_priv欄位表示對錶中的列的操作許可權，包括Select、Insert、Update和References。

3.4 procs_priv表

procs_priv表可以對 儲存過程和儲存函式設定操作許可權 ，表結構如下：

mysql> desc mysql.procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type                                   | Null | Key | Default           | Extra                                         |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host         | char(255)                              | NO   | PRI |                   |                                               |
| Db           | char(64)                               | NO   | PRI |                   |                                               |
| User         | char(32)                               | NO   | PRI |                   |                                               |
| Routine_name | char(64)                               | NO   | PRI |                   |                                               |
| Routine_type | enum('FUNCTION','PROCEDURE')           | NO   | PRI | NULL              |                                               |
| Grantor      | varchar(288)                           | NO   | MUL |                   |                                               |
| Proc_priv    | set('Execute','Alter Routine','Grant') | NO   |     |                   |                                               |
| Timestamp    | timestamp                              | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

4.訪問控制(瞭解)

4.1 連線核實階段

當使用者試圖連線MySQL伺服器時，伺服器基於使用者的身份以及使用者是否能提供正確的密碼驗證身份來確定接受或者拒絕連線。即客戶端使用者會在連線請求中提供使用者名稱、主機地址、使用者密碼，MySQL伺服器接收到使用者請求後，會使用user表中的host、user和authentication_string這3個欄位匹配客戶端提供資訊。

伺服器只有在user表記錄的Host和User欄位匹配客戶端主機名和使用者名稱，並且提供正確的密碼時才接受連線。如果連線核實沒有透過，伺服器就完全拒絕訪問；否則，伺服器接受連線，然後進入階段2等待使用者請求。

4.2 請求核實階段

一旦建立了連線，伺服器就進入了訪問控制的階段2，也就是請求核實階段。對此連線上進來的每個請求，伺服器檢查該請求要執行什麼操作、是否有足夠的許可權來執行它，這正是需要授權表中的許可權列發揮作用的地方。這些許可權可以來自user、db、table_priv和column_priv表。

確認許可權時，MySQL首先檢查user表，如果指定的許可權沒有在user表中被授予，那麼MySQL就會繼續檢查db表，db表是下一安全層級，其中的許可權限定於資料庫層級，在該層級的SELECT許可權允許使用者檢視指定資料庫的所有表中的資料；如果在該層級沒有找到限定的許可權，則MySQL繼續 檢查tables_priv表以及columns_priv表，如果所有許可權表都檢查完畢，但還是沒有找到允許的許可權操作，MySQL將返回錯誤資訊，使用者請求的操作不能執行，操作失敗。

提示： MySQL透過向下層級的順序（從user表到columns_priv表）檢查許可權表，但並不是所有的許可權都要執行該過程。例如，一個使用者登入到MySQL伺服器之後只執行對MySQL的管理操作，此時只涉及管理許可權，因此MySQL只檢查user表。另外，如果請求的許可權操作不被允許，MySQL也不會繼續檢查下一層級的表。

5.角色許可權管理

5.1 角色的理解

引入角色的目的是方便管理擁有相同許可權的使用者。恰當的許可權設定，可以確保資料的安全性，這是至關重要的。

5.2 建立角色

建立角色使用CREATE ROLE語句，語法如下：

CREATE ROLE 'role_name' [@'host_name'] [,'role_name'[@'host_name']]...

角色名稱的命名規則和使用者名稱類似。如果host_name省略，預設為%，role_name不可省略，不可為空。

練習：建立一個經理的角色

CREATE ROLE 'manager'@'localhost';

5.3 給角色賦予許可權

建立角色之後，預設這個角色是沒有任何許可權的，我們需要給角色授權。給角色授權的語法結構是：

GRANT 許可權1,許可權2,... ON table_name TO 'role_name'[@'host_name'];

上述語句中privileges代表許可權的名稱，多個許可權以逗號隔開。可使用SHOW語句查詢許可權名稱。

mysql> show privileges\G
*************************** 1. row ***************************
Privilege: Alter
  Context: Tables
  Comment: To alter the table
*************************** 2. row ***************************
Privilege: Alter routine
  Context: Functions,Procedures
  Comment: To alter or drop stored functions/procedures
*************************** 3. row ***************************
Privilege: Create
  Context: Databases,Tables,Indexes
  Comment: To create new databases and tables
...
*************************** 60. row ***************************
Privilege: INNODB_REDO_LOG_ENABLE
  Context: Server Admin
  Comment: 
*************************** 61. row ***************************
Privilege: INNODB_REDO_LOG_ARCHIVE
  Context: Server Admin
  Comment: 
*************************** 62. row ***************************
Privilege: REPLICATION_APPLIER
  Context: Server Admin
  Comment: 
62 rows in set (0.00 sec)

舉例，給manager角色賦予dbtest1的所有表的增刪改查許可權

grant insert,delete,update,select on dbtest1.* to 'manager'@'localhost';

5.4 檢視角色的許可權

賦予角色許可權之後，我們可以透過 SHOW GRANTS 語句，來檢視許可權是否建立成功了：

mysql> show grants for 'manager'@'localhost';#在使用角色或使用者時儘量指明user和host，這樣才能確定是哪一個使用者
+------------------------------------------------------------------------------+
| Grants for manager@localhost                                                 |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`localhost`                                  |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `dbtest1`.* TO `manager`@`localhost` |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

只要你建立了一個角色，系統就會自動給你一個“ USAGE ”許可權，意思是 連線登入資料庫的許可權 。程式碼的最後一行代表了我們給角色“manager”賦予的許可權，也就是對dbtest1.*的增刪改查許可權。

5.5 回收角色的許可權

角色授權後，可以對角色的許可權進行維護，對許可權進行新增或撤銷。新增許可權使用GRANT語句，與使用者撤銷授權相同。撤銷角色許可權也使用REVOKE語句。修改了角色的許可權，會影響擁有該角色的賬號的許可權。

撤銷角色許可權的SQL語法如下：

REVOKE privileges ON tablename FROM 'rolename';

舉例：撤銷manager角色的dbtest1下所有表的刪除許可權

revoke delete on dbtest.* from 'manager'@'localhost';

5.6 刪除角色

當我們需要對業務重新整合的時候，可能就需要對之前建立的角色進行清理，刪除一些不會再使用的角色。

drop role rolename[,rolename1...]; #[]內容可寫可不寫，看需要

舉例：刪除manager角色

drop role 'manager'@'localhost';

5.7 給使用者賦予角色

角色建立並授權後，要賦給使用者並啟用狀態才能發揮作用。給使用者新增角色可使用GRANT語句，語法形式如下：

GRANT role [,role2,...] TO user [,user2,...];

在上述語句中，role代表角色，user代表使用者。可將多個角色同時賦予多個使用者，用逗號隔開即可。

舉例，首先建立一個'worker'@'%'角色，授予該角色所有庫的表的所有許可權，並新建使用者'changming06'@'%'，將worker角色賦予該使用者

mysql> create role 'worker'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'worker'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'changming06'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant 'worker'@'%' to 'changming06'@'%';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

mysql> grant 'worker'@'%' to 'changming06'@'%';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

mysql> grant system_user on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant 'worker'@'%' to 'changming06'@'%';
Query OK, 0 rows affected (0.00 sec)

注意，mysql 8.0.16之後，mysql官方新增加了一個system_user許可權，所以需要給操作使用者賦予該許可權，才能擁有給使用者賦予角色的許可權

此時，使用'changming06'@'%'角色，檢視資料庫時，只有

mysql> show databases;#證明賦予changming06的角色還未生效
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

5.8 啟用角色

方式1，使用set default role命令啟用角色

SET DEFAULT ROLE ALL TO 'changming06'@'%';#只是啟用了賦予該使用者的角色

方式2：將activate_all_roles_on_login設定為ON

  • 預設情況下
mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (0.01 sec)
  • 修改
set global activate_all_roles_on_login=ON;

這條 SQL 語句的意思是，對所有角色永久啟用。執行這條語句之後，使用者才真正擁有了賦予角色的所有許可權。

5.9 撤銷使用者的角色

撤銷使用者角色的SQL語法如下：

REVOKE role FROM user;

舉例：撤銷賦予'changming06'@'%'使用者的'worker'@'%'角色

mysql> revoke 'worker'@'%' from 'changming06'@'%';
Query OK, 0 rows affected (0.00 sec)

5.10 設定強制角色

強制角色是給每個建立賬戶的預設角色，不需要手動設定了。強制角色無法被revoke和drop。

方式1：配置檔案中設定

[mysqld]
mandatory_roles='role1,role2@localhost'#可以寫多個角色...

方式2：執行時設定

set persist mandatory_roles='role1,role2@localhost';#服務重啟後仍然有效
set global mandatory_roles='role1,role2@localhost';#服務重啟後失效

5.11 小結

語句 作用
CREATE ROLE or DROP ROLE 常見或刪除角色
GRANT or REVOKE 給角色或使用者分配許可權
SHOW GRANTS 顯示 賬號或角色擁有的 許可權或角色
SET DEFAULT ROLE 設定賬戶預設使用什麼角色
SET ROLE 改變當前會話的角色
CURRENT_ROLE函式 顯示當前會話的角色
mandatory_roles和activate_all_roles_on_login變數 允許定義使用者登入時強制或啟用授權的角色

6.配置檔案的使用

6.1 配置檔案格式

和在命令列中指定的啟動選項不同的是，配置檔案中的啟動選項被劃分為若干組，每個組有一個組名，用中括號[]括起來

[server]
(具體的配置項..)

[mysqld]
(具體的配置項..)

[mysqld_safe]
(具體的配置項..)

[client]
(具體的配置項..)

[mysql]
(具體的配置項..)

[mysqladmin]
(具體的配置項..)

像這個配置檔案裡就定義了許多個組，組名分別是server . mysqld、mysqld_safe、 client、mysql.mysqladmin。每個組下邊可以定義若干個啟動選項，我們以[server]組為例來看一下填寫啟動選項的形式(其他組中啟動選項的形式是一樣的):

[server]
option1	#該選項不需要選項值 配置檔案中出現為true 不出現為false
option2=value2	#該選項需要選項值

在配置檔案中指定啟動選項的語法類似於命令列語法，但是配置檔案中指定的啟動選項不允許加 --字首，並且每行只指定一個選項，而且=周圍可以有空白字元（命令列中選項名、=、選項值之間不允許有空白字元)。另外，在配置檔案中，我們可以使用#來新增註釋，從#出現直到行尾的內容都屬於註釋內容，讀取配置檔案時會忽略這些註釋內容。|

6.2 啟動命令與選項組

配置檔案中不同的選項組是給不同的啟動命令使用的。不過有兩個選項組特別

  • [server]組下的啟動選項將作用於所有的伺服器程式
  • [client]組下的啟動選項將作用於所有的客戶端程式

下面是啟動命令能讀取的選項組都有那些

啟動命令 類別 讀取組
mysqld 啟動伺服器 [mysqld]、[server]
mysqld_safe 啟動伺服器 [mysqld]、[server]、[mysqld_safe]
mysql.server 啟動伺服器 [mysqld]、[server]、[mysql.server]
mysql 啟動客戶端 [mysql]、[client]
mysqladmin 啟動客戶端 [mysqladmin]、[client]
mysqldump 啟動客戶端 [mysqldump、client]

比如，在/etc/mysql/my.cnf這個配置檔案中新增一些內容

[server]
skip-networking
default-srorge-engine=MyISAM

然後使用mysqld啟動mysql服務

mysqld

雖然在命令列沒有新增啟動選項，但是在程式啟動的時候，就會預設的到我們上邊提到的配置檔案路徑下查詢配置檔案，其中就包括/etc/my .cnf。又由於mysqld命令可以讀取[server]選項組的內容，所以skip-networking和default-storage-engine=MyISAM這兩個選項是生效的。可以把這些啟動選項放在[client]組裡再試試用mysqld啟動伺服器程式，就不生效。

6.3 特定MySQL版本的專用選項組

我們可以在選項組的名稱後加上特定的MySQL版本號，比如對於[mysqld]選項組來說，我們可以定義一個[mysqld-5.7]的選項組，它的含義和[mysqld]一樣，只不過只有版本號為5.7的mysqld程式才能使用這個選項組中的選項,

6.4 同一配置檔案中多個組的優先順序

同一命令可以訪問配置檔案中的多個組，比如mysqld可以訪問[mysqld]、[server]組，如果在同一個配置檔案中，比如/etc/my.cnf，在這些組裡出現了同樣的配置項，比如這樣

[server]
default-storage-engine=InnoDB

[mysqld]
default-storage-engine=MyISAM

那麼，將以最後一個出現的組中的啟動選項為準，比方說例子中default-storage-engine即出現在[mysqld]組也出現在[server]組，因為[mysqld]組在[server]組後邊，就以[mysqld]組中的配置項為準

6.5 命令列和配置檔案中啟動項的區別

在命令列上指定的絕大部分啟動選項都可以放到配置檔案中，但是又一些選項是專門為命令列設計的，比方說defaults-extra-file、defaults-file這樣的選項本身就是為了指定配置檔案路徑的，寫在配置檔案中不合適。

如果同一個啟動選項即出現在命令列中，又出現在配置檔案中，那麼以命令列中啟動項為準。

總結，/etc/my.cnf會覆蓋預設的啟動配置項，命令列輸入的啟動項，會覆蓋/etc/my.cnf中的配置項，/etc/my.cnf中沒有覆蓋預設的。

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。

相關文章