MySQL快速禁用賬戶登入及如何復(fù)制/復(fù)用賬戶密碼(最新推薦)

更新時(shí)間：2024年01月25日 09:13:30 作者：paul_hch

這篇文章主要介紹了MySQL如何快速禁用賬戶登入及如何復(fù)制/復(fù)用賬戶密碼,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

1. 快速臨時(shí)禁止某用戶登入

有幾個(gè)方法：

修改其密碼 ALTER USER x IDENTIFIED BY 'new_passwd'，或者將其修改為隨機(jī)密碼 ALTER USER x IDENTIFIED BY RANDOM PASSWORD；
鎖定其賬戶 ALTER USER x ACCOUNT LOCK；

2. 角色ROLES管理

把角色/ROLES授予某個(gè)賬號(hào)后，記得還要再激活才行：

# 創(chuàng)建ROLE r1并授予用戶u1
mysql> CREATE ROLE r1;
mysql> GRANT SELECT ON sbtest.* TO r1;
mysql> GRANT r1 to u1;
# 激活
mysql> SET DEFAULT ROLE r1 TO u1;

關(guān)于ROLES還有其他幾個(gè)有趣的事：

和USERS一樣，都存儲(chǔ)在mysql.user表。
新創(chuàng)建的ROLE默認(rèn)是沒有密碼的 & 密碼過期 & 處于LOCK狀態(tài)。
可以為ROLE設(shè)置密碼，并對其UNLOCK后（執(zhí)行ALTER USER命令），也可以像USER那樣正常登入了。
將ROLE授予某個(gè)賬戶后，授權(quán)不能立即生效，需要新建立連接才可以（如果是直接對賬戶GRANT授權(quán)，無需重連就能立即生效）。

3. 關(guān)于授權(quán)的其他幾點(diǎn)補(bǔ)充

可以對某個(gè)表單獨(dú)授予CREATE\DROP\ALTER等權(quán)限。
創(chuàng)建臨時(shí)表（CREATE TEMPORARY TABLES）的授權(quán)只能針對某個(gè)DB，不能指定具體數(shù)據(jù)表名。
無法回收USAGE權(quán)限。也就是說想要禁用某賬戶的話，要么DROP，要么參考上一條方法，修改其密碼或?qū)⑵銵OCK，而不能通過回收USAGE權(quán)限將其禁用。
利用GRANT授權(quán)后，是能立即生效的。也就是說，如果在一個(gè)事務(wù)中發(fā)現(xiàn)權(quán)限不夠，立即請管理員授權(quán)，（不用重新連接）直接重試一次事務(wù)，即可成功。
MySQL授權(quán)支持具體到某個(gè)列，但也要注意做好控制。

來舉個(gè)例子：

# 對賬戶u1授予對 test.t1 表 c1 列的UPDATE權(quán)限
mysql> GRANT UPDATE(c1) ON test.t1 to u1;
# 切換到u1賬戶登入
$ mysql -hxx -uu1 -pxx test
# 這幾個(gè)SQL能成功
mysql> UPDATE test.t1 SET c1='c1' LIMIT 1;
mysql> UPDATE test.t1 SET c1=CONCAT('c', rand());
# 這幾個(gè)SQL則因?yàn)闄?quán)限不足失敗了
mysql> UPDATE test.t1 SET c1=CONCAT ('c-new' , c1) ;
ERROR 1143 (42000): SELECT command denied to user 'u1'@'127.0.0.1' for column 'c' in table 't1'
mysql> UPDATE test.t1 SET c1='c1' WHERE id = 1;
ERROR 1143 (42000): SELECT command denied to user 'u1'@'127.0.0.1' for column 'id' in table 't1'

上面這個(gè)例子中，因?yàn)橘~戶 u1 只有對 test.t1(c1) 列的UPDATE權(quán)限，因此是看不到其他列的，即便是讀取c1列也不行。在真實(shí)生產(chǎn)環(huán)境中，可以加上對主鍵列或其他搜索列的授權(quán)，方便加上搜索條件后再更新，例如：

mysql> SHOW GRANTS FOR u1;
| GRANT USAGE ON *.* TO `u1`@`%`                                      |
| GRANT SELECT (`id`, `c1`), UPDATE (`c1`) ON `test`.`t1` TO `u1`@`%` |

4. 如何復(fù)制/復(fù)用賬戶密碼

采用 mysql_native_password 方式創(chuàng)建用戶時(shí)，可以直接從其他賬戶的密碼串復(fù)制過來作為新賬戶的密碼，例如：

mysql> create user u4 identified with mysql_native_password by 'u4';
mysql> select host,user,plugin,authentication_string from mysql.user where user='u4';
+------+------+-----------------------+-------------------------------------------+
| host | user | plugin                | authentication_string                     |
+------+------+-----------------------+-------------------------------------------+
| %    | u4   | mysql_native_password | *06196708822D12C033A8BF492D3902405DF3C781 |
+------+------+-----------------------+-------------------------------------------+
mysql> create user u5 identified with mysql_native_password as '*06196708822D12C033A8BF492D3902405DF3C781';
mysql> select host,user,plugin,authentication_string from mysql.user where user in ('u4', 'u5');
+------+------+-----------------------+-------------------------------------------+
| host | user | plugin                | authentication_string                     |
+------+------+-----------------------+-------------------------------------------+
| %    | u4   | mysql_native_password | *06196708822D12C033A8BF492D3902405DF3C781 |
| %    | u5   | mysql_native_password | *06196708822D12C033A8BF492D3902405DF3C781 |
+------+------+-----------------------+-------------------------------------------+

但是當(dāng)使用 caching_sha2_password 創(chuàng)建用戶時(shí)，就不能這么做了，否則會(huì)提示類似下面的報(bào)錯(cuò)：

mysql> select host,user,plugin,authentication_string from mysql.user where user='u1'\G
*************************** 1. row ***************************
                 host: %
                 user: u1
               plugin: caching_sha2_password
authentication_string: $A$005$OWA-ad3A,DOzIxrKgUCklxlU/Ty1OHKeGN7LG0QekszR9A6MicWq2
mysql> create user u3 identified with caching_sha2_password as '$A$005$OWA-ad3A,DOzIxrKgUCklxlU/Ty1OHKeGN7LG0QekszR9A6MicWq2';
ERROR 1827 (HY000): The password hash doesn't have the expected format.

經(jīng)過查閱手冊，發(fā)現(xiàn)可以用十六進(jìn)制方式指定密碼串，不過前提是需要先設(shè)置 print_identified_with_as_hex=1，例如：

mysql> set print_identified_with_as_hex=1;

# 執(zhí)行SHOW CREATE USER查看現(xiàn)有賬戶密碼串
mysql> show create user u4\G
show create user u4\G
*************************** 1. row ***************************
CREATE USER for u1@%: CREATE USER `u1`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035244F574114162D6114176433411E1C1A2C44194F1B777A4978724B6755436B6C786C552F5479314F484B65474E374C473051656B737A523941364D6963577132 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT PASSWORD_LOCK_TIME 1

# 復(fù)制十六進(jìn)制密碼串，創(chuàng)建新賬戶即可
mysql> create user u6 identified with caching_sha2_password as 0x244124303035244F574114162D6114176433411E1C1A2C44194F1B777A4978724B6755436B6C786C552F5479314F484B65474E374C473051656B737A523941364D6963577132;

MySQL手冊中的相關(guān)介紹如下：

Password hash values displayed in the IDENTIFIED WITH clause of output from SHOW CREATE USER may contain unprintable characters that have adverse effects on terminal displays and in other environments. Enabling the print_identified_with_as_hex system variable (available as of MySQL 8.0.17) causes SHOW CREATE USER to display such hash values as hexadecimal strings rather than as regular string literals. Hash values that do not contain unprintable characters still display as regular string literals, even with this variable enabled.

Enjoy GreatSQL ??