MySQL 權限控制細節分析
今天周天，早上懶瞭一會兒，起的有點兒晚，中午沒事兒幹，重新看瞭看MySQL裡面的權限控制模塊，再次回頭看，還是有很多收獲的細節，這裡記錄一下，方便自己後續查看。
關於權限部分的內容，之前3月11號的文章中有寫過一些，今天的內容，我們使用一個一個的細節知識點來撰寫（本文中所使用的MySQL版本是5.7.16），在寫這些知識點之前，我們首先介紹一下MySQL的權限控制粒度、然後瞭解一下MySQL中客戶端發起請求的時候，服務端所做的核實工作，先來看權限控制粒度：
1、全局層級
全局權限使用於給一個給定服務器中的所有數據庫，這些權限存儲在mysql.user表中，使用grant all on *.*的方法和revoke all on *.*的方法授予或者回收權限。
2、數據庫層級
數據庫權限適用於一個給定數據庫中的所有目標，包含表對象和存儲過程，這些權限存儲在mysql.db表中，使用grant all on db_name.*或者對應的revoke方法可以授予和回收數據庫權限
3、表層級
表權限適用於一個給定表中的所有列，這些權限存儲在mysql的tables_priv表中，一般使用grant all on db_name.tbl_name和對應的revoke語句來授予或者撤銷權限。
4、列層級的權限
列層級的權限適用於一個給定表中的指定列，這些權限存儲在mysql.columns_priv的表中，由於這個權限不常用，這裡給出它的授權方法示例，如下：
首先我們創建一個用戶，擁有yeyztest.test1這個表的select權限：
mysql:mysql 19:35:38>>show grants for dba_yeyz@'192.168.18.%' ; +------------------------------------------------------------------------------+ | Grants for [email protected].% | +------------------------------------------------------------------------------+ | 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中的一個字段id進行update操作，結果如下：
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'
當然，我們是不能進行update的，這個時候，我們使用root賬號給這個dba_yeyz的賬號一個id列的權限，然後再看它的結果：
mysql:mysql 19:38:38>>show grants for dba_yeyz@'192.168.18.%' ; +------------------------------------------------------------------------------+ | Grants for [email protected].% | +------------------------------------------------------------------------------+ | 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)
這裡需要註意給字段添加權限的語句，也就是：
grant update (id) on yeyztest.test1 to XXXXXX
也就是在權限後面跟上字段的名稱。
這個時候，我們查詢一下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進行update操作，可以看到結果：
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、子程序層級
create routine、alter routine、execute和grant權限適用於已經存儲的子程序，這些權限可以被授予為全局層級和數據庫層級，可以被存儲在mysql.procs_priv中。
客戶端發起請求的時候，MySQL服務器核實請求時候的流程圖如下：
我將今天看到的一些細節的知識點一個一個羅列出來，希望對大傢有點兒幫助：
1、在MySQL5.7.16版本中，mysql系統庫中已經沒有host表瞭，跟權限控制相關的表隻有5個，分別是user、db、table_priv、proc_priv、column_priv。
2、mysql.user表的主鍵是用user和host聯合起來組成的，且看表結構：
[email protected]: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 '', ---------------權限字段（29個）-------------- `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', ......此處省略 ---------------安全字段（4個）--------------- `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個）-------------- `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個）--------------- `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password', --------------密碼字段（5個）-------------- `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`) -------------聯合主鍵，host在前----------- ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' 1 row in set (0.00 sec)
3、tables_priv表中隻有以下權限，也就是關於表的權限：
select、insert、update、delete、drop、create、alter、grant、references、index、create view、show view、trigger；
columns_priv表中的權限隻有下面四個：select、insert、update，references
4、修改一個用戶的密碼，一般有以下幾種方式：
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的方法，事實上，在新的版本8.0中，set password的方法已經不能使用瞭，所以建議使用alter user的方法設置新的密碼。
除此之外，還有一種方法，不太常用，就是使用grant的方法覆蓋掉之前的密碼，這裡我們簡單實驗一般，看看效果：
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)
上面的測試可以看到，當我們使用grant的方法給一個指定的用戶重新設置密碼的時候，之前的老密碼會被覆蓋，所以這個操作在線上應該慎用，在每次grant的時候看看有沒有已經存在的賬號，確認沒有之後，再進行grant操作
5、如果我們不慎忘記瞭mysql的root密碼，可以重新啟動mysql服務，加上–skip-grant-tables這個參數來啟動mysql服務，這樣就可以直接免除瞭在權限表裡面的匹配工作，直接登陸進mysql服務中，從而修改root賬號的密碼。
6、如果使用update或者insert記錄到mysql.user表中的方法創建賬戶或者修改密碼，在執行完語句之後，必須使用flush privileges的操作刷新權限表，否則該操作無法產生效果。
7、有幾個權限會影響mysqladmin工具的執行，分別是
reload權限：影響flush操作
shutdown權限：影響shutdown操作
process權限：影響processlist操作
super權限：影響kill操作
8、之前提到瞭mysql.user表中的資源控制的字段，分別是
max_questions每小時最大請求數、max_updates每小時最大更新數、max_connections每小時最大連接數、max_user_connections單個用戶可同時建立的最大連接數。
如果我們想給一個用戶設置這個參數，可以使用如下的SQL來進行設置：
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語句中使用瞭with這個選項，with後面可以跟的選項有5個，分別是：
grant option：被授權的用戶可以將這些權限賦予別的用戶
max_queries_per_hour count:每個小時可以執行count次查詢；
max_updates_per_hour count:每個小時可以執行count次更新；
max_connections_per_hour count:每個小時可以建立count個連接；
max_user_connections count：設置單個用戶可以同時建立count個連接
9、設置全局變量：
SET GLOBAL default_password_lifetime = 180;
SET GLOBAL default_password_lifetime = 0;
可以設置密碼的生命周期為6個月，6個月之後失效，如果設置為0，則一直有效。
當然，還可以在創建用戶的時候就指定密碼的修改周期或者禁用密碼的修改周期：
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、有時候我們似乎已經刪除瞭賬號密碼，但是卻還可以通過賬號密碼進行訪問，這個時候，需要檢查一個設置，就是看看user表中是否有空記錄：
select user,host from mysql.user where user=”;
很有可能是你設置瞭user為空的記錄，這樣導致所有的用戶都可以直接登陸。如果有，最好直接幹掉它，因為它違背瞭安全的宗旨。
以上就是MySQL 權限控制細節分析的詳細內容，更多關於MySQL 權限控制的資料請關註WalkonNet其它相關文章！