MySQL 權(quán)限控制細(xì)節(jié)分析

更新時(shí)間：2021年03月27日 11:56:26 作者：AsiaYe

這篇文章主要介紹了MySQL 權(quán)限控制細(xì)節(jié)分析的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下

今天周天，早上懶了一會(huì)兒，起的有點(diǎn)兒晚，中午沒事兒干，重新看了看MySQL里面的權(quán)限控制模塊，再次回頭看，還是有很多收獲的細(xì)節(jié)，這里記錄一下，方便自己后續(xù)查看。
關(guān)于權(quán)限部分的內(nèi)容，之前3月11號(hào)的文章中有寫過一些，今天的內(nèi)容，我們使用一個(gè)一個(gè)的細(xì)節(jié)知識(shí)點(diǎn)來撰寫（本文中所使用的MySQL版本是5.7.16），在寫這些知識(shí)點(diǎn)之前，我們首先介紹一下MySQL的權(quán)限控制粒度、然后了解一下MySQL中客戶端發(fā)起請(qǐng)求的時(shí)候，服務(wù)端所做的核實(shí)工作，先來看權(quán)限控制粒度：

1、全局層級(jí)

全局權(quán)限使用于給一個(gè)給定服務(wù)器中的所有數(shù)據(jù)庫(kù)，這些權(quán)限存儲(chǔ)在mysql.user表中，使用grant all on *.*的方法和revoke all on *.*的方法授予或者回收權(quán)限。

2、數(shù)據(jù)庫(kù)層級(jí)

數(shù)據(jù)庫(kù)權(quán)限適用于一個(gè)給定數(shù)據(jù)庫(kù)中的所有目標(biāo)，包含表對(duì)象和存儲(chǔ)過程，這些權(quán)限存儲(chǔ)在mysql.db表中，使用grant all on db_name.*或者對(duì)應(yīng)的revoke方法可以授予和回收數(shù)據(jù)庫(kù)權(quán)限

3、表層級(jí)

表權(quán)限適用于一個(gè)給定表中的所有列，這些權(quán)限存儲(chǔ)在mysql的tables_priv表中，一般使用grant all on db_name.tbl_name和對(duì)應(yīng)的revoke語(yǔ)句來授予或者撤銷權(quán)限。

4、列層級(jí)的權(quán)限

列層級(jí)的權(quán)限適用于一個(gè)給定表中的指定列，這些權(quán)限存儲(chǔ)在mysql.columns_priv的表中，由于這個(gè)權(quán)限不常用，這里給出它的授權(quán)方法示例，如下：

首先我們創(chuàng)建一個(gè)用戶，擁有yeyztest.test1這個(gè)表的select權(quán)限：

mysql:mysql 19:35:38>>show grants for dba_yeyz@'192.168.18.%' ;
+------------------------------------------------------------------------------+
| Grants for dba_yeyz@192.168.18.%                       |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba_yeyz'@'192.168.18.%'               |
| GRANT SELECT ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%'        |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

然后我們多test1中的一個(gè)字段id進(jìn)行update操作，結(jié)果如下：

mysql> select * from test1;
+---------------+
| id      |
+---------------+
|      22 |
| 3333333333333 |
+---------------+
2 rows in set (0.00 sec)

mysql> update test1 set id=2 where id=22;
ERROR 1142 (42000): UPDATE command denied to user 'dba_yeyz'@'192.168.18.**' for table 'test1'

當(dāng)然，我們是不能進(jìn)行update的，這個(gè)時(shí)候，我們使用root賬號(hào)給這個(gè)dba_yeyz的賬號(hào)一個(gè)id列的權(quán)限，然后再看它的結(jié)果：

mysql:mysql 19:38:38>>show grants for dba_yeyz@'192.168.18.%' ;
+------------------------------------------------------------------------------+
| Grants for dba_yeyz@192.168.18.%                       |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba_yeyz'@'192.168.18.%'               |
| GRANT SELECT ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%'       |
| GRANT SELECT, UPDATE (id) ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%' |
+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

這里需要注意給字段添加權(quán)限的語(yǔ)句，也就是：

grant update (id) on yeyztest.test1 to XXXXXX

也就是在權(quán)限后面跟上字段的名稱。

這個(gè)時(shí)候，我們查詢一下columns_priv的表，可以看到里面的記錄是：

mysql:mysql 19:39:46>>select * from columns_priv;
+--------------+----------+----------+------------+-------------+---------------------+-------------+
| Host     | Db    | User   | Table_name | Column_name | Timestamp      | Column_priv |
+--------------+----------+----------+------------+-------------+---------------------+-------------+
| 192.168.18.% | yeyztest | dba_yeyz | test1   | id     | 0000-00-00 00:00:00 | Update   |
+--------------+----------+----------+------------+-------------+---------------------+-------------+
1 row in set (0.00 sec)

再次用dba_yeyz進(jìn)行update操作，可以看到結(jié)果：

mysql> update test1 set id=2 where id=22;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select *from test1;
+---------------+
| id      |
+---------------+
|       2 |
| 3333333333333 |
+---------------+
2 rows in set (0.00 sec)

將id=22的列成功改成了id=2。

5、子程序?qū)蛹?jí)

create routine、alter routine、execute和grant權(quán)限適用于已經(jīng)存儲(chǔ)的子程序，這些權(quán)限可以被授予為全局層級(jí)和數(shù)據(jù)庫(kù)層級(jí)，可以被存儲(chǔ)在mysql.procs_priv中。

客戶端發(fā)起請(qǐng)求的時(shí)候，MySQL服務(wù)器核實(shí)請(qǐng)求時(shí)候的流程圖如下：

我將今天看到的一些細(xì)節(jié)的知識(shí)點(diǎn)一個(gè)一個(gè)羅列出來，希望對(duì)大家有點(diǎn)兒幫助：

1、在MySQL5.7.16版本中，mysql系統(tǒng)庫(kù)中已經(jīng)沒有host表了，跟權(quán)限控制相關(guān)的表只有5個(gè)，分別是user、db、table_priv、proc_priv、column_priv。

2、mysql.user表的主鍵是用user和host聯(lián)合起來組成的，且看表結(jié)構(gòu)：

mysql--dba_admin@127.0.0.1:mysql 19:44:56>>show create table mysql.user\G
*************************** 1. row ***************************
    Table: user
Create Table: CREATE TABLE `user` (
 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
---------------權(quán)限字段（29個(gè)）--------------
 `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 ......此處省略

---------------安全字段（4個(gè)）---------------
 `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
 `ssl_cipher` blob NOT NULL,
 `x509_issuer` blob NOT NULL,
 `x509_subject` blob NOT NULL,
---------------資源控制字段（4個(gè)）--------------
 `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
 `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
 `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
 `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
--------------插件字段（1個(gè)）---------------
 `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
--------------密碼字段（5個(gè)）--------------
 `authentication_string` text COLLATE utf8_bin,
 `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `password_last_changed` timestamp NULL DEFAULT NULL,
 `password_lifetime` smallint(5) unsigned DEFAULT NULL,
 `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 PRIMARY KEY (`Host`,`User`) -------------聯(lián)合主鍵，host在前-----------
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

3、tables_priv表中只有以下權(quán)限，也就是關(guān)于表的權(quán)限：

select、insert、update、delete、drop、create、alter、grant、references、index、create view、show view、trigger；

columns_priv表中的權(quán)限只有下面四個(gè)：select、insert、update，references

4、修改一個(gè)用戶的密碼，一般有以下幾種方式：

set password for user@host = password('newpassword');
update mysql.user set authentication_string=password('pwd') where user='username' and host='hostname';
alter user user@host identified by 'newpassword';
mysqladmin -u username -h hostname -p password "new password";

最好的方式是alter user的方法，事實(shí)上，在新的版本8.0中，set password的方法已經(jīng)不能使用了，所以建議使用alter user的方法設(shè)置新的密碼。

除此之外，還有一種方法，不太常用，就是使用grant的方法覆蓋掉之前的密碼，這里我們簡(jiǎn)單實(shí)驗(yàn)一般，看看效果：

mysql:mysql 20:01:05>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%' identified by '111111';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql:mysql 20:01:29>>select user,host,concat(user,'@','\'',host,'\''),authentication_string from mysql.user;
+------------------+----------------+-----------------------------------+-------------------------------------------+
| user       | host      | concat(user,'@','\'',host,'\'')  | authentication_string           |
+------------------+----------------+-----------------------------------+-------------------------------------------+
| dba_yeyz     | 192.168.18.%  | dba_yeyz@'192.168.18.%'      | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+------------------+----------------+-----------------------------------+-------------------------------------------+
11 rows in set (0.00 sec)

mysql:mysql 20:01:31>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql:mysql 20:01:57>>select user,host,concat(user,'@','\'',host,'\''),authentication_string from mysql.user;
+------------------+----------------+-----------------------------------+-------------------------------------------+
| user       | host      | concat(user,'@','\'',host,'\'')  | authentication_string           |
+------------------+----------------+-----------------------------------+-------------------------------------------+
| dba_yeyz     | 192.168.18.%  | dba_yeyz@'192.168.18.%'      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------------------+----------------+-----------------------------------+-------------------------------------------+
1 rows in set (0.00 sec)

上面的測(cè)試可以看到，當(dāng)我們使用grant的方法給一個(gè)指定的用戶重新設(shè)置密碼的時(shí)候，之前的老密碼會(huì)被覆蓋，所以這個(gè)操作在線上應(yīng)該慎用，在每次grant的時(shí)候看看有沒有已經(jīng)存在的賬號(hào)，確認(rèn)沒有之后，再進(jìn)行g(shù)rant操作

5、如果我們不慎忘記了mysql的root密碼，可以重新啟動(dòng)mysql服務(wù)，加上--skip-grant-tables這個(gè)參數(shù)來啟動(dòng)mysql服務(wù)，這樣就可以直接免除了在權(quán)限表里面的匹配工作，直接登陸進(jìn)mysql服務(wù)中，從而修改root賬號(hào)的密碼。

6、如果使用update或者insert記錄到mysql.user表中的方法創(chuàng)建賬戶或者修改密碼，在執(zhí)行完語(yǔ)句之后，必須使用flush privileges的操作刷新權(quán)限表，否則該操作無法產(chǎn)生效果。

7、有幾個(gè)權(quán)限會(huì)影響mysqladmin工具的執(zhí)行，分別是

reload權(quán)限：影響flush操作

shutdown權(quán)限：影響shutdown操作

process權(quán)限：影響processlist操作

super權(quán)限：影響kill操作

8、之前提到了mysql.user表中的資源控制的字段，分別是

max_questions每小時(shí)最大請(qǐng)求數(shù)、max_updates每小時(shí)最大更新數(shù)、max_connections每小時(shí)最大連接數(shù)、max_user_connections單個(gè)用戶可同時(shí)建立的最大連接數(shù)。

如果我們想給一個(gè)用戶設(shè)置這個(gè)參數(shù)，可以使用如下的SQL來進(jìn)行設(shè)置：

mysql:mysql 20:01:58>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%' with max_queries_per_hour 1000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql:mysql 20:13:13>>select user,host,max_questions from mysql.user where user='dba_yeyz';  
+----------+--------------+---------------+
| user   | host     | max_questions |
+----------+--------------+---------------+
| dba_yeyz | 192.168.18.% |     1000 |
+----------+--------------+---------------+
1 row in set (0.00 sec)

注意到，這里的grant語(yǔ)句中使用了with這個(gè)選項(xiàng)，with后面可以跟的選項(xiàng)有5個(gè)，分別是：

grant option：被授權(quán)的用戶可以將這些權(quán)限賦予別的用戶

max_queries_per_hour count:每個(gè)小時(shí)可以執(zhí)行count次查詢；

max_updates_per_hour count:每個(gè)小時(shí)可以執(zhí)行count次更新；

max_connections_per_hour count:每個(gè)小時(shí)可以建立count個(gè)連接；

max_user_connections count：設(shè)置單個(gè)用戶可以同時(shí)建立count個(gè)連接

9、設(shè)置全局變量：

SET GLOBAL default_password_lifetime = 180;

SET GLOBAL default_password_lifetime = 0;

可以設(shè)置密碼的生命周期為6個(gè)月，6個(gè)月之后失效，如果設(shè)置為0，則一直有效。

當(dāng)然，還可以在創(chuàng)建用戶的時(shí)候就指定密碼的修改周期或者禁用密碼的修改周期：

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

10、有時(shí)候我們似乎已經(jīng)刪除了賬號(hào)密碼，但是卻還可以通過賬號(hào)密碼進(jìn)行訪問，這個(gè)時(shí)候，需要檢查一個(gè)設(shè)置，就是看看user表中是否有空記錄：

select user,host from mysql.user where user='';

很有可能是你設(shè)置了user為空的記錄，這樣導(dǎo)致所有的用戶都可以直接登陸。如果有，最好直接干掉它，因?yàn)樗`背了安全的宗旨。

以上就是MySQL 權(quán)限控制細(xì)節(jié)分析的詳細(xì)內(nèi)容，更多關(guān)于MySQL 權(quán)限控制的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: