MySQL中的賬號(hào)與權(quán)限管理
權(quán)限系統(tǒng)的工作原理
MySQL權(quán)限系統(tǒng)通過下面兩個(gè)階段進(jìn)行認(rèn)證：
- 對連接的用戶進(jìn)行身份認(rèn)證，合法的用戶通過認(rèn)證、不合法的用戶拒絕連接。
- 對通過認(rèn)證的合法用戶賦予相應(yīng)的權(quán)限，用戶可以在這些權(quán)限范圍內(nèi)對數(shù)據(jù)庫做相應(yīng)的操作。
對于身份，MySQL是通過IP地址和用戶名聯(lián)合進(jìn)行確認(rèn)的，例如MySQL安裝默認(rèn)創(chuàng)建的用戶root@localhost表示用戶root只能從本地（localhost）進(jìn)行連接才可以通過認(rèn)證，此用戶從其他任何主機(jī)對數(shù)據(jù)庫進(jìn)行的連接都將被拒絕。也就是說，同樣的一個(gè)用戶名，如果來自不同的IP地址，則MySQL將其視為不同的用戶。
MySQL的權(quán)限表在數(shù)據(jù)庫啟動(dòng)地時(shí)候就載入內(nèi)存，當(dāng)用戶通過身份認(rèn)證后，就在內(nèi)存中進(jìn)行相應(yīng)權(quán)限的存取，這樣，此用戶就可以在數(shù)據(jù)庫中做權(quán)限范圍內(nèi)的各種操作了。
權(quán)限表
系統(tǒng)會(huì)用到名叫“mysql”數(shù)據(jù)庫（安裝MySQL時(shí)被創(chuàng)建）中user表作為權(quán)限表
我們看看user表的結(jié)構(gòu)（注：本文示例使用的是MySQL5.7.25版本）
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
當(dāng)用戶進(jìn)行連接的時(shí)候，權(quán)限表的存取過程有以下現(xiàn)個(gè)階段。
- 先從user表中的
Host、User、authentication_string(密碼)、password_expired和password_lifetime這幾個(gè)字段中判斷連接的IP、用戶名和密碼是否存在于表中，如果存在，則通過身份驗(yàn)證，否則拒絕連接。 - 如果驗(yàn)證通過，再通過以
_priv結(jié)尾的那些枚舉字段（這些都是用戶的權(quán)限開關(guān)（Y/N））得到用戶擁有的權(quán)限。
賬號(hào)管理
賬號(hào)管理主要包括賬號(hào)的創(chuàng)建、權(quán)限更改和賬號(hào)的刪除。用戶連接數(shù)據(jù)庫的第一步都從賬號(hào)創(chuàng)建開始。
有兩種方法可以用來創(chuàng)建賬號(hào)：使用GRANT語法創(chuàng)建或者直接操作授權(quán)表，但更推薦使用第一種方法，因?yàn)椴僮骱唵危鲥e(cuò)幾率更少。
我們用幾個(gè)例子來說明吧：
-
創(chuàng)建用戶
創(chuàng)建用戶tom，權(quán)限為可以在所有數(shù)據(jù)庫上執(zhí)行所有權(quán)限，只能從本地進(jìn)行連接。
mysql> GRANT ALL PRIVILEGES ON *.* TO tom@localhost IDENTIFIED BY 'tompassword' WITH GRANT OPTION;如果你執(zhí)行這個(gè)語句碰到以下錯(cuò)誤：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements。這個(gè)是密碼策略的問題，請?jiān)O(shè)置比較復(fù)雜的密碼，或者修改密碼策略，這里就不詳細(xì)說了。GRANT命令說明：
ALL PRIVILEGES是表示所有權(quán)限，你也可以使用select、update等權(quán)限。ON用來指定權(quán)限針對哪些庫和表，格式是數(shù)據(jù)庫名.表名，這里*.*表示所有數(shù)據(jù)庫和所有表。TO表示將權(quán)限賦予某個(gè)用戶。tom@localhost，表示tom用戶，@后面接限制的主機(jī)，可以是IP、IP段、域名以及%，%表示任何地方。注意：這里%有的版本不包括本地，以前碰到過給某個(gè)用戶設(shè)置了%允許任何地方登錄，但是在本地登錄不了，這個(gè)和版本有關(guān)系，遇到這個(gè)問題再加一個(gè)localhost的用戶就可以了。IDENTIFIED BY指定用戶的登錄密碼， 這里'tompassword'就是用戶tom的密碼。WITH GRANT OPTION這個(gè)選項(xiàng)表示該用戶可以將自己擁有的權(quán)限授權(quán)給別人。注意：經(jīng)常有人在創(chuàng)建操作用戶的時(shí)候不指定WITH GRANT OPTION選項(xiàng)導(dǎo)致后來該用戶不能使用GRANT命令創(chuàng)建用戶或者給其它用戶授權(quán)。備注：可以使用
GRANT重復(fù)給用戶添加權(quán)限，權(quán)限疊加，比如你先給用戶添加一個(gè)select權(quán)限，然后又給用戶添加一個(gè)insert權(quán)限，那么該用戶就同時(shí)擁有了select和insert權(quán)限。使用
GRANT操作用戶權(quán)限之后，再使用FLUSH PRIVILEGES命令來刷新權(quán)限使其立即生效mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) -
查看用戶的權(quán)限
直接使用
SHOW GRANTS默認(rèn)查看root@localhost的權(quán)限mysql> SHOW GRANTS; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.01 sec)查看某個(gè)用戶的權(quán)限
mysql> SHOW GRANTS FOR tom@localhost; +----------------------------------------------------------------------+ | Grants for tom@localhost | +----------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'tom'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------+ 1 row in set (0.00 sec) -
收回權(quán)限
mysql> REVOKE DELETE ON *.* FROM 'tom'@'localhost'; Query OK, 0 rows affected (0.00 sec) -
對用戶賬戶重命名
mysql> RENAME USER tom@localhost to jerry@localhost; Query OK, 0 rows affected (0.00 sec) -
刪除用戶
mysql> DROP USER jerry@localhost; Query OK, 0 rows affected (0.01 sec) -
修改和重置密碼
- 用
SET PASSWORD命令修改密碼
mysql> SET PASSWORD FOR root@localhost = PASSWORD('123456'); Query OK, 0 rows affected, 1 warning (0.01 sec)- 直接修改user表
mysql> UPDATE user SET authentication_string=PASSWORD('123456root') WHERE user='root' and host='localhost'; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)- 在未登錄mysql的情況下用mysqladmin命令修改密碼
$ mysqladmin -uroot -p123456root password 123321- 在丟失root密碼的時(shí)候
關(guān)閉mysql服務(wù)（根據(jù)你自己的操作系統(tǒng)自行關(guān)閉），然后跳過權(quán)限認(rèn)證啟動(dòng)mysql服務(wù)
$ mysqld_safe --skip-grant-tables &無密碼登陸
$ mysql -uroot進(jìn)入之后使用上面直接修改user表的方法修改root用戶的密碼
最后殺掉
mysqld_safe和mysqld的進(jìn)程重新啟動(dòng)mysql服務(wù)，用新的密碼登陸吧。
- 用
