1.用戶管理

MySQL用戶可以分為普通用戶和root用戶。root用戶是超級(jí)管理員，擁有所有權(quán)限，包括創(chuàng)建用戶、刪除用戶和修改用戶的密碼等管理權(quán)限。普通用戶只擁有被授予的各種權(quán)限。

MySQL提供了很多語(yǔ)句用來(lái)管理用戶賬號(hào)，這些語(yǔ)句可以用來(lái)管理包括登錄和退出MySQL服務(wù)器，創(chuàng)建用戶，刪除用戶、密碼管理和權(quán)限管理等內(nèi)容。

MySQL數(shù)據(jù)庫(kù)的安全性需要通過(guò)賬戶管理來(lái)保證。

1.1 登錄MySQL服務(wù)器

啟動(dòng)MySQL服務(wù)后，可以通過(guò)mysql命令來(lái)登錄MySQL服務(wù)器，命令如下：

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

下面詳細(xì)介紹命令中的參數(shù)：

• -h參數(shù)后面接主機(jī)名或者主機(jī)IP，hostname為主機(jī)名，hostIP為主機(jī)IP。
• -P參數(shù)后面接MySQL服務(wù)的端口，通過(guò)該參數(shù)連接到指定的端口。MySQL服務(wù)的默認(rèn)端口是3306，不使用該參數(shù)時(shí)自動(dòng)連接到3306端口，port為連接的端口號(hào)。
• -u參數(shù)后面接用戶名，username為用戶名。
• -p參數(shù)會(huì)提示輸入密碼。
• DatabaseName參數(shù)指明登錄到哪一個(gè)數(shù)據(jù)庫(kù)中。如果沒(méi)有該參數(shù)，就會(huì)直接登錄到MySQL數(shù)據(jù)庫(kù)中，然后可以使用USE命令來(lái)選擇數(shù)據(jù)庫(kù)。
• -e參數(shù)后面可以直接加SQL語(yǔ)句。登錄MySQL服務(wù)器以后即可執(zhí)行這個(gè)SQL語(yǔ)句，然后退出MySQL服務(wù)器。

舉例

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

1.2 創(chuàng)建用戶

CREATE USER語(yǔ)句的基本語(yǔ)法如下

CREATE USER 用戶名 [IDENTIFIED BY '密碼'][,用戶名 [IDENTIFIED BY '密碼']];

• 用戶名參數(shù)表示新建用戶的賬戶，由用戶（User）和主機(jī)名（Host）構(gòu)成；
• “[ ]”表示可選，也就是說(shuō)，可以指定用戶登錄時(shí)需要密碼驗(yàn)證，也可以不指定密碼驗(yàn)證，這樣用戶可以直接登錄。不過(guò)，不指定密碼的方式不安全，不推薦使用。如果指定密碼值，這里需要使用IDENTIFIED BY指定明文密碼值。
• CREATE USER語(yǔ)句可以同時(shí)創(chuàng)建多個(gè)用戶。

舉例：

CREATE USER zhang3 IDENTIFIED BY 'abc12345';# 默認(rèn)host是%


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

1.3 修改用戶

privileges n.權(quán)限

修改用戶名:

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

flush privileges;

1.4 刪除用戶

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

使用DROP USER語(yǔ)句來(lái)刪除用戶時(shí)，操作用戶，必須擁有DROP USER權(quán)限。DROP USER 語(yǔ)句的基本語(yǔ)法如下

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

舉例

DROP USER wang5;#默認(rèn)刪除host為%的用戶，這是因?yàn)樵趍ysql.user表的定義，host和user列構(gòu)成了復(fù)合主鍵
#等價(jià)于DROP USER 'wang5'@'%' 

DROP USER 'li4'@'localhost';

方式2：使用delete方式刪除

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

#執(zhí)行完DELETE命令后要使用FLUSH命令來(lái)使用戶生效
flush privileges;

舉例

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

flush privileges;

注意：不推薦通過(guò) DELETE FROM USER u WHERE USER='li4' 進(jìn)行刪除，系統(tǒng)會(huì)有殘留信息保留。而drop user命令會(huì)刪除用戶以及對(duì)應(yīng)的權(quán)限，執(zhí)行命令后你會(huì)發(fā)現(xiàn)mysql.user表和mysql.db表的相應(yīng)記錄都消失了。

1.5 設(shè)置當(dāng)前用戶密碼

舊的寫法如下

# 修改當(dāng)前用戶的密碼：（MySQL5.7測(cè)試有效）
SET PASSWORD = PASSWORD('123456');

這里介紹推薦的寫法：

1.使用ALTER USER命令來(lái)修改當(dāng)前用戶密碼 用戶可以使用ALTER命令來(lái)修改自身密碼，如下語(yǔ)句代表修改當(dāng)前登錄用戶的密碼?；菊Z(yǔ)法如下：

ALTER USER USER() IDENTIFIED BY 'new_password';

2.使用set語(yǔ)句來(lái)修改當(dāng)前用戶密碼 使用root用戶登錄MySQL后，可以使用SET語(yǔ)句來(lái)修改密碼，具體SQL語(yǔ)句如下：

SET PASSWORD='new_password';

該語(yǔ)句會(huì)自動(dòng)將密碼加密后再賦給用戶。

1.6 修改其他用戶密碼

1.使用ALTER語(yǔ)句來(lái)修改普通用戶的密碼 可以使用ALTER USER語(yǔ)句來(lái)修改普通用戶的密碼。基本語(yǔ)法形式如下：

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

2.使用SET命令來(lái)修改普通用戶的密碼 使用root用戶登錄到MySQL服務(wù)器后，可以使用SET語(yǔ)句來(lái)修改普通用戶的密碼。SET語(yǔ)句的代碼如下：

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

3.使用UPDATE語(yǔ)句修改普通用戶的密碼（不推薦）

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

1.7 MySQL8密碼管理(了解)

1.7.1 密碼過(guò)期策略

• 在MySQL中，數(shù)據(jù)庫(kù)管理員可以手動(dòng)設(shè)置賬號(hào)密碼過(guò)期，也可以建立一個(gè)自動(dòng)密碼過(guò)期策略。
• 過(guò)期策略可以是全局的，也可以為每個(gè)賬號(hào)設(shè)置單獨(dú)的過(guò)期策略。

ALTER USER user password EXPIRE;#expire vi.失效，終止

舉例

ALTER USER 'wang5'@'%' password EXPIRE;#手動(dòng)設(shè)置user為wang5且host地址為%的用戶密碼過(guò)期

• 方式1：使用SQL語(yǔ)句更改該變量的值并持久化

SET PERSIST default_password_lifetime = 180; # 建立全局策略，設(shè)置密碼每隔180天過(guò)期

• 方式2：配置文件my.cnf中進(jìn)行維護(hù)

[mysqld]
default_password_lifetime=180 #建立全局策略，設(shè)置密碼每隔180天過(guò)期

手動(dòng)設(shè)置指定時(shí)間過(guò)期方式2：?jiǎn)为?dú)設(shè)置

每個(gè)賬號(hào)既可延用全局密碼過(guò)期策略，也可單獨(dú)設(shè)置策略。在CREATE USER和ALTER USER語(yǔ)句上加入PASSWORD EXPIRE選項(xiàng)可實(shí)現(xiàn)單獨(dú)設(shè)置策略。下面是一些語(yǔ)句示例。

#設(shè)置kangshifu賬號(hào)密碼每90天過(guò)期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

#設(shè)置密碼永不過(guò)期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;

#延用全局密碼過(guò)期策略：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;

1.7.2 密碼重用策略

手動(dòng)設(shè)置密碼重用方式1：全局

• 方式1，使用SQL

SET PERSIST password_history = 6; #設(shè)置不能選擇最近使用過(guò)的6個(gè)密碼

SET PERSIST password_reuse_interval = 365; #設(shè)置不能選擇最近一年內(nèi)的密碼

• 方式2：my.cnf配置文件

[mysqld]
password_history=6
password_reuse_interval=365

手動(dòng)設(shè)置密碼重用方式2：?jiǎn)为?dú)設(shè)置

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

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

#既不能使用最近5個(gè)密碼，也不能使用365天內(nèi)的密碼
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.用戶(賬號(hào))權(quán)限管理

關(guān)于MySQL的權(quán)限簡(jiǎn)單的理解就是MYSQL允許你做你權(quán)力以內(nèi)的事情，不可以越界,比如只允許你執(zhí)行 SELECT操作，那么你就不能執(zhí)行 UPDATE操作，只允許你從某臺(tái)機(jī)器上連接 MYSQL，那么你就不能從除那臺(tái)機(jī)器以外的其他機(jī)器連接MySQL。

2.1 權(quán)限列表

MySQL到底都有那些權(quán)限呢?

mysql> show privileges;#有62條權(quán)限

常用的權(quán)限

• 1.CREATE和DROP權(quán)限 可以創(chuàng)建新的數(shù)據(jù)庫(kù)和表，或刪除（移掉）已有的數(shù)據(jù)庫(kù)和表。如果將MySQL數(shù)據(jù)庫(kù)中的DROP權(quán)限授予某用戶，用戶就可以刪除MySQL訪問(wèn)權(quán)限保存的數(shù)據(jù)庫(kù)。
• 2.SELECT、INSERT、UPDATE和DELETE權(quán)限 允許在一個(gè)數(shù)據(jù)庫(kù)現(xiàn)有的表上實(shí)施操作。
• 3.SELECT權(quán)限 只有在它們真正從一個(gè)表中檢索行時(shí)才被用到。
• 4.INDEX權(quán)限 允許創(chuàng)建或刪除索引，INDEX適用于已有的表。如果具有某個(gè)表的CREATE權(quán)限，就可以在CREATE TABLE語(yǔ)句中包括索引定義。
• 5.ALTER權(quán)限 可以使用ALTER TABLE來(lái)更改表的結(jié)構(gòu)和重新命名表。
• 6.CREATE ROUTINE權(quán)限 用來(lái)創(chuàng)建保存的程序（函數(shù)和程序），ALTER ROUTINE權(quán)限用來(lái)更改和刪除保存的程序， EXECUTE權(quán)限 用來(lái)執(zhí)行保存的程序。
• 7.GRANT權(quán)限 允許授權(quán)給其他用戶，可用于數(shù)據(jù)庫(kù)、表和保存的程序。
• 8.FILE權(quán)限 使用戶可以使用LOAD DATA INFILE和SELECT ... INTO OUTFILE語(yǔ)句讀或?qū)懛?wù)器上的文件，任何被授予FILE權(quán)限的用戶都能讀或?qū)慚ySQL服務(wù)器上的任何文件（說(shuō)明用戶可以讀任何數(shù)據(jù)庫(kù)目錄下的文件，因?yàn)榉?wù)器可以訪問(wèn)這些文件）。

MySQL的權(quán)限分布

2.2 授予權(quán)限的原則

權(quán)限控制主要是出于安全因素，因此需要遵循以下幾個(gè)經(jīng)驗(yàn)原則：

1、只授予能滿足需要的最小權(quán)限，防止用戶干壞事。比如用戶只是需要查詢，那就只給select權(quán)限就可以了，不要給用戶賦予update、insert或者delete權(quán)限。

2、創(chuàng)建用戶的時(shí)候限制用戶的登錄主機(jī)，一般是限制成指定IP或者內(nèi)網(wǎng)IP段。

3、為每個(gè)用戶設(shè)置滿足密碼復(fù)雜度的密碼。

4、定期清理不需要的用戶，回收權(quán)限或者刪除用戶。

2.3 授予權(quán)限

給用戶授權(quán)的方式有 2 種，分別是通過(guò)把角色賦予用戶給用戶授權(quán)和直接給用戶授權(quán)。用戶是數(shù)據(jù)庫(kù)的使用者，我們可以通過(guò)給用戶授

予訪問(wèn)數(shù)據(jù)庫(kù)中資源的權(quán)限，來(lái)控制使用者對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)，消除安全隱患。

授權(quán)命令格式

#用戶名@用戶地址需要 'user'@'host'格式 user是msyql.user表中的user字段值 host是host字符值
GRANT 權(quán)限1,權(quán)限2,... ON 數(shù)據(jù)庫(kù)名稱.表名 TO 用戶名@用戶地址 [IDENTIFIED BY '密碼口令']

• 如果發(fā)現(xiàn)賦予權(quán)限的用戶，還未創(chuàng)建，會(huì)新建該用戶。(注意在MySQL 8.0.11版本之后，不允許使用GRANT命令創(chuàng)建用戶了，也就是說(shuō)在8.0.11之后的版本，必須先創(chuàng)建用戶，再賦予權(quán)限)
• 給li4用戶授予dbtest1庫(kù)下的所有表的增刪改查權(quán)限

grant select,insert,update,delete on datest1.* to 'li4'@'%';

• 授予通過(guò)網(wǎng)絡(luò)方式登錄的joe用戶 ，對(duì)所有庫(kù)所有表的全部權(quán)限，注意這里唯獨(dú)不包括grant的權(quán)限

grant all privileges on *.* to 'joe'@'%' IDENTIFIED BY '密碼';

• ALL PRIVILEGES是表示所有權(quán)限。你也可以使用SELECT、UPDATE等權(quán)限。

• ON用來(lái)指定權(quán)限針對(duì)哪些庫(kù)和表。

• .中前面的號(hào)用來(lái)指定數(shù)據(jù)庫(kù)名，后面的號(hào)用來(lái)指定表名。這里的×表示所有的。

• TO表示將權(quán)限賦予某個(gè)用戶。

• li4@'localhost'表示li⒁4用戶，@后面接限制的主機(jī)，可以是IP、IP段、域名以及%，%表示任何地方。

注意:這里%有的版本不包括本地，以前碰到過(guò)給某個(gè)用戶設(shè)置了%允許任何地方登錄，但是在本地登錄不了，這個(gè)和版本有關(guān)系。遇到這個(gè)問(wèn)題再加一個(gè)localhost的用戶就可以了。

• IDENTIFIED BY指定用戶的登錄密碼。

如果需要賦予包括GRANT的權(quán)限，添加參數(shù)"WITH GRANT OPTION"這個(gè)選項(xiàng)即可，表示該用戶可以將自己擁有的權(quán)限授權(quán)給別人。經(jīng)常有人在創(chuàng)建操作用戶的時(shí)候不指定WITH GRANT OPTION選項(xiàng)導(dǎo)致后來(lái)該用戶不能使用GRANT命令創(chuàng)建用戶或者給其它用戶授權(quán)。

可以使用GRANT重復(fù)給用戶添加權(quán)限，權(quán)限疊加，比如你先給用戶添加一個(gè)SELECT權(quán)限，然后又給用戶添加一個(gè)INSERT權(quán)限。那么該用戶就同時(shí)擁有了SELECT和INSERT權(quán)限。

我們?cè)陂_(kāi)發(fā)應(yīng)用的時(shí)候，經(jīng)常會(huì)遇到一種需求，就是要根據(jù)用戶的不同，對(duì)數(shù)據(jù)進(jìn)行橫向和縱向的分組。

• 所謂橫向的分組，就是指用戶可以接觸到的數(shù)據(jù)的范圍，比如可以看到哪些表的數(shù)據(jù)；
• 所謂縱向的分組，就是指用戶對(duì)接觸到的數(shù)據(jù)能訪問(wèn)到什么程度，比如能看、能改，甚至是刪除。

2.4 查看權(quán)限

• 查看當(dāng)前用戶權(quán)限

show grants;

#或
show grants for current_user;

#或
show grants for current_user();

• 查看某個(gè)用戶的全局權(quán)限

show grants for 'user'@'host';

2.5 收回權(quán)限

收回權(quán)限就是取消已經(jīng)賦予用戶的某些權(quán)限。收回用戶不必要的權(quán)限可以在一定程度上保證系統(tǒng)的安全性。MySQL中使用REVOKE語(yǔ)句

取消用戶的某些權(quán)限。使用REVOKE收回權(quán)限之后，用戶賬戶的記錄將從db、host、tables_priv和columns_priv表中刪除，但是用戶賬戶

記錄仍然在user表中保存（刪除user表中的賬戶記錄使用DROP USER語(yǔ)句）。

注意：在將用戶賬戶從user表刪除之前，應(yīng)該收回相應(yīng)用戶的所有權(quán)限。

• 收回權(quán)限命令

#用戶名@用戶地址需要 'user'@'host'格式 user是msyql.user表中的user字段值 host是host字符值
REVOKE 權(quán)限1,權(quán)限2,.... ON 數(shù)據(jù)庫(kù)名.表名 FROM 用戶名@用戶地址;

• 舉例

#收回全庫(kù)全表的所有權(quán)限
REVOKE ALL PRIVILEGES ON *.* FROM 'joe'@'%';

#收回mysql庫(kù)下的所有表的增刪改查權(quán)限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM 'joe'@'localhost';

• 注意: 有些權(quán)限可能需要被操作的用戶重新登錄后才生效。

總結(jié)
有一些程序員喜歡使用Root超級(jí)用戶來(lái)訪問(wèn)數(shù)據(jù)庫(kù)，完全把權(quán)限控制放在應(yīng)用層面實(shí)現(xiàn)。這樣當(dāng)然也是可以的。但建議大家，盡量使用數(shù)據(jù)庫(kù)自己的角色和用戶機(jī)制來(lái)控制訪問(wèn)權(quán)限，不要輕易用Root賬號(hào)。因?yàn)镽oot賬號(hào)密碼放在配置文件里面不安全，一旦泄露，數(shù)據(jù)庫(kù)就會(huì)完全失去保護(hù)。
而且，MySQL的權(quán)限控制功能十分完善，應(yīng)該盡量利用，可以提高效率，而且安全可靠。

3.權(quán)限表

MySQL服務(wù)器通過(guò)權(quán)限表來(lái)控制用戶對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)，權(quán)限表存放在mysql數(shù)據(jù)庫(kù)中。MySQL數(shù)據(jù)庫(kù)系統(tǒng)會(huì)根據(jù)這些權(quán)限表的內(nèi)容為每個(gè)用戶賦予相應(yīng)的權(quán)限。這些權(quán)限表中最重要的是user表、db表。除此之外，還有table_priv表、column_priv表和proc_priv表等。在MySQL啟動(dòng)時(shí)，服務(wù)器將這些數(shù)據(jù)庫(kù)表中權(quán)限信息的內(nèi)容讀入內(nèi)存。

3.1 user表

user表是MySQL中最重要的一個(gè)權(quán)限表，記錄用戶賬號(hào)和權(quán)限信息，有51個(gè)字段(Linux mysql 8.0.25版本)，有45個(gè)字段(Windows mysql 5.7.19)，不同小版本可能不同，沒(méi)測(cè)試過(guò)。

DESC mysql.user\G#在命令行下查看user表的表結(jié)構(gòu)

3.1.1 范圍列(用戶列)

• host列: 表示連接類型

  • %表示所有遠(yuǎn)程通過(guò) TCP方式的連接

  • IP 地址如 (192.168.1.2、127.0.0.1) 通過(guò)制定ip地址進(jìn)行的TCP方式的連接

  • 機(jī)器名通過(guò)制定網(wǎng)絡(luò)中的機(jī)器名進(jìn)行的TCP方式的連接

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

  • localhost本地方式通過(guò)命令行方式的連接 ，比如mysql -u xxx -p xxx 方式的連接。

• user列：表示用戶名，同一用戶通過(guò)不同方式鏈接的權(quán)限是不一樣的。

• password ： 密碼

• 所有密碼串通過(guò) password(明文字符串) 生成的密文字符串。MySQL 8.0 在用戶管理方面增加了角色管理，默認(rèn)的密碼加密方式也做了調(diào)整，由之前的SHA1改為了SHA2，不可逆 。同時(shí)加上 MySQL 5.7 的禁用用戶和用戶過(guò)期的功能，MySQL 在用戶管理方面的功能和安全性都較之前版本大大的增強(qiáng)了。

• mysql 5.7 及之后版本的密碼保存到 authentication_string 字段中不再使用password 字段。

3.1.2 權(quán)限列

• Grant_priv字段
  • 表示是否擁有GRANT權(quán)限
• Shutdown_priv字段
  • 表示是否擁有停止MySQL服務(wù)的權(quán)限
• Super_priv字段
  • 表示是否擁有超級(jí)權(quán)限
• Execute_priv字段
  • 表示是否擁有EXECUTE權(quán)限。擁有EXECUTE權(quán)限，可以執(zhí)行存儲(chǔ)過(guò)程和函數(shù)。
• Select_priv , Insert_priv等
  • 表示是否擁有SELECT，INSERT權(quán)限。

3.1.3 安全列

安全列只有6個(gè)字段，其中兩個(gè)是ssl相關(guān)的（ssl_type、ssl_cipher），用于加密 ；兩個(gè)是x509相關(guān)的（x509_issuer、x509_subject），用于標(biāo)識(shí)用戶；另外Plugin字段用于驗(yàn)證用戶身份的插件，該字段不能為空。如果該字段為空，服務(wù)器就使用內(nèi)建授權(quán)驗(yàn)證機(jī)制驗(yàn)證用戶身份。

3.1.4 資源控制列

資源控制列的字段用來(lái)限制用戶使用的資源，包含4個(gè)字段，分別為：

• max_questions，用戶每小時(shí)允許執(zhí)行的查詢操作次數(shù)；
• max_updates，用戶每小時(shí)允許執(zhí)行的更新操作次數(shù)；
• max_connections，用戶每小時(shí)允許執(zhí)行的連接操作次數(shù)；
• max_user_connections，用戶允許同時(shí)建立的連接次數(shù)。

小結(jié)，user表和創(chuàng)建的表一樣，有權(quán)限的前提下，可以進(jìn)行增刪改查操作。

SELECT * FROM mysql.user \G;#查看用戶, 以列的方式顯示數(shù)據(jù)

#查看一部分字段
SELECT 
	host,user,authentication_string,select_priv,insert_priv,drop_priv
FROM 
	mysql.user;

3.2 db表

使用DESC查看db表的基本結(jié)構(gòu)：

DESC mysql.db;

1. 用戶列

db表用戶列有3個(gè)字段，分別是Host、User、Db。這3個(gè)字段分別表示主機(jī)名、用戶名和數(shù)據(jù)庫(kù)名。表示從某個(gè)主機(jī)連接某個(gè)用戶對(duì)某個(gè)數(shù)據(jù)庫(kù)的操作權(quán)限，這3個(gè)字段的組合構(gòu)成了db表的主鍵。

2. 權(quán)限列

Create_routine_priv和Alter_routine_priv這兩個(gè)字段決定用戶是否具有創(chuàng)建和修改存儲(chǔ)過(guò)程的權(quán)限。

3.3 tables_priv表和columns_priv表

tables_priv表用來(lái)對(duì)表設(shè)置操作權(quán)限，columns_priv表用來(lái)對(duì)表的某一列設(shè)置權(quán)限。

desc tables_priv;

desc columns_priv;

3.3.1 tables_priv表

tables_priv表有8個(gè)字段，分別是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和Column_priv，各個(gè)字段說(shuō)明如下：

• Host 、 Db 、 User 和 Table_name 四個(gè)字段分別表示主機(jī)名、數(shù)據(jù)庫(kù)名、用戶名和表名。這四個(gè)字段組合成了tables_priv的主鍵。
• Grantor表示修改該記錄的用戶。
• Timestamp表示修改該記錄的時(shí)間。
• Table_priv 表示對(duì)象的操作權(quán)限。包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter。
• Column_priv字段表示對(duì)表中的列的操作權(quán)限，包括Select、Insert、Update和References。

3.3.2 columns_priv表

columns_priv表有7個(gè)字段，分別是Host、Db、User、Table_name、Column_name、Timestamp、Column_priv，各個(gè)字段說(shuō)明如下：

• Host 、 Db 、 User 、Table_name和 Column_name 四個(gè)字段分別表示主機(jī)名、數(shù)據(jù)庫(kù)名、用戶名、表名和列名。這五個(gè)字段組合成了columns_priv的主鍵。
• Timestamp表示修改該記錄的時(shí)間。
• Column_priv字段表示對(duì)表中的列的操作權(quán)限，包括Select、Insert、Update和References。

3.4 procs_priv表

procs_priv表可以對(duì) 存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)設(shè)置操作權(quán)限 ，表結(jié)構(gòu)如下：

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.訪問(wèn)控制(了解)

4.1 連接核實(shí)階段

當(dāng)用戶試圖連接MySQL服務(wù)器時(shí)，服務(wù)器基于用戶的身份以及用戶是否能提供正確的密碼驗(yàn)證身份來(lái)確定接受或者拒絕連接。即客戶端用戶會(huì)在連接請(qǐng)求中提供用戶名、主機(jī)地址、用戶密碼，MySQL服務(wù)器接收到用戶請(qǐng)求后，會(huì)使用user表中的host、user和authentication_string這3個(gè)字段匹配客戶端提供信息。

服務(wù)器只有在user表記錄的Host和User字段匹配客戶端主機(jī)名和用戶名，并且提供正確的密碼時(shí)才接受連接。如果連接核實(shí)沒(méi)有通過(guò)，服務(wù)器就完全拒絕訪問(wèn)；否則，服務(wù)器接受連接，然后進(jìn)入階段2等待用戶請(qǐng)求。

4.2 請(qǐng)求核實(shí)階段

一旦建立了連接，服務(wù)器就進(jìn)入了訪問(wèn)控制的階段2，也就是請(qǐng)求核實(shí)階段。對(duì)此連接上進(jìn)來(lái)的每個(gè)請(qǐng)求，服務(wù)器檢查該請(qǐng)求要執(zhí)行什么操作、是否有足夠的權(quán)限來(lái)執(zhí)行它，這正是需要授權(quán)表中的權(quán)限列發(fā)揮作用的地方。這些權(quán)限可以來(lái)自u(píng)ser、db、table_priv和column_priv表。

確認(rèn)權(quán)限時(shí)，MySQL首先檢查user表，如果指定的權(quán)限沒(méi)有在user表中被授予，那么MySQL就會(huì)繼續(xù)檢查db表，db表是下一安全層級(jí)，其中的權(quán)限限定于數(shù)據(jù)庫(kù)層級(jí)，在該層級(jí)的SELECT權(quán)限允許用戶查看指定數(shù)據(jù)庫(kù)的所有表中的數(shù)據(jù)；如果在該層級(jí)沒(méi)有找到限定的權(quán)限，則MySQL繼續(xù) 檢查tables_priv表以及columns_priv表，如果所有權(quán)限表都檢查完畢，但還是沒(méi)有找到允許的權(quán)限操作，MySQL將返回錯(cuò)誤信息，用戶請(qǐng)求的操作不能執(zhí)行，操作失敗。

提示： MySQL通過(guò)向下層級(jí)的順序（從user表到columns_priv表）檢查權(quán)限表，但并不是所有的權(quán)限都要執(zhí)行該過(guò)程。例如，一個(gè)用戶登錄到MySQL服務(wù)器之后只執(zhí)行對(duì)MySQL的管理操作，此時(shí)只涉及管理權(quán)限，因此MySQL只檢查user表。另外，如果請(qǐng)求的權(quán)限操作不被允許，MySQL也不會(huì)繼續(xù)檢查下一層級(jí)的表。

5.角色權(quán)限管理

5.1 角色的理解

引入角色的目的是方便管理?yè)碛邢嗤瑱?quán)限的用戶。恰當(dāng)?shù)臋?quán)限設(shè)定，可以確保數(shù)據(jù)的安全性，這是至關(guān)重要的。

5.2 創(chuàng)建角色

創(chuàng)建角色使用CREATE ROLE語(yǔ)句，語(yǔ)法如下：

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

角色名稱的命名規(guī)則和用戶名類似。如果host_name省略，默認(rèn)為%，role_name不可省略，不可為空。

練習(xí)：創(chuàng)建一個(gè)經(jīng)理的角色

CREATE ROLE 'manager'@'localhost';

5.3 給角色賦予權(quán)限

創(chuàng)建角色之后，默認(rèn)這個(gè)角色是沒(méi)有任何權(quán)限的，我們需要給角色授權(quán)。給角色授權(quán)的語(yǔ)法結(jié)構(gòu)是：

GRANT 權(quán)限1,權(quán)限2,... ON table_name TO 'role_name'[@'host_name'];

上述語(yǔ)句中privileges代表權(quán)限的名稱，多個(gè)權(quán)限以逗號(hào)隔開(kāi)?？墒褂肧HOW語(yǔ)句查詢權(quán)限名稱。

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的所有表的增刪改查權(quán)限

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

5.4 查看角色的權(quán)限

賦予角色權(quán)限之后，我們可以通過(guò) SHOW GRANTS 語(yǔ)句，來(lái)查看權(quán)限是否創(chuàng)建成功了：

mysql> show grants for 'manager'@'localhost';#在使用角色或用戶時(shí)盡量指明user和host，這樣才能確定是哪一個(gè)用戶
+------------------------------------------------------------------------------+
| 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)

只要你創(chuàng)建了一個(gè)角色，系統(tǒng)就會(huì)自動(dòng)給你一個(gè)“ USAGE ”權(quán)限，意思是 連接登錄數(shù)據(jù)庫(kù)的權(quán)限 。代碼的最后一行代表了我們給角色“manager”賦予的權(quán)限，也就是對(duì)dbtest1.*的增刪改查權(quán)限。

5.5 回收角色的權(quán)限

角色授權(quán)后，可以對(duì)角色的權(quán)限進(jìn)行維護(hù)，對(duì)權(quán)限進(jìn)行添加或撤銷。添加權(quán)限使用GRANT語(yǔ)句，與用戶撤銷授權(quán)相同。撤銷角色權(quán)限也使用REVOKE語(yǔ)句。修改了角色的權(quán)限，會(huì)影響擁有該角色的賬號(hào)的權(quán)限。

撤銷角色權(quán)限的SQL語(yǔ)法如下：

REVOKE privileges ON tablename FROM 'rolename';

舉例：撤銷manager角色的dbtest1下所有表的刪除權(quán)限

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

5.6 刪除角色

當(dāng)我們需要對(duì)業(yè)務(wù)重新整合的時(shí)候，可能就需要對(duì)之前創(chuàng)建的角色進(jìn)行清理，刪除一些不會(huì)再使用的角色。

drop role rolename[,rolename1...]; #[]內(nèi)容可寫可不寫，看需要

舉例：刪除manager角色

drop role 'manager'@'localhost';

5.7 給用戶賦予角色

角色創(chuàng)建并授權(quán)后，要賦給用戶并激活狀態(tài)才能發(fā)揮作用。給用戶添加角色可使用GRANT語(yǔ)句，語(yǔ)法形式如下：

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

在上述語(yǔ)句中，role代表角色，user代表用戶。可將多個(gè)角色同時(shí)賦予多個(gè)用戶，用逗號(hào)隔開(kāi)即可。

舉例，首先創(chuàng)建一個(gè)'worker'@'%'角色，授予該角色所有庫(kù)的表的所有權(quán)限，并新建用戶'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官方新增加了一個(gè)system_user權(quán)限，所以需要給操作用戶賦予該權(quán)限，才能擁有給用戶賦予角色的權(quán)限

此時(shí)，使用'changming06'@'%'角色，查看數(shù)據(jù)庫(kù)時(shí)，只有

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設(shè)置為ON

• 默認(rèn)情況下

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 語(yǔ)句的意思是，對(duì)所有角色永久激活。運(yùn)行這條語(yǔ)句之后，用戶才真正擁有了賦予角色的所有權(quán)限。

5.9 撤銷用戶的角色

撤銷用戶角色的SQL語(yǔ)法如下：

REVOKE role FROM user;

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

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

5.10 設(shè)置強(qiáng)制角色

強(qiáng)制角色是給每個(gè)創(chuàng)建賬戶的默認(rèn)角色，不需要手動(dòng)設(shè)置了。強(qiáng)制角色無(wú)法被revoke和drop。

方式1：配置文件中設(shè)置

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

方式2：運(yùn)行時(shí)設(shè)置

set persist mandatory_roles='role1,role2@localhost';#服務(wù)重啟后仍然有效
set global mandatory_roles='role1,role2@localhost';#服務(wù)重啟后失效

5.11 小結(jié)

6.配置文件的使用

6.1 配置文件格式

和在命令行中指定的啟動(dòng)選項(xiàng)不同的是，配置文件中的啟動(dòng)選項(xiàng)被劃分為若干組，每個(gè)組有一個(gè)組名，用中括號(hào)[]括起來(lái)

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

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

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

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

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

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

像這個(gè)配置文件里就定義了許多個(gè)組，組名分別是server . mysqld、mysqld_safe、 client、mysql.mysqladmin。每個(gè)組下邊可以定義若干個(gè)啟動(dòng)選項(xiàng)，我們以[server]組為例來(lái)看一下填寫啟動(dòng)選項(xiàng)的形式(其他組中啟動(dòng)選項(xiàng)的形式是一樣的):

[server]
option1	#該選項(xiàng)不需要選項(xiàng)值 配置文件中出現(xiàn)為true 不出現(xiàn)為false
option2=value2	#該選項(xiàng)需要選項(xiàng)值

在配置文件中指定啟動(dòng)選項(xiàng)的語(yǔ)法類似于命令行語(yǔ)法，但是配置文件中指定的啟動(dòng)選項(xiàng)不允許加 --前綴，并且每行只指定一個(gè)選項(xiàng)，而且=周圍可以有空白字符（命令行中選項(xiàng)名、=、選項(xiàng)值之間不允許有空白字符)。另外，在配置文件中，我們可以使用#來(lái)添加注釋，從#出現(xiàn)直到行尾的內(nèi)容都屬于注釋內(nèi)容，讀取配置文件時(shí)會(huì)忽略這些注釋內(nèi)容。|

6.2 啟動(dòng)命令與選項(xiàng)組

配置文件中不同的選項(xiàng)組是給不同的啟動(dòng)命令使用的。不過(guò)有兩個(gè)選項(xiàng)組特別

• [server]組下的啟動(dòng)選項(xiàng)將作用于所有的服務(wù)器程序
• [client]組下的啟動(dòng)選項(xiàng)將作用于所有的客戶端程序

下面是啟動(dòng)命令能讀取的選項(xiàng)組都有那些

比如，在/etc/mysql/my.cnf這個(gè)配置文件中添加一些內(nèi)容

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

然后使用mysqld啟動(dòng)mysql服務(wù)

mysqld

雖然在命令行沒(méi)有添加啟動(dòng)選項(xiàng)，但是在程序啟動(dòng)的時(shí)候，就會(huì)默認(rèn)的到我們上邊提到的配置文件路徑下查找配置文件，其中就包括/etc/my .cnf。又由于mysqld命令可以讀取[server]選項(xiàng)組的內(nèi)容，所以skip-networking和default-storage-engine=MyISAM這兩個(gè)選項(xiàng)是生效的?？梢园堰@些啟動(dòng)選項(xiàng)放在[client]組里再試試用mysqld啟動(dòng)服務(wù)器程序，就不生效。

6.3 特定MySQL版本的專用選項(xiàng)組

我們可以在選項(xiàng)組的名稱后加上特定的MySQL版本號(hào)，比如對(duì)于[mysqld]選項(xiàng)組來(lái)說(shuō)，我們可以定義一個(gè)[mysqld-5.7]的選項(xiàng)組，它的含義和[mysqld]一樣，只不過(guò)只有版本號(hào)為5.7的mysqld程序才能使用這個(gè)選項(xiàng)組中的選項(xiàng),

6.4 同一配置文件中多個(gè)組的優(yōu)先級(jí)

同一命令可以訪問(wèn)配置文件中的多個(gè)組，比如mysqld可以訪問(wèn)[mysqld]、[server]組，如果在同一個(gè)配置文件中，比如/etc/my.cnf，在這些組里出現(xiàn)了同樣的配置項(xiàng)，比如這樣

[server]
default-storage-engine=InnoDB

[mysqld]
default-storage-engine=MyISAM

那么，將以最后一個(gè)出現(xiàn)的組中的啟動(dòng)選項(xiàng)為準(zhǔn)，比方說(shuō)例子中default-storage-engine即出現(xiàn)在[mysqld]組也出現(xiàn)在[server]組，因?yàn)?code>[mysqld]組在[server]組后邊，就以[mysqld]組中的配置項(xiàng)為準(zhǔn)

6.5 命令行和配置文件中啟動(dòng)項(xiàng)的區(qū)別

在命令行上指定的絕大部分啟動(dòng)選項(xiàng)都可以放到配置文件中，但是又一些選項(xiàng)是專門為命令行設(shè)計(jì)的，比方說(shuō)defaults-extra-file、defaults-file這樣的選項(xiàng)本身就是為了指定配置文件路徑的，寫在配置文件中不合適。

如果同一個(gè)啟動(dòng)選項(xiàng)即出現(xiàn)在命令行中，又出現(xiàn)在配置文件中，那么以命令行中啟動(dòng)項(xiàng)為準(zhǔn)。

總結(jié)，/etc/my.cnf會(huì)覆蓋默認(rèn)的啟動(dòng)配置項(xiàng)，命令行輸入的啟動(dòng)項(xiàng)，會(huì)覆蓋/etc/my.cnf中的配置項(xiàng)，/etc/my.cnf中沒(méi)有覆蓋默認(rèn)的。

只是為了記錄自己的學(xué)習(xí)歷程，且本人水平有限，不對(duì)之處，請(qǐng)指正。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-840918.html

到了這里，關(guān)于MySQL-03.用戶管理和權(quán)限管理的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！