MySQL如何快速禁用账户登入 & 如何复制/复用账户密码

2023-02-22 10:42:48 浏览数 (1)

* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

  1. 如何快速临时禁止某账户登入
  2. 角色ROLES管理需要先激活
  3. 关于授权的其他几点补充
  4. 如何复制/复用账户密码

1. 快速临时禁止某用户登入

有几个方法：

  • 修改其密码 ALTER USER x IDENTIFIED BY 'new_passwd'，或者将其修改为随机密码 ALTER USER x IDENTIFIED BY RANDOM PASSWORD；
  • 锁定其账户 ALTER USER x ACCOUNT LOCK；

2. 角色ROLES管理

把角色/ROLES授予某个账号后，记得还要再激活才行：

代码语言：javascript复制
# 创建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. 关于授权的其他几点补充

  • 可以对某个表单独授予CREATEDROPALTER等权限。
  • 创建临时表（CREATE TEMPORARY TABLES）的授权只能针对某个DB，不能指定具体数据表名。
  • 无法回收USAGE权限。也就是说想要禁用某账户的话，要么DROP，要么参考上一条方法，修改其密码或将其LOCK，而不能通过回收USAGE权限将其禁用。
  • 利用GRANT授权后，是能立即生效的。也就是说，如果在一个事务中发现权限不够，立即请管理员授权，（不用重新连接）直接重试一次事务，即可成功。
  • MySQL授权支持具体到某个列，但也要注意做好控制。

来举个例子：

代码语言：javascript复制
# 对账户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列也不行。在真实生产环境中，可以加上对主键列或其他搜索列的授权，方便加上搜索条件后再更新，例如：

代码语言：javascript复制
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 方式创建用户时，可以直接从其他账户的密码串复制过来作为新账户的密码，例如：

代码语言：javascript复制
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 创建用户时，就不能这么做了，否则会提示类似下面的报错：

代码语言：javascript复制
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，例如：

代码语言：javascript复制
mysql> set print_identified_with_as_hex=1;

# 执行SHOW CREATE USER查看现有账户密码串
mysql> show create user u4G
show create user u4G
*************************** 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手册中的相关介绍如下：

代码语言：javascript复制
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 :)

《深入浅出MGR》视频课程

戳此小程序即可直达B站

https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0

文章推荐：

  • 活动 | GreatSQL出席NVIDIA InfiniBand 存算分离数据库在线研讨会
  • 联合评测 | GreatSQL 开源数据库在 DapuStor Roealsen5 NVMe SSD 中的应用探索
  • MySQL通过 XtraBackup 备份恢复单个库
  • 图文结合带你搞懂MySQL日志之relay log（中继日志）
  • GreatSQL社区月报 | 2022.12

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。

GreatSQL社区官网: https://greatsql.cn/

Gitee: https://gitee.com/GreatSQL/GreatSQL

GitHub: https://github.com/GreatSQL/GreatSQL

Bilibili：https://space.bilibili.com/1363850082

https 网络安全 数据库 云数据库SQLServer sql

0 人点赞