前言

相信對于絕大多數(shù)開發(fā)人員都接觸過數(shù)據(jù)庫，當(dāng)今最常用的關(guān)系型數(shù)據(jù)庫有Mysql、Oracle、PostgreSql、SQLserver，本文重點(diǎn)講解Mysql用戶權(quán)限的分配、管理。用戶權(quán)限在實(shí)際開發(fā)中有什么用途呢？總結(jié)作用如下：

一、可以根據(jù)登錄用戶限制用戶訪問資源(庫、表)
二、可以根據(jù)登錄用戶限制用戶的操作權(quán)限(能對哪些庫、表執(zhí)行增刪改查操作)
三、可以指定用戶登錄IP或者域名
四、可以限制用戶權(quán)限分配

一、Mysql權(quán)限級別分析

Mysql權(quán)限級別分為了五個(gè)層級，并且每個(gè)級別的權(quán)限都對應(yīng)著不同的表，這些表都存在于mysql庫下，在Mysql官方文檔中有詳細(xì)介紹權(quán)限的概念MYSQL官方中文文檔，以下將簡單講述這五個(gè)級別的作用和范圍。

（1）全局級別

全局權(quán)限適用于一個(gè)給定服務(wù)器中的所有數(shù)據(jù)庫。這些權(quán)限存儲(chǔ)在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*用于授予和撤銷全局權(quán)限。

在mysql數(shù)據(jù)庫里，存在一張user表，mysql中所有的用戶都是存放在user表中，user表的字段可以分為四大類(用戶列、權(quán)限列、安全例、資源控制列)

（1.1） USER表的組成結(jié)構(gòu)

通過查看user表結(jié)構(gòu)可以看出，user表保存了用戶的登錄信息和權(quán)限，這個(gè)權(quán)限是全局性的，針對的是全部數(shù)據(jù)庫資源，換句話說，只要user表里的某個(gè)權(quán)限是Y(Y表示YES,N表NO)，那么這個(gè)權(quán)限可以作用于全部數(shù)據(jù)庫資源。

（1.1.1） 用戶列

user表中的用戶列包括字段Host、User、Password:

Host: 登錄的主機(jī)名，表示允許用戶從哪臺機(jī)器登錄接連到Mysql服務(wù)器?？梢允褂肐P地址或者域名，%表示允許從任何主機(jī)連接到Mysql服務(wù)器。
User:登錄的用戶名
Password: 用戶登錄數(shù)據(jù)庫的密碼，當(dāng)該字段為空時(shí)，表示該用戶賬號登錄不需要密碼

（1.1.2） 權(quán)限列

user表中的權(quán)限列是以_priv結(jié)尾的字段，這些字段默認(rèn)為N，N表示該權(quán)限不能用到所有數(shù)據(jù)庫上，Y則與N的效果相反。以下是權(quán)限列各個(gè)字段的說明:

 （1.1.3） 安全列

user表的安全列有4個(gè)字段：ssl_type、ssl_cipher、x509_issuer、x509_subject

ssl用于加密；
x509標(biāo)準(zhǔn)可以用來標(biāo)識用戶。普通的發(fā)行版都沒有加密功能。可以使用SHOW VARIABLES LIKE 'have_openssl’語句來查看是否具有ssl功能。如果取值為DISABLED，那么則沒有ssl加密功能。

（1.1.4） 資源控制列

user表的4個(gè)資源控制列是：max_questions、max_updates、max_connections、max_user_connections

max_questions：每小時(shí)可以允許執(zhí)行多少次查詢；
max_updates：每小時(shí)可以允許執(zhí)行多少次更新；
max_connections：每小時(shí)可以建立多少連接；
max_user_connections：單個(gè)用戶可以同時(shí)具有的連接數(shù)。

默認(rèn)值為0，表示無限制。

（2）數(shù)據(jù)庫層級

數(shù)據(jù)庫權(quán)限適用于一個(gè)給定數(shù)據(jù)庫中的所有目標(biāo)。這些權(quán)限存儲(chǔ)在mysql.db和mysql.host表中。GRANT ALL ON
db_name.*和REVOKE ALL ON db_name.*用于授予和撤銷數(shù)據(jù)庫權(quán)限。

剛剛我們已經(jīng)討論了全局級別的權(quán)限配置，對user表進(jìn)行了解析，但是user表上的權(quán)限都是針對與所有數(shù)據(jù)庫的，如果我們想將權(quán)限力度劃分的更細(xì)，如果我想讓某個(gè)數(shù)據(jù)庫用戶只能查看某個(gè)數(shù)據(jù)庫里的表數(shù)據(jù)，那么此時(shí)就需要用到數(shù)據(jù)庫層級的權(quán)限控制，主要分析mysql庫里的db表。

db表里的權(quán)限列與user表里的含義差不多一致，只是db表的權(quán)限只是針對與某個(gè)數(shù)據(jù)庫，而非全局?jǐn)?shù)據(jù)庫。其中DB字段存儲(chǔ)的是數(shù)據(jù)庫名稱。

（3）表層級

表權(quán)限適用于一個(gè)給定表中的所有列。這些權(quán)限存儲(chǔ)在mysql.talbes_priv表中。GRANT ALL ON
db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表權(quán)限

如果你清楚了全局級別的權(quán)限和數(shù)據(jù)庫層的權(quán)限，那么表層級的權(quán)限就相對好理解了，表層級的權(quán)限是用于控制用戶對某個(gè)數(shù)據(jù)庫里的某個(gè)表是否有權(quán)限進(jìn)行操作。比如你想指定某個(gè)用戶只能操作指定數(shù)據(jù)庫里的某張表，此時(shí)就需要用到表層級的權(quán)限來控制。

tables_priv表的字段相比較與user和db表要少的多，因?yàn)樗刂频臋?quán)限更細(xì)，它的結(jié)構(gòu)如下：

（1.3） tables_priv表的組成結(jié)構(gòu)

（4）列層級

列權(quán)限適用于一個(gè)給定表中的單一列。這些權(quán)限存儲(chǔ)在mysql.columns_priv表中。當(dāng)使用REVOKE時(shí)，您必須指定與被授權(quán)列相同的列。

列層級的使用還是比較少，所以這里就不再過多闡述。

（5）子程序?qū)蛹?/h3>

REATE ROUTINE, ALTER ROUTINE,EXECUTE和GRANT權(quán)限適用于已存儲(chǔ)的子程序。這些權(quán)限可以被授予為全局層級和數(shù)據(jù)庫層級。而且，除了CREATE ROUTINE外，這些權(quán)限可以被授予為子程序?qū)蛹?，并存?chǔ)在mysql.procs_priv表中。

（6）權(quán)限分配順序

MySQL權(quán)限分配順序是按照層級由大到小進(jìn)行解析的，也就是先查看
user表（所有數(shù)據(jù)庫）–>db表(某個(gè)數(shù)據(jù)庫)->table_priv(某個(gè)表)->columns_pirv(某列)進(jìn)行查找。
如果發(fā)現(xiàn)user表中某個(gè)權(quán)限是Y，那么就不會(huì)繼續(xù)往下找，如果User表某個(gè)權(quán)限是N，那么就去找db表，依次往下找。

二、實(shí)戰(zhàn)用戶權(quán)限分配

（1）新建用戶

創(chuàng)建用戶常用的命令是CREATE USER，不過使用CREATE USER語句創(chuàng)建用戶時(shí)，需要操作員要有相應(yīng)的權(quán)限，也就是user表中的字段Create_priv為Y才能創(chuàng)建用戶。比如創(chuàng)建一個(gè)名為admin的用戶，并且登錄密碼為123456:

CREATE USER ‘admin’@‘%’ IDENTIFIED BY ‘123456’
一般創(chuàng)建用戶的格式為：CREATE USER ‘用戶名’@‘登錄IP或者域名’ IDENTIFIED BY ‘登錄密碼’。
其中需要注意的是登錄IP或者域名，很多時(shí)候會(huì)將登錄IP設(shè)置為**%，%**代表所有主機(jī)，表示該用戶可以在任何機(jī)器上進(jìn)行登錄。192.168.101.%表示允許192.168.101這個(gè)網(wǎng)段的主機(jī)進(jìn)行登錄。

當(dāng)你只是完上面的創(chuàng)建admin用戶命令后，你可以打開mysql庫下的user表，你會(huì)發(fā)現(xiàn)里面多了一條admin的數(shù)據(jù)，但是所有的權(quán)限都是N，這表明該admin用戶沒有任何權(quán)限，不能查看操作任何數(shù)據(jù)庫資源。

此時(shí)你可以嘗試使用admin用戶登錄你現(xiàn)有的數(shù)據(jù)庫，你會(huì)發(fā)現(xiàn)admin賬號只能查看information_schema這個(gè)數(shù)據(jù)庫，那是因?yàn)槲覀冊趧?chuàng)建admin賬號時(shí)，并沒有分配任何權(quán)限。

如果你想在創(chuàng)建賬號的時(shí)候就分配給該賬號一定的權(quán)限，那么你可以使用 : grant all privileges on *.* to 'admin'@'%' identified by '123456' with grant option; 該語句表示創(chuàng)建admin賬號允許從任何主機(jī)登錄并且擁有數(shù)據(jù)庫全部的權(quán)限，可以將自己的權(quán)限授予給別人。我們將這條語句拆分:

創(chuàng)建賬號時(shí)并賦予全部權(quán)限: 
grant all privileges on *.* to '用戶名'@'登錄IP或者域名' identified by '登錄密碼' with grant option;
all privileges: 表示授權(quán)用戶全部權(quán)限,當(dāng)然你也可以指定賦予具體權(quán)限，比如SELECT、UPDATE、CREATE、DROP等。
on:表示這些權(quán)限可以作用于哪些數(shù)據(jù)庫和哪些表，*.*表示作用于所有數(shù)據(jù)庫和所有表，格式為 數(shù)據(jù)庫名.表名。比如有一個(gè)test庫，想讓admin擁有查看test庫所有表的權(quán)限，那么可以這樣寫：grant SELECT on test.* to 'admin'@'%' identified by '登錄密碼';
to：將權(quán)限授予哪個(gè)用戶。格式：'用戶名'@'登錄IP或域名'。
IDENTIFIED by：指定用戶的登錄密碼。
with grant option：表示允許用戶將自己的權(quán)限授權(quán)給其它用戶。

但請注意可能由于MySQL的數(shù)據(jù)庫版本問題，有些版本不允許在創(chuàng)建用戶時(shí)就給予權(quán)限，那么需要將創(chuàng)建和權(quán)限賦予分為兩步:

create user 'admin'@'%' identified by '123456' -- 創(chuàng)建用戶
GRANT all privileges ON  *.* TO 'admin'@'%' -- 將所有權(quán)限賦予給admin

一般來說我們不會(huì)輕易創(chuàng)建一個(gè)用戶給他賦予所有權(quán)限，也就是 grant all privileges，那么grant后面可以接哪些權(quán)限呢？這里簡單整理了一部分權(quán)限以及其含義：

以上表格舉例了部分權(quán)限以及其含義，如果你想給一個(gè)賬號賦予多個(gè)權(quán)限，可以用英文逗號分開，比如Grant SELECT,UPDATE,DROP等，如果想連接更多信息，歡迎點(diǎn)擊Mysql賬號管理查看

（2）刪除用戶

剛剛我們已經(jīng)創(chuàng)建了一個(gè)admin賬號并且沒有為其設(shè)置任何權(quán)限，此時(shí)我們可以使用 DROP USER 命令刪除某個(gè)用戶，例如我們要?jiǎng)h除之前創(chuàng)建的admin用戶，那么我們可以使用命令:

DROP USER 'admin'@'%' -- 刪除admin用戶，用戶名(User)和主機(jī)名(Host)構(gòu)成
FLUSH PRIVILEGES?; -- 刷新權(quán)限

此時(shí)user表中的admin用戶就刪除了。

（3）禁用用戶、啟用用戶

有時(shí)候我們只想禁用某個(gè)用戶的登錄，并不想將該用戶進(jìn)行刪除，那么可以使用MySQL的禁用功能。在演示之前我們現(xiàn)在創(chuàng)建一個(gè)admin用戶，該用戶擁有訪問數(shù)據(jù)庫(db_cztl_dev)的權(quán)限，請注意db_cztl_dev是在我MySQL中存在的一個(gè)數(shù)據(jù)庫，您可以使用您自己的數(shù)據(jù)庫。執(zhí)行命令:

create user 'admin'@'%' identified by '123456' -- 創(chuàng)建用戶
GRANT SELECT ON `db_cztl_dev`.* TO 'admin'@'%' -- 將db_cztl_dev數(shù)據(jù)庫的讀權(quán)限賦予admin

此時(shí)user表會(huì)有admin賬號的信息，我們可以看到user表里的admin的select_priv是為N，因?yàn)?strong>admin現(xiàn)在的權(quán)限只是能訪問某一個(gè)數(shù)據(jù)庫，而非全部數(shù)據(jù)庫，所以user表里的select_pirv是為N，但是db表(數(shù)據(jù)庫層級)會(huì)在db_cztl_dev那條數(shù)據(jù)的select_priv為N。

表示admin可以訪問數(shù)據(jù)庫db_cztl_dev，擁有該數(shù)據(jù)庫下的所有SELECT權(quán)限，讓我們用admin賬號登錄，就可以看到db_cztl_dev數(shù)據(jù)庫。

如果我們要禁用admin賬號，可以輸入命令:

ALTER USER 'admin'@'%' ACCOUNT lock; -- 禁用admin賬號
FLUSH PRIVILEGES; -- 刷新權(quán)限

此時(shí)重新使用admin用戶登錄，就可以提示以下信息:

解除admin禁用可以使用命令:

ALTER USER 'admin'@'%' ACCOUNT UNLOCK; --解除admin用戶
FLUSH PRIVILEGES; -- 刷新權(quán)限

（4）重命名用戶名

修改用戶名的格式如下：

rename user '用戶名'@'IP或者域名' to '新用戶名'@'IP或者域名';

比如我們要將admin用戶重名為king，那么可以這樣寫：

rename user 'admin'@'%' to 'king'@'%';

（5）修改用戶密碼

set password for '用戶名'@'IP或者域名'=password('新密碼')

（6）收回權(quán)限(revoke)

根據(jù)上面幾小節(jié)我們指定如何給一個(gè)用戶賦予權(quán)限可以用Grant命令，相對應(yīng)的撤銷用戶的某個(gè)權(quán)限可以使用REVOKE。

（6.1）查看數(shù)據(jù)庫中所有用戶信息

我們知道用戶的基本信息都存放在user表中，那么查看所有用戶信息可以使用以下語句:

SELECT user,host FROM mysql.`user`;

（6.2）查看用戶擁有的所有權(quán)限

查看某個(gè)用戶的權(quán)限可以使用 show grants for 用戶名，比如我們要查看admin用戶的權(quán)限有哪些:

SHOW GRANTS FOR 'admin'@'%'

可以看到admin用戶擁有查看數(shù)據(jù)庫db_cztl_dev下所有資源的權(quán)限。

（6.3）使用REVOKE撤銷權(quán)限

由4.2小節(jié)我們可以看到admin擁有訪問db_cztl_dev數(shù)據(jù)庫的權(quán)限，假如我們此時(shí)想撤銷admin的權(quán)限，可以這樣寫:

REVOKE SELECT ON db_cztl_dev.* FROM 'admin'@'%'; -- 撤銷admin在數(shù)據(jù)db_cztl_dev的權(quán)限

此時(shí)我們再使用SHOW GRANTS FOR 'admin'@'%'查看admin權(quán)限，可以看到admin訪問db_cztl_dev數(shù)據(jù)庫的權(quán)限已被撤銷。

（7）修改用戶權(quán)限(Grant 權(quán)限類型 ON 數(shù)據(jù)庫.表 TO 用戶)

通過6.3我們已經(jīng)撤銷了admin查看db_cztl_dev的權(quán)限，如果此時(shí)我們想賦予admin用戶可以訪問并且修改db_cztl_dev庫下cztl_line表的權(quán)限，可以這樣做:

GRANT SELECT,UPDATE ON db_cztl_dev.cztl_line TO 'admin'@'%';
FLUSH PRIVILEGES;

重新使用admin賬號登錄數(shù)據(jù)庫，就可以訪問和修改db_cztl_dev庫下的cztl_line表。

如果想讓admin用戶能訪問整個(gè)db_cztl_dev庫下所有資源，可以追加權(quán)限：

GRANT SELECT ON `db_cztl_dev`.* TO 'admin'@'%'

重新admin登錄就可以查到所有表

（8） 設(shè)置MySQL用戶密碼過期策略

設(shè)置系統(tǒng)參數(shù)default_password_lifetime作用于所有的用戶賬戶，可以通過show variables like ‘default_password_lifetime’;查看密碼過期策略，0表示用不過期，可以通過 set global default_password_lifetime=180;設(shè)置密碼過期時(shí)間為180天。

（8.1）手動(dòng)強(qiáng)制某個(gè)用戶密碼過期

ALTER USER '用戶名'@'IP或者域名' PASSWORD EXPIRE;

假如我們要將admin賬號強(qiáng)制密碼過期，可以這樣做:

ALTER USER 'admin'@'%' PASSWORD EXPIRE; --強(qiáng)制admin用戶密碼過期

重啟使用admin登錄時(shí)，會(huì)彈出重置密碼界面。

總結(jié)

到此這篇關(guān)于Mysql用戶權(quán)限分配的文章就介紹到這了,更多相關(guān)Mysql用戶權(quán)限分配內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！