前言

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

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

一、Mysql權限級別分析

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

（1）全局級別

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

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

（1.1） USER表的組成結構

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

（1.1.1） 用戶列

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

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

（1.1.2） 權限列

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

 （1.1.3） 安全列

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

ssl用于加密；
x509標準可以用來標識用戶。普通的發(fā)行版都沒有加密功能?？梢允褂肧HOW VARIABLES LIKE 'have_openssl’語句來查看是否具有ssl功能。如果取值為DISABLED，那么則沒有ssl加密功能。

（1.1.4） 資源控制列

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

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

默認值為0，表示無限制。

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

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

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

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

（3）表層級

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

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

tables_priv表的字段相比較與user和db表要少的多，因為它控制的權限更細，它的結構如下：

（1.3） tables_priv表的組成結構

（4）列層級

列權限適用于一個給定表中的單一列。這些權限存儲在mysql.columns_priv表中。當使用REVOKE時，您必須指定與被授權列相同的列。

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

（5）子程序層級

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

（6）權限分配順序

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

二、實戰(zhàn)用戶權限分配

（1）新建用戶

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

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

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

此時你可以嘗試使用admin用戶登錄你現(xiàn)有的數(shù)據(jù)庫，你會發(fā)現(xiàn)admin賬號只能查看information_schema這個數(shù)據(jù)庫，那是因為我們在創(chuàng)建admin賬號時，并沒有分配任何權限。

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

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

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

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

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

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

（2）刪除用戶

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

DROP USER 'admin'@'%' -- 刪除admin用戶，用戶名(User)和主機名(Host)構成
FLUSH PRIVILEGES?; -- 刷新權限

此時user表中的admin用戶就刪除了。

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

有時候我們只想禁用某個用戶的登錄，并不想將該用戶進行刪除，那么可以使用MySQL的禁用功能。在演示之前我們現(xiàn)在創(chuàng)建一個admin用戶，該用戶擁有訪問數(shù)據(jù)庫(db_cztl_dev)的權限，請注意db_cztl_dev是在我MySQL中存在的一個數(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ù)庫的讀權限賦予admin

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

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

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

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

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

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

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

（4）重命名用戶名

修改用戶名的格式如下：

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

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

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

（5）修改用戶密碼

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

（6）收回權限(revoke)

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

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

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

SELECT user,host FROM mysql.`user`;

（6.2）查看用戶擁有的所有權限

查看某個用戶的權限可以使用 show grants for 用戶名，比如我們要查看admin用戶的權限有哪些:

SHOW GRANTS FOR 'admin'@'%'

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

（6.3）使用REVOKE撤銷權限

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

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

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

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

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

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

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

如果想讓admin用戶能訪問整個db_cztl_dev庫下所有資源，可以追加權限：

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

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

（8） 設置MySQL用戶密碼過期策略

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

（8.1）手動強制某個用戶密碼過期

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

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

ALTER USER 'admin'@'%' PASSWORD EXPIRE; --強制admin用戶密碼過期

重啟使用admin登錄時，會彈出重置密碼界面。

總結

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

最新評論