MySQL如何快速禁用賬戶登入 & 如何復制/復用賬戶密碼-有解無憂

GreatSQL社區原創內容未經授權不得隨意使用，轉載請聯系小編并注明來源，
GreatSQL是MySQL的國產分支版本，使用上與MySQL一致，
作者：葉金榮
文章來源：GreatSQL社區原創

如何快速臨時禁止某賬戶登入

角色ROLES管理需要先激活

關于授權的其他幾點補充

如何復制/復用賬戶密碼

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

有幾個方法：

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

2. 角色ROLES管理

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

# 創建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;

關于ROLES還有其他幾個有趣的事：

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

3. 關于授權的其他幾點補充

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

來舉個例子：

# 對賬戶u1授予對 test.t1 表 c1 列的UPDATE權限
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則因為權限不足失敗了
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權限，因此是看不到其他列的，即便是讀取c1列也不行，在真實生產環境中，可以加上對主鍵列或其他搜索列的授權，方便加上搜索條件后再更新，例如：

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 方式創建用戶時，可以直接從其他賬戶的密碼串復制過來作為新賬戶的密碼，例如：

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 創建用戶時，就不能這么做了，否則會提示類似下面的報錯：

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.

經過查閱手冊，發現可以用十六進制方式指定密碼串，不過前提是需要先設定 print_identified_with_as_hex=1，例如：

mysql> set print_identified_with_as_hex=1;

# 執行SHOW CREATE USER查看現有賬戶密碼串
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

# 復制十六進制密碼串，創建新賬戶即可
mysql> create user u6 identified with caching_sha2_password as 0x244124303035244F574114162D6114176433411E1C1A2C44194F1B777A4978724B6755436B6C786C552F5479314F484B65474E374C473051656B737A523941364D6963577132;

MySQL手冊中的相關介紹如下：

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

關于 GreatSQL

GreatSQL是由萬里資料庫維護的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級應用的MySQL分支版本，

相關鏈接： GreatSQL社區 Gitee GitHub Bilibili

GreatSQL社區：

社區博客有獎征稿詳情：https://greatsql.cn/thread-100-1-1.html

技術交流群：

微信：掃碼添加GreatSQL社區助手微信好友，發送驗證資訊加群，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/542241.html

標籤：MySQL

上一篇：NL連接一定是小表驅動大表效率高嗎

下一篇：[20221227]a mutating table error without a trigger!.txt