簡述構(gòu)成mysql權(quán)限控制步驟

1、mysql的權(quán)限是，從某處來的用戶對某對象的權(quán)限。

成都創(chuàng)新互聯(lián)網(wǎng)站建設提供從項目策劃、軟件開發(fā)，軟件安全維護、網(wǎng)站優(yōu)化(SEO)、網(wǎng)站分析、效果評估等整套的建站服務，主營業(yè)務為成都做網(wǎng)站、成都網(wǎng)站建設，重慶App定制開發(fā)以傳統(tǒng)方式定制建設網(wǎng)站，并提供域名空間備案等一條龍服務，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務。成都創(chuàng)新互聯(lián)深信只要達到每一位用戶的要求，就會得到認可，從而選擇與我們長期合作。這樣，我們也可以走得更遠！

2、mysql的權(quán)限采用白名單策略，指定用戶能做什么，沒有指定的都不能做。

3、權(quán)限校驗分成兩個步驟：

a、能不能連接，檢查從哪里來，用戶名和密碼，常見錯誤 ERROR 1045 (28000): Access denied for user ...

b、能不能執(zhí)行操作，粒度從粗到細，分別為：mysql.user, mysql.db, msql.tables_priv, mysql.columns_priv, mysql.proc_priv。

需要注意的是，這些表各有分工，但是在權(quán)限分配上有一定的重合。

可以這樣理解，mysql 先檢查對大范圍是否有權(quán)限，如果沒有再到小范圍里去檢查。比如：先檢查對這個數(shù)據(jù)庫是否有select權(quán)限，如果有，就允許執(zhí)行。如果沒有，再檢查對表是否有select權(quán)限，一直到最細粒度，也沒有權(quán)限，就拒絕執(zhí)行。舉例來說：要檢查張三能否控制一個團，我只要先檢查張三能否控制一個軍，如果可以，就是有權(quán)限，如果不行，再檢查張三能否控制一個師。因此，粒度控制越細，權(quán)限校驗的步驟越多，性能越差，需要考慮。

4、mysql服務啟動之后，就會把權(quán)限有關(guān)的表的數(shù)據(jù)讀到內(nèi)存中，對權(quán)限做的修改，是否會即時生效？要看情況，手動修改表數(shù)據(jù)，需要 flush privileges

5、創(chuàng)建用戶 create user，修改密碼 set password，注意 alter user只是設置密碼過期，可以登錄，但是不能執(zhí)行任何操作，必須從新設置密碼，刪除用戶 drop user

6、host+user 標示唯一的一個用戶，也就是說都叫張三，從不同地方來的張三是兩個用戶，他們有不同的權(quán)限。

7、那么問題來了，表中有兩條記錄：'root'@'192.168.1.101' 和 'root'@'%', 現(xiàn)在root來登錄，mysql 怎么匹配呢？認為是哪個root呢？

mysql 對用戶進行了排序，先對host排序，再對user排序，小范圍在前面，大范圍在后面，從上往下匹配。

8、權(quán)限授予，grant 權(quán)限 on 對象 to 用戶@哪里來 identified by 密碼

9、收回權(quán)限，revoke 權(quán)限 on 對象 from 用戶@哪里來，注意revoke 必須要與grant 對應，也就是說，只能收回授予的權(quán)限。

10、那么問題來了，我授予張三 select的權(quán)限，現(xiàn)在revoke all privileges 也不能收回張三select的權(quán)限，因為沒有對張三 grant all privileges，怎么解決這個問題？

使用 revoke all privileges，grant option from user

11、權(quán)限級別：從某臺主機來的某個用戶，對某個數(shù)據(jù)庫中某個表的某些列的某部分記錄，是否有權(quán)限。

12、全局：對象是mysql服務的所有數(shù)據(jù)庫，包含服務級的管理權(quán)限，比如showdown

13、數(shù)據(jù)庫：對象是某一個數(shù)據(jù)庫

14、表：對象是數(shù)據(jù)庫中某個表

15、列：對象是表中的某個列，比如：grant select (name) on xxx to xxx

16、程序：對象是存儲過程和方法。

17、information_scheme，數(shù)據(jù)庫和表是存放數(shù)據(jù)的，那么誰來存放 數(shù)據(jù)庫和表這些信息呢？ information_scheme 就是記錄數(shù)據(jù)庫和表的，需要注意的是，infromation_scheme沒有對應的物理文件，它是mysql在內(nèi)存中維護的。

18、權(quán)限設定原則：

a、盡量縮小權(quán)限

b、按業(yè)務，分離用戶，不同的業(yè)務對應不同的用戶

c、避免權(quán)限粒度太細，因為mysql權(quán)限檢查，會影響性能。

19、文件泄密，linux下mysql客戶端執(zhí)行的操作記錄在文件 ~/.mysql_history中，輸出重定向/dev/null

20、密碼丟失怎么辦？

a、mysql啟動，增加選項重置密碼

b、mysql啟動，增加選項不檢查權(quán)限，登陸后修改密碼，退出重啟啟動。

MYSQL 給用戶賦予了 GRANT OPTION 權(quán)限，如何取消？

當權(quán)限1,權(quán)限2

mysql grant 權(quán)限1,權(quán)限2,…權(quán)限n on 名稱.表名稱 to 用戶名@用戶地址 identified by ‘連接口令’;

權(quán)限1,權(quán)限2,…權(quán)限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14個權(quán)限。

當權(quán)限1,權(quán)限2,…權(quán)限n被all privileges或者all代替，表示賦予用戶全部權(quán)限。

當數(shù)據(jù)庫名稱.表名稱被*.*代替，表示賦予用戶操作服務器上所有數(shù)據(jù)庫所有表的權(quán)限。

用戶地址可以是localhost，也可以是ip地址、機器名字、域名。也可以用’%表示從任何地址連接。

‘連接口令’不能為空，否則創(chuàng)建失敗。

mysql中怎樣回收用戶wang在數(shù)據(jù)庫accounts的權(quán)限？

你可以直接刪除wang用戶

進入mysql.user表，delete from user where user='wang'

mysql中不能設置列的權(quán)限

一、用戶管理

1、登錄mysql

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

比如

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

-h參數(shù) 后面接主機名或者主機IP，hostname為主機，hostIP為主機IP。

-P參數(shù) 后面接MySQL服務的端口，通過該參數(shù)連接到指定的端口。MySQL服務的默認端口是3306，

不使用該參數(shù)時自動連接到3306端口，port為連接的端口號。

-u參數(shù) 后面接用戶名，username為用戶名。

-p參數(shù) 會提示輸入密碼。

DatabaseName參數(shù) 指明登錄到哪一個數(shù)據(jù)庫中。如果沒有該參數(shù)，就會直接登錄到MySQL數(shù)據(jù)庫

中，然后可以使用USE命令來選擇數(shù)據(jù)庫。

-e參數(shù) 后面可以直接加SQL語句。登錄MySQL服務器以后即可執(zhí)行這個SQL語句，然后退出MySQL

服務器。

2、創(chuàng)建用戶

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

比如

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

用戶名參數(shù)表示新建用戶的賬戶，由 用戶（User） 和 主機名（Host） 構(gòu)成；

“[ ]”表示可選，也就是說，可以指定用戶登錄時需要密碼驗證，也可以不指定密碼驗證，這樣用戶

可以直接登錄。不過，不指定密碼的方式不安全，不推薦使用。如果指定密碼值，這里需要使用

IDENTIFIED BY指定明文密碼值。

CREATE USER語句可以同時創(chuàng)建多個用戶。

3、更新用戶

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

FLUSH PRIVILEGES;

4、刪除用戶

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

使用DROP USER語句來刪除用戶時，必須用于DROP USER權(quán)限。DROP USER語句的基本語法形式如下

DROP USER user[,user]…;

比如

DROP USER li4 ; # 默認刪除host為%的用戶

DROP USER 'kangshifu'@'localhost';

方式2：使用DELETE方式刪除

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

FLUSH PRIVILEGES;

比如

DELETE FROM mysql.user WHERE Host='localhost' AND User='Emily';

FLUSH PRIVILEGES;

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

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';

6、修改其他用戶密碼

1. 使用ALTER語句來修改普通用戶的密碼 可以使用ALTER USER語句來修改普通用戶的密碼?；菊Z法形 式如下：

ALTER USER user [IDENTIFIED BY '新密碼']

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

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";

7、 MySQL8密碼管理(了解

7.1、密碼過期策略

在MySQL中，數(shù)據(jù)庫管理員可以 手動設置 賬號密碼過期，也可以建立一個 自動 密碼過期策略。 過期策略可以是 全局的 ，也可以為 每個賬號 設置單獨的過期策略

ALTER USER user PASSWORD EXPIRE;

比如

ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE;

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

SET PERSIST default_password_lifetime = 180; # 建立全局策略，設置密碼每隔180天過期

方式②：配置文件my.cnf中進行維護

[mysqld]

default_password_lifetime=180 #建立全局策略，設置密碼每隔180天過期

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

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

#設置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;

7.2、密碼重置策略

手動設置密碼重用：全局

方式1：使用sql

SET PERSIST password_history = 6; #設置不能選擇最近使用過的6個密碼

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

方式2：配置文件

[mysqld]

password_history=6

password_reuse_interval=365

手動設置密碼重用：單獨設置

#不能使用最近5個密碼：

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個密碼，也不能使用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;

二、權(quán)限管理

1、 權(quán)限列表

MySQL到底都有哪些權(quán)限呢？

mysql show privileges;

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

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

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

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

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

3、為每個用戶 設置滿足密碼復雜度的密碼 。

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

3、 授予權(quán)限

給用戶授權(quán)的方式有 2 種，分別是通過把 角色賦予用戶給用戶授權(quán) 和 直接給用戶授權(quán) 。用戶是數(shù)據(jù)庫的 使用者，我們可以通過給用戶授予訪問數(shù)據(jù)庫中資源的權(quán)限，來控制使用者對數(shù)據(jù)庫的訪問，消除安全 隱患。 授權(quán)命令：

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

比如給li4用戶用本地命令行方式，授予atguigudb這個庫下的所有表的插刪改查的權(quán)限

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

比如授予通過網(wǎng)絡方式登錄的joe用戶 ，對所有庫所有表的全部權(quán)限，密碼設為123。注意這里唯獨不包

括grant的權(quán)限

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

我們在開發(fā)應用的時候，經(jīng)常會遇到一種需求，就是要根據(jù)用戶的不同，對數(shù)據(jù)進行橫向和縱向的 分組。 所謂橫向的分組，就是指用戶可以接觸到的數(shù)據(jù)的范圍，比如可以看到哪些表的數(shù)據(jù)；

所謂縱向的分組，就是指用戶對接觸到的數(shù)據(jù)能訪問到什么程度，比如能看、能改，甚至是 刪除。

4、 查看權(quán)限

查看當前用戶權(quán)限

SHOW GRANTS;

# 或

SHOW GRANTS FOR CURRENT_USER;

# 或

SHOW GRANTS FOR CURRENT_USER();

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

SHOW GRANTS FOR 'user'@'主機地址' ;

5、收回權(quán)限

收回權(quán)限就是取消已經(jīng)賦予用戶的某些權(quán)限。收回用戶不必要的權(quán)限可以在一定程度上保證系統(tǒng)的安全 性。MySQL中使用 REVOKE語句 取消用戶的某些權(quán)限。使用REVOKE收回權(quán)限之后，用戶賬戶的記錄將從 db、host、tables_priv和columns_priv表中刪除，但是用戶賬戶記錄仍然在user表中保存（刪除user表中 的賬戶記錄使用DROP USER語句）。 注意：在將用戶賬戶從user表刪除之前，應該收回相應用戶的所有權(quán)限。

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

比如

#收回全庫全表的所有權(quán)限

REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';

#收回mysql庫下的所有表的插刪改查權(quán)限

REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

注意： 須用戶重新登錄后才能生效

三、權(quán)限表

1、user表

user表是MySQL中最重要的一個權(quán)限表， 記錄用戶賬號和權(quán)限信息 ，有49個字段。如下圖：

這些字段可以分成4類，分別是范圍列（或用戶列）、權(quán)限列、安全列和資源控制列

范圍列（或用戶列）

權(quán)限列

安全列

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

資源控制列

資源控制列的字段用來 限制用戶使用的資源 ，包含4個字段，分別為： ①max_questions，用戶每小時允許執(zhí)行的查詢操作次數(shù)； ②max_updates，用戶每小時允許執(zhí)行的更新 操作次數(shù)； ③max_connections，用戶每小時允許執(zhí)行的連接操作次數(shù)； ④max_user_connections，用戶 允許同時建立的連接次數(shù)。 查看字段：

DESC mysql.user;

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

查詢特定字段：

SELECT host,user,authentication_string,select_priv,insert_priv,drop_priv

FROM mysql.user;

2、db表

使用DESCRIBE查看db表的基本結(jié)構(gòu)： DESCRIBE mysql.db;

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

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

3、 tables_priv表和columns_priv表

tables_priv表用來 對表設置操作權(quán)限 ，columns_priv表用來對表的 某一列設置權(quán)限 。tables_priv表和 columns_priv表的結(jié)構(gòu)分別如圖： desc mysql.tables_priv;

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

Host 、 Db 、 User 和 Table_name 四個字段分別表示主機名、數(shù)據(jù)庫名、用戶名和表名。

Grantor表示修改該記錄的用戶。

Timestamp表示修改該記錄的時間。

Table_priv 表示對象的操作權(quán)限。包括Select、Insert、Update、Delete、Create、Drop、Grant、

References、Index和Alter。

Column_priv字段表示對表中的列的操作權(quán)限，包括Select、Insert、Update和References。

desc mysql.columns_priv;

4、 procs_priv表

procs_priv表可以對 存儲過程和存儲函數(shù)設置操作權(quán)限 ，表結(jié)構(gòu)如圖： desc mysql.procs_priv;

四、訪問控制

1、連接核實階段

當用戶試圖連接MySQL服務器時，服務器基于用戶的身份以及用戶是否能提供正確的密碼驗證身份來確 定接受或者拒絕連接。即客戶端用戶會在連接請求中提供用戶名、主機地址、用戶密碼，MySQL服務器 接收到用戶請求后，會使用user表中的host、user和authentication_string這3個字段匹配客戶端提供信 息。 服務器只有在user表記錄的Host和User字段匹配客戶端主機名和用戶名，并且提供正確的密碼時才接受 連接。如果連接核實沒有通過，服務器就完全拒絕訪問；否則，服務器接受連接，然后進入階段2等待 用戶請求。

2、 請求核實階段

一旦建立了連接，服務器就進入了訪問控制的階段2，也就是請求核實階段。對此連接上進來的每個請 求，服務器檢查該請求要執(zhí)行什么操作、是否有足夠的權(quán)限來執(zhí)行它，這正是需要授權(quán)表中的權(quán)限列發(fā) 揮作用的地方。這些權(quán)限可以來自user、db、table_priv和column_priv表。 確認權(quán)限時，MySQL首先 檢查user表 ，如果指定的權(quán)限沒有在user表中被授予，那么MySQL就會繼續(xù) 檢 查db表 ，db表是下一安全層級，其中的權(quán)限限定于數(shù)據(jù)庫層級，在該層級的SELECT權(quán)限允許用戶查看指 定數(shù)據(jù)庫的所有表中的數(shù)據(jù)；如果在該層級沒有找到限定的權(quán)限，則MySQL繼續(xù) 檢查tables_priv表 以 及 columns_priv表 ，如果所有權(quán)限表都檢查完畢，但還是沒有找到允許的權(quán)限操作，MySQL將 返回錯 誤信息 ，用戶請求的操作不能執(zhí)行，操作失敗。

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

五、角色管理

1、創(chuàng)建角色

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

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

練習：我們現(xiàn)在需要創(chuàng)建一個經(jīng)理的角色，就可以用下面的代碼：

CREATE ROLE 'manager'@'localhost';

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

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

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

上述語句中privileges代表權(quán)限的名稱，多個權(quán)限以逗號隔開?？墒褂肧HOW語句查詢權(quán)限名稱，圖11-43 列出了部分權(quán)限列表。

SHOW PRIVILEGES\G;

練習1：我們現(xiàn)在想給經(jīng)理角色授予商品信息表、盤點表和應付賬款表的只讀權(quán)限，就可以用下面的代碼 來實現(xiàn)：

GRANT SELECT ON demo.settlement TO 'manager';

GRANT SELECT ON demo.goodsmaster TO 'manager';

GRANT SELECT ON demo.invcount TO 'manager';

3、查看角色權(quán)限

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

只要你創(chuàng)建了一個角色，系統(tǒng)就會自動給你一個“ USAGE ”權(quán)限，意思是 連接登錄數(shù)據(jù)庫的權(quán)限 。代碼的 最后三行代表了我們給角色“manager”賦予的權(quán)限，也就是對商品信息表、盤點表和應付賬款表的只讀權(quán) 限。 結(jié)果顯示，庫管角色擁有商品信息表的只讀權(quán)限和盤點表的增刪改查權(quán)限。

4、回收角色權(quán)限

角色授權(quán)后，可以對角色的權(quán)限進行維護，對權(quán)限進行添加或撤銷。添加權(quán)限使用GRANT語句，與角色 授權(quán)相同。撤銷角色或角色權(quán)限使用REVOKE語句。 修改了角色的權(quán)限，會影響擁有該角色的賬戶的權(quán)限。 撤銷角色權(quán)限的SQL語法如下：

REVOKE privileges ON tablename FROM 'rolename';

練習：撤銷school_write角色的權(quán)限。 （

（1）使用如下語句撤銷school_write角色的權(quán)限。

REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write';

（2）撤銷后使用SHOW語句查看school_write對應的權(quán)限，語句如下。

SHOW GRANTS FOR 'school_write';

5、刪除角色

當我們需要對業(yè)務重新整合的時候，可能就需要對之前創(chuàng)建的角色進行清理，刪除一些不會再使用的角 色。刪除角色的操作很簡單，你只要掌握語法結(jié)構(gòu)就行了。

DROP ROLE role [,role2]...

注意， 如果你刪除了角色，那么用戶也就失去了通過這個角色所獲得的所有權(quán)限 。 練習：執(zhí)行如下SQL刪除角色school_read。

DROP ROLE 'school_read';

6、給用戶賦予角色

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

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

在上述語句中，role代表角色，user代表用戶。可將多個角色同時賦予多個用戶，用逗號隔開即可。 練習：給kangshifu用戶添加角色school_read權(quán)限。 （1）使用GRANT語句給kangshifu添加school_read權(quán) 限，SQL語句如下。

GRANT 'school_read' TO 'kangshifu'@'localhost';

（2）添加完成后使用SHOW語句查看是否添加成功，SQL語句如下。

SHOW GRANTS FOR 'kangshifu'@'localhost';

（3）使用kangshifu用戶登錄，然后查詢當前角色，如果角色未激活，結(jié)果將顯示NONE。SQL語句如 下。

SELECT CURRENT_ROLE();

7、激活角色

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

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

使用 SET DEFAULT ROLE 為下面4個用戶默認激活所有已擁有的角色如下：

SET DEFAULT ROLE ALL TO

'dev1'@'localhost',

'read_user1'@'localhost',

'read_user2'@'localhost',

'rw_user1'@'localhost';

方式2：將activate_all_roles_on_login設置為ON

show variables like 'activate_all_roles_on_login';

SET GLOBAL activate_all_roles_on_login=ON;

這條 SQL 語句的意思是，對 所有角色永久激活 。運行這條語句之后，用戶才真正擁有了賦予角色的所有 權(quán)限。

8、撤銷用戶角色

REVOKE role FROM user;

練習：撤銷kangshifu用戶的school_read角色。 （1）撤銷的SQL語句如下

REVOKE 'school_read' FROM 'kangshifu'@'localhost';

（2）撤銷后，執(zhí)行如下查詢語句，查看kangshifu用戶的角色信息

SHOW GRANTS FOR 'kangshifu'@'localhost';

9、設置強制角色

設置強制角色(mandatory role)

方式1：服務啟動前設置

[mysqld]

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

方式2：運行時設置

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