老师，帮我看下mysql8.0 收回用户权限失效

Question

[imooc@localhost ~]$ mysql -uroot -p -h******
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.16 MySQL Community Server - GPL

Copyright © 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show grants;
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON . TO root@% WITH GRANT OPTION |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create user ‘imooc’@’%’ identified by ‘132456’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set global validate_password=0;
ERROR 1193 (HY000): Unknown system variable 'validate_password’
mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password.length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> create user ‘imooc’@’%’ identified by ‘132456’;
ERROR 1396 (HY000): Operation CREATE USER failed for ‘imooc’@’%‘
mysql> drop user ‘imooc’@’%’;
Query OK, 0 rows affected (0.07 sec)

mysql> create user ‘imooc’@’%’ identified by ‘132456’;
Query OK, 0 rows affected (0.11 sec)

mysql> ALTER USER ‘root’@’%’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ‘lq_mysql2019CN’;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges
-> ;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER ‘imooc’@’%’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER USER ‘imooc’@’%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges
-> ;
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on . to ‘imooc’@’%’;
Query OK, 0 rows affected (0.11 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> revoke all privileges on . from ‘imooc’@’%’;
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
mysql> revoke all privileges on . from imooc;
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

Answer 1

编程浪子 2019-05-07 10:41:37

你好

你只需 SHOW GRANTS;  截图我看看

• 提问者 棒棒糖__ #1

  好的
  mysql> show grants\G;
  *************************** 1. row ***************************
  Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION
  1 row in set (0.00 sec)
  
  ERROR: 
  No query specified

• 归海棠3621204 #2

  老师您好，我也遇到这个问题，我使用的是mysql8。我在添加权限的时候发现，不可以对同一个用户添加两次权限，第一次会成功，第二次就会失败，提示：ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation。
  但是当我用root取消其他用户权限时，就会一直报这个错误。
  
  show grants结果，为啥我有两行，有点不会看。
  
  show grants\G
  
  *************************** 1. row ***************************
  
  Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION
  
  *************************** 2. row ***************************
  
  Grants for root@%: GRANT ALL PRIVILEGES ON `db1`.* TO `root`@`%` WITH GRANT OPTION
  
  2 rows in set (0.00 sec)