第03章 用戶(hù)與權(quán)限管理

1. 用戶(hù)管理

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ǔ)句"

• -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ù)后面接用戶(hù)名，username為用戶(hù)名。
• -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)建用戶(hù)

CREATE USER 用戶(hù)名 [IDENTIFIED BY '密碼'][,用戶(hù)名 [IDENTIFIED BY '密碼']];

舉例：

CREATE USER zhang3 IDENTIFIED BY '123123'; # 默認(rèn)host是 %
CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';

1.3 修改用戶(hù)

UPDATE mysql.user SET USER='li4' WHERE USER='wang5'; 
FLUSH PRIVILEGES;

1.4 刪除用戶(hù)

方式1：使用DROP方式刪除（推薦）

DROP USER user[,user]…;

舉例：

DROP USER li4 ; # 默認(rèn)刪除host為%的用戶(hù)
DROP USER 'kangshifu'@'localhost';

方式2：使用DELETE方式刪除（不推薦，有殘留信息）

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
FLUSH PRIVILEGES;

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

1. 使用ALTER USER命令來(lái)修改當(dāng)前用戶(hù)密碼

ALTER USER USER() IDENTIFIED BY 'new_password';

2. 使用SET語(yǔ)句來(lái)修改當(dāng)前用戶(hù)密碼

SET PASSWORD='new_password';

1.6 修改其它用戶(hù)密碼

1. 使用ALTER語(yǔ)句來(lái)修改普通用戶(hù)的密碼

ALTER USER user [IDENTIFIED BY '新密碼'] 
[,user[IDENTIFIED BY '新密碼']]…;

2. 使用SET命令來(lái)修改普通用戶(hù)的密碼

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

1.7 MySQL8密碼管理(了解)

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;
   

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

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

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

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

手動(dòng)設(shè)置指定時(shí)間過(guò)期方式2：單獨(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;

2. 密碼重用策略

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

   方式①：使用SQL

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

   方式②：my.cnf配置文件

   [mysqld]
   password_history=6
   password_reuse_interval=365
   

   手動(dòng)設(shè)置密碼重用方式2：單獨(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'@'localhostPASSWORD HISTORY 5
   PASSWORD REUSE INTERVAL 365 DAY;
   

2. 權(quán)限管理

2.1 權(quán)限列表

show privileges;

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

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

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

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

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

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

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

2.3 授予權(quán)限

GRANT 權(quán)限1,權(quán)限2,…權(quán)限n ON 數(shù)據(jù)庫(kù)名稱(chēng).表名稱(chēng) TO 用戶(hù)名@用戶(hù)地址 [IDENTIFIED BY ‘密碼口令’];

• 該權(quán)限如果發(fā)現(xiàn)沒(méi)有該用戶(hù)，則會(huì)直接新建一個(gè)用戶(hù)。
• 給li4用戶(hù)用本地命令行方式，授予atguigudb這個(gè)庫(kù)下的所有表的插刪改查的權(quán)限。

GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost;

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

GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';

2.4 查看權(quán)限

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

SHOW GRANTS; 
# 或 
SHOW GRANTS FOR CURRENT_USER; 
# 或 
SHOW GRANTS FOR CURRENT_USER();

• 查看某用戶(hù)的全局權(quán)限

SHOW GRANTS FOR 'user'@'主機(jī)地址';

2.5 收回權(quán)限

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

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

REVOKE 權(quán)限1,權(quán)限2,…權(quán)限n ON 數(shù)據(jù)庫(kù)名稱(chēng).表名稱(chēng) FROM 用戶(hù)名@用戶(hù)地址;

• 舉例

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

• 注意：須用戶(hù)重新登錄后才能生效

3. 角色管理

3.1 創(chuàng)建角色

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

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

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

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

上述語(yǔ)句中privileges代表權(quán)限的名稱(chēng)，多個(gè)權(quán)限以逗號(hào)隔開(kāi)。可使用SHOW語(yǔ)句查詢(xún)權(quán)限名稱(chēng)

SHOW PRIVILEGES\G

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

SHOW GRANTS FOR 'role_name';

只要你創(chuàng)建了一個(gè)角色，系統(tǒng)就會(huì)自動(dòng)給你一個(gè)“USAGE”權(quán)限，意思是連接登錄數(shù)據(jù)庫(kù)的權(quán)限。

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

REVOKE privileges ON tablename FROM 'rolename';

3.5 刪除角色

DROP ROLE role [,role2]...

注意，如果你刪除了角色，那么用戶(hù)也就失去了通過(guò)這個(gè)角色所獲得的所有權(quán)限。

3.6 給用戶(hù)賦予角色

角色創(chuàng)建并授權(quán)后，要賦給用戶(hù)并處于激活狀態(tài)才能發(fā)揮作用。

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

查詢(xún)當(dāng)前已激活的角色

SELECT CURRENT_ROLE();

3.7 激活角色

方式1：使用set default role 命令激活角色

SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';

方式2：將activate_all_roles_on_login設(shè)置為ON

SET GLOBAL activate_all_roles_on_login=ON;

這條 SQL 語(yǔ)句的意思是，對(duì)所有角色永久激活。

3.8 撤銷(xiāo)用戶(hù)的角色

REVOKE role FROM user;

3.9 設(shè)置強(qiáng)制角色(mandatory role)

方式1：服務(wù)啟動(dòng)前設(shè)置

[mysqld] 
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'

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

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系統(tǒng)重啟后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系統(tǒng)重啟后失效