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 😃