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

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

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

1. 快速臨時禁止某用戶登入

有幾個方法：

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

2. 角色ROLES管理

把角色/ROLES授予某個賬號后，記得還要再激活才行：

# 創(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還有其他幾個有趣的事：

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

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

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

來舉個例子：

# 對賬戶u1授予對 test.t1 表 c1 列的UPDATE權(quán)限
mysql> GRANT UPDATE(c1) ON test.t1 to u1;
# 切換到u1賬戶登入
$ mysql -hxx -uu1 -pxx test
# 這幾個SQL能成功
mysql> UPDATE test.t1 SET c1='c1' LIMIT 1;
mysql> UPDATE test.t1 SET c1=CONCAT('c', rand());
# 這幾個SQL則因為權(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'

上面這個例子中，因為賬戶 u1 只有對 test.t1(c1) 列的UPDATE權(quán)限，因此是看不到其他列的，即便是讀取c1列也不行。在真實生產(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. 如何復制/復用賬戶密碼

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

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 |
+------+------+-----------------------+-------------------------------------------+

但是當使用 caching_sha2_password 創(chuàng)建用戶時，就不能這么做了，否則會提示類似下面的報錯：

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)可以用十六進制方式指定密碼串，不過前提是需要先設(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

# 復制十六進制密碼串，創(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 ??