MySQL 權(quán)限介紹
相信對(duì)于絕大多數(shù)開發(fā)人員都接觸過數(shù)據(jù)庫(kù)，當(dāng)今最常用的關(guān)系型數(shù)據(jù)庫(kù)有Mysql、Oracle、PostgreSql、SQLserver，本文重點(diǎn)講解Mysql用戶權(quán)限的分配、管理。用戶權(quán)限在實(shí)際開發(fā)中有什么用途呢？總結(jié)作用如下：
一、可以根據(jù)登錄用戶限制用戶訪問資源(庫(kù)、表)
二、可以根據(jù)登錄用戶限制用戶的操作權(quán)限(能對(duì)哪些庫(kù)、表執(zhí)行增刪改查操作)
三、可以指定用戶登錄IP或者域名
四、可以限制用戶權(quán)限分配
一、Mysql權(quán)限級(jí)別分析
Mysql權(quán)限級(jí)別分為了五個(gè)層級(jí)，并且每個(gè)級(jí)別的權(quán)限都對(duì)應(yīng)著不同的表，這些表都存在于mysql庫(kù)下，在Mysql官方文檔中有詳細(xì)介紹權(quán)限的概念MYSQL官方中文文檔，以下將簡(jiǎn)單講述這五個(gè)級(jí)別的作用和范圍。
（1）全局級(jí)別
全局權(quán)限適用于一個(gè)給定服務(wù)器中的所有數(shù)據(jù)庫(kù)。這些權(quán)限存儲(chǔ)在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*用于授予和撤銷全局權(quán)限。
在mysql數(shù)據(jù)庫(kù)里，存在一張user表，mysql中所有的用戶都是存放在user表中，user表的字段可以分為四大類(用戶列、權(quán)限列、安全例、資源控制列)
（1.1） USER表的組成結(jié)構(gòu)
通過查看user表結(jié)構(gòu)可以看出，user表保存了用戶的登錄信息和權(quán)限，這個(gè)權(quán)限是全局性的，針對(duì)的是全部數(shù)據(jù)庫(kù)資源，換句話說，只要user表里的某個(gè)權(quán)限是Y(Y表示YES,N表NO)，那么這個(gè)權(quán)限可以作用于全部數(shù)據(jù)庫(kù)資源。
（1.1.1） 用戶列
user表中的用戶列包括字段Host、User、Password:
Host: 登錄的主機(jī)名，表示允許用戶從哪臺(tái)機(jī)器登錄接連到Mysql服務(wù)器?？梢允褂肐P地址或者域名，%表示允許從任何主機(jī)連接到Mysql服務(wù)器。
User:登錄的用戶名
Password: 用戶登錄數(shù)據(jù)庫(kù)的密碼，當(dāng)該字段為空時(shí)，表示該用戶賬號(hào)登錄不需要密碼
（1.1.2） 權(quán)限列
user表中的權(quán)限列是以_priv結(jié)尾的字段，這些字段默認(rèn)為N，N表示該權(quán)限不能用到所有數(shù)據(jù)庫(kù)上，Y則與N的效果相反。以下是權(quán)限列各個(gè)字段的說明:
| 列名 | 說明 |
|---|---|
| Select_priv | 是否擁有SELECT權(quán)限命令查詢數(shù)據(jù) |
| Insert_priv | 是否擁有INSERT權(quán)限命令插入數(shù)據(jù) |
| Update_priv | 是否擁有UPDATE權(quán)限命令修改數(shù)據(jù) |
| Delete_priv | 是否擁有DELETE權(quán)限命令刪除數(shù)據(jù) |
| Create_priv | 是否擁有權(quán)限創(chuàng)建新的數(shù)據(jù)庫(kù)和表 |
| Drop_priv | 是否擁有權(quán)限刪除現(xiàn)有數(shù)據(jù)庫(kù)和表 |
| Reload_priv | 是否擁有權(quán)限執(zhí)行刷新和重新加載MySQL所用各種內(nèi)部緩存的特定命令，包括日志、權(quán)限、主機(jī)、查詢和表 |
| Shutdown_priv | 是否擁有權(quán)限關(guān)閉MySQL服務(wù)器 |
| Process_priv | 是否擁有權(quán)限通過SHOW PROCESSLIST命令查看其他用戶的進(jìn)程 |
| File_priv | 是否擁有權(quán)限執(zhí)行SELECT INTO OUTFILE和LOAD DATA INFILE命令 |
| Grant_priv | 是否擁有權(quán)限將自己已授權(quán)的權(quán)限授權(quán)給其他用戶 |
| Index_priv | 是否擁有權(quán)限創(chuàng)建和刪除表索引 |
| Alter_priv | 是否擁有權(quán)限重命名和修改表結(jié)構(gòu) |
| Show_db_priv | 是否擁有權(quán)限查看服務(wù)器上所有數(shù)據(jù)庫(kù)的名字，包括用戶擁有足夠訪問權(quán)限的數(shù)據(jù)庫(kù) |
| Super_priv | 是否擁有權(quán)限執(zhí)行某些強(qiáng)大的管理功能，例如通過KILL命令刪除用戶進(jìn)程，使用SET GLOBAL修改全局MySQL變量，執(zhí)行關(guān)于復(fù)制和日志的各種命令 |
| Create_tmp_table_priv | 是否擁有權(quán)限創(chuàng)建臨時(shí)表 |
| Lock_tables_priv | 是否擁有權(quán)限使用LOCK TABLES命令阻止對(duì)表的訪問/修改 |
| Repl_slave_priv | 是否擁有權(quán)限讀取用于維護(hù)復(fù)制數(shù)據(jù)庫(kù)環(huán)境的二進(jìn)制日志文件。此用戶位于主系統(tǒng)中，有利于主機(jī)和客戶機(jī)之間的通信 |
| Create_view_priv | 是否擁有權(quán)限創(chuàng)建視圖 |
| Show_view_priv | 是否擁有權(quán)限查看視圖或了解視圖如何執(zhí)行 |
| Create_routine_priv | 是否擁有權(quán)限更改或放棄存儲(chǔ)過程和函數(shù) |
| Alter_routine_priv | 是否擁有權(quán)限修改或刪除存儲(chǔ)函數(shù)及函數(shù) |
| Create_user_priv | 是否擁有權(quán)限執(zhí)行CREATE USER命令，這個(gè)命令用于創(chuàng)建新的MySQL賬戶 |
| Event_priv | 是否擁有權(quán)限創(chuàng)建、修改和刪除事件 |
| Trigger_priv | 是否擁有權(quán)限創(chuàng)建和刪除觸發(fā)器 |
（1.1.3） 安全列
user表的安全列有4個(gè)字段：ssl_type、ssl_cipher、x509_issuer、x509_subject
ssl用于加密；
x509標(biāo)準(zhǔn)可以用來標(biāo)識(shí)用戶。普通的發(fā)行版都沒有加密功能?？梢允褂肧HOW VARIABLES LIKE 'have_openssl’語(yǔ)句來查看是否具有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：?jiǎn)蝹€(gè)用戶可以同時(shí)具有的連接數(shù)。
默認(rèn)值為0，表示無限制。
（2）數(shù)據(jù)庫(kù)層級(jí)
數(shù)據(jù)庫(kù)權(quán)限適用于一個(gè)給定數(shù)據(jù)庫(kù)中的所有目標(biāo)。這些權(quán)限存儲(chǔ)在mysql.db和mysql.host表中。GRANT ALL ON
db_name.*和REVOKE ALL ON db_name.*用于授予和撤銷數(shù)據(jù)庫(kù)權(quán)限。
剛剛我們已經(jīng)討論了全局級(jí)別的權(quán)限配置，對(duì)user表進(jìn)行了解析，但是user表上的權(quán)限都是針對(duì)與所有數(shù)據(jù)庫(kù)的，如果我們想將權(quán)限力度劃分的更細(xì)，如果我想讓某個(gè)數(shù)據(jù)庫(kù)用戶只能查看某個(gè)數(shù)據(jù)庫(kù)里的表數(shù)據(jù)，那么此時(shí)就需要用到數(shù)據(jù)庫(kù)層級(jí)的權(quán)限控制，主要分析mysql庫(kù)里的db表。
db表里的權(quán)限列與user表里的含義差不多一致，只是db表的權(quán)限只是針對(duì)與某個(gè)數(shù)據(jù)庫(kù)，而非全局?jǐn)?shù)據(jù)庫(kù)。其中DB字段存儲(chǔ)的是數(shù)據(jù)庫(kù)名稱。
（3）表層級(jí)
表權(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)限
如果你清楚了全局級(jí)別的權(quán)限和數(shù)據(jù)庫(kù)層的權(quán)限，那么表層級(jí)的權(quán)限就相對(duì)好理解了，表層級(jí)的權(quán)限是用于控制用戶對(duì)某個(gè)數(shù)據(jù)庫(kù)里的某個(gè)表是否有權(quán)限進(jìn)行操作。比如你想指定某個(gè)用戶只能操作指定數(shù)據(jù)庫(kù)里的某張表，此時(shí)就需要用到表層級(jí)的權(quán)限來控制。
tables_priv表的字段相比較與user和db表要少的多，因?yàn)樗刂频臋?quán)限更細(xì)，它的結(jié)構(gòu)如下：
（1.3） tables_priv表的組成結(jié)構(gòu)
| 列名 | 說明 |
|---|---|
| Host | 用戶登錄的主機(jī)，可以是IP或者域名 |
| Db | 數(shù)據(jù)庫(kù)名稱 |
| User | 登錄用戶名 |
| Talbe_name | 表名稱 |
| Grantor | 權(quán)限的設(shè)置者 |
| Table_priv | 對(duì)表進(jìn)行操作的權(quán)限，對(duì)表的操作權(quán)限有：Select、Insert、Update、Delete、Create、Drop、Grant、Index、Alter、Create View、Show View以及Trigger |
| Column_priv | 對(duì)表中的數(shù)據(jù)列進(jìn)行操作的權(quán)限 Insert、Update、References |
（4）列層級(jí)
列權(quán)限適用于一個(gè)給定表中的單一列。這些權(quán)限存儲(chǔ)在mysql.columns_priv表中。當(dāng)使用REVOKE時(shí)，您必須指定與被授權(quán)列相同的列。
列層級(jí)的使用還是比較少，所以這里就不再過多闡述。
（5）子程序?qū)蛹?jí)
REATE ROUTINE, ALTER ROUTINE,EXECUTE和GRANT權(quán)限適用于已存儲(chǔ)的子程序。這些權(quán)限可以被授予為全局層級(jí)和數(shù)據(jù)庫(kù)層級(jí)。而且，除了CREATE ROUTINE外，這些權(quán)限可以被授予為子程序?qū)蛹?jí)，并存儲(chǔ)在mysql.procs_priv表中。
（6）權(quán)限分配順序
MySQL權(quán)限分配順序是按照層級(jí)由大到小進(jìn)行解析的，也就是先查看
user表（所有數(shù)據(jù)庫(kù)）–>db表(某個(gè)數(shù)據(jù)庫(kù))->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語(yǔ)句創(chuàng)建用戶時(shí)，需要操作員要有相應(yīng)的權(quán)限，也就是user表中的字段Create_priv為Y才能創(chuàng)建用戶。比如創(chuàng)建一個(gè)名為admin的用戶，并且登錄密碼為123456:
CREATE USER ‘a(chǎn)dmin’@‘%’ 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庫(kù)下的user表，你會(huì)發(fā)現(xiàn)里面多了一條admin的數(shù)據(jù)，但是所有的權(quán)限都是N，這表明該admin用戶沒有任何權(quán)限，不能查看操作任何數(shù)據(jù)庫(kù)資源。
此時(shí)你可以嘗試使用admin用戶登錄你現(xiàn)有的數(shù)據(jù)庫(kù)，你會(huì)發(fā)現(xiàn)admin賬號(hào)只能查看information_schema這個(gè)數(shù)據(jù)庫(kù)，那是因?yàn)槲覀冊(cè)趧?chuàng)建admin賬號(hào)時(shí)，并沒有分配任何權(quán)限。
如果你想在創(chuàng)建賬號(hào)的時(shí)候就分配給該賬號(hào)一定的權(quán)限，那么你可以使用 : grant all privileges on *.* to 'admin'@'%' identified by '123456' with grant option; 該語(yǔ)句表示創(chuàng)建admin賬號(hào)允許從任何主機(jī)登錄并且擁有數(shù)據(jù)庫(kù)全部的權(quán)限，可以將自己的權(quán)限授予給別人。我們將這條語(yǔ)句拆分:
創(chuàng)建賬號(hào)時(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ù)庫(kù)和哪些表，*.*表示作用于所有數(shù)據(jù)庫(kù)和所有表，格式為 數(shù)據(jù)庫(kù)名.表名。比如有一個(gè)test庫(kù)，想讓admin擁有查看test庫(kù)所有表的權(quán)限，那么可以這樣寫：grant SELECT on test.* to 'admin'@'%' identified by '登錄密碼';
to：將權(quán)限授予哪個(gè)用戶。格式：'用戶名'@'登錄IP或域名'。
IDENTIFIED by：指定用戶的登錄密碼。
with grant option：表示允許用戶將自己的權(quán)限授權(quán)給其它用戶。
但請(qǐng)注意可能由于MySQL的數(shù)據(jù)庫(kù)版本問題，有些版本不允許在創(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)限呢？這里簡(jiǎn)單整理了一部分權(quán)限以及其含義：
| 權(quán)限 | 意義 |
|---|---|
| ALL [PRIVILEGES] | 設(shè)置除GRANT OPTION之外的所有簡(jiǎn)單權(quán)限 |
| ALTER | 允許使用ALTER TABLE |
| ALTER ROUTINE | 更改或取消已存儲(chǔ)的子程序 |
| CREATE | 允許使用CREATE TABLE |
| CREATE USER | 允許使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。 |
| CREATE VIEW | 允許使用CREATE VIEW |
| DELETE | 允許使用DELETE |
| DROP | 允許使用DROP TABLE |
| INDEX | 允許使用CREATE INDEX和DROP INDEX |
| INSERT | 允許使用INSERT |
| SELECT | 允許使用SELECT |
| SHOW DATABASES | SHOW DATABASES顯示所有數(shù)據(jù)庫(kù) |
| SHOW VIEW | 允許使用SHOW CREATE VIEW |
| SHUTDOWN | 允許使用mysqladmin shutdown |
| UPDATE | 允許使用UPDATE |
| GRANT OPTION | 允許授予權(quán)限 |
以上表格舉例了部分權(quán)限以及其含義，如果你想給一個(gè)賬號(hào)賦予多個(gè)權(quán)限，可以用英文逗號(hào)分開，比如Grant SELECT,UPDATE,DROP等，如果想連接更多信息，歡迎點(diǎn)擊Mysql賬號(hào)管理查看
（2）刪除用戶
剛剛我們已經(jīng)創(chuàng)建了一個(gè)admin賬號(hào)并且沒有為其設(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ù)庫(kù)(db_cztl_dev)的權(quán)限，請(qǐng)注意db_cztl_dev是在我MySQL中存在的一個(gè)數(shù)據(jù)庫(kù)，您可以使用您自己的數(shù)據(jù)庫(kù)。執(zhí)行命令:
create user 'admin'@'%' identified by '123456' -- 創(chuàng)建用戶
GRANT SELECT ON `db_cztl_dev`.* TO 'admin'@'%' -- 將db_cztl_dev數(shù)據(jù)庫(kù)的讀權(quán)限賦予admin
此時(shí)user表會(huì)有admin賬號(hào)的信息，我們可以看到user表里的admin的select_priv是為N，因?yàn)?strong>admin現(xiàn)在的權(quán)限只是能訪問某一個(gè)數(shù)據(jù)庫(kù)，而非全部數(shù)據(jù)庫(kù)，所以user表里的select_pirv是為N，但是db表(數(shù)據(jù)庫(kù)層級(jí))會(huì)在db_cztl_dev那條數(shù)據(jù)的select_priv為N。
表示admin可以訪問數(shù)據(jù)庫(kù)db_cztl_dev，擁有該數(shù)據(jù)庫(kù)下的所有SELECT權(quán)限，讓我們用admin賬號(hào)登錄，就可以看到db_cztl_dev數(shù)據(jù)庫(kù)。
如果我們要禁用admin賬號(hào)，可以輸入命令:
ALTER USER 'admin'@'%' ACCOUNT lock; -- 禁用admin賬號(hào)
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命令，相對(duì)應(yīng)的撤銷用戶的某個(gè)權(quán)限可以使用REVOKE。
（6.1）查看數(shù)據(jù)庫(kù)中所有用戶信息
我們知道用戶的基本信息都存放在user表中，那么查看所有用戶信息可以使用以下語(yǔ)句:
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ù)庫(kù)db_cztl_dev下所有資源的權(quán)限。
（6.3）使用REVOKE撤銷權(quán)限
由4.2小節(jié)我們可以看到admin擁有訪問db_cztl_dev數(shù)據(jù)庫(kù)的權(quán)限，假如我們此時(shí)想撤銷admin的權(quán)限，可以這樣寫:
REVOKE SELECT ON db_cztl_dev.* FROM 'admin'@'%'; -- 撤銷admin在數(shù)據(jù)db_cztl_dev的權(quán)限
此時(shí)我們?cè)偈褂?code>SHOW GRANTS FOR 'admin'@'%'查看admin權(quán)限，可以看到admin訪問db_cztl_dev數(shù)據(jù)庫(kù)的權(quán)限已被撤銷。
（7）修改用戶權(quán)限(Grant 權(quán)限類型 ON 數(shù)據(jù)庫(kù).表 TO 用戶)
通過6.3我們已經(jīng)撤銷了admin查看db_cztl_dev的權(quán)限，如果此時(shí)我們想賦予admin用戶可以訪問并且修改db_cztl_dev庫(kù)下cztl_line表的權(quán)限，可以這樣做:
GRANT SELECT,UPDATE ON db_cztl_dev.cztl_line TO 'admin'@'%';
FLUSH PRIVILEGES;
重新使用admin賬號(hào)登錄數(shù)據(jù)庫(kù)，就可以訪問和修改db_cztl_dev庫(kù)下的cztl_line表。
如果想讓admin用戶能訪問整個(gè)db_cztl_dev庫(kù)下所有資源，可以追加權(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賬號(hào)強(qiáng)制密碼過期，可以這樣做:
ALTER USER 'admin'@'%' PASSWORD EXPIRE; --強(qiáng)制admin用戶密碼過期
重啟使用admin登錄時(shí)，會(huì)彈出重置密碼界面。
文章來源：http://www.zghlxwxcb.cn/news/detail-450633.html
總結(jié)
以上就是我對(duì)MySQL用戶權(quán)限分配的總結(jié)和分享，如果有任何意見或者錯(cuò)誤歡迎大家及時(shí)提出，如果有疑問也歡迎留言一起討論交流！文章來源地址http://www.zghlxwxcb.cn/news/detail-450633.html
到了這里，關(guān)于Mysql用戶權(quán)限分配詳解的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
