【MySQL 8.0】ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
xixuefeng
MySQL
2021-06-11 15:27:54
2,197 次浏览
【MySQL 8.0】ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation已关闭评论
操作系统:中标麒麟高级服务器操作系统7.6
数据库:mysql-8.0.25-el7-x86_64.tar.gz
MySQL 8.0 新特性之一:用户密码的加密算法由mysql_native_password改为了caching_sha2_password
场景:安装MySQL8.0以后,顺手就创建了一个用户create user ‘dev’@’%’ identified by ‘oracle’;创建完成后想起来了,默认情况下加密算法是caching_sha2_password,navicat就目前的版本来说,连不上mysql数据库,那么我就尝试的改一下用户密码的加密算法(应该就是重新修改一下密码,在改密码的时候,指定原来的加密算法mysql_native_password)如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
[root@DB8 etc]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.25 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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> alter user 'dev'@'%' identified with mysql_native_password by 'oracle'; ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation mysql> mysql> mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@% | +----------------+ 1 row in set (0.00 sec) mysql> mysql> alter user 'dev'@'%' identified with mysql_native_password by 'oracle'; ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation mysql> |
就目前我对mysql认知,并没有看到SYSTEM_USER相关的权限
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
mysql> select * from mysql.user where user='root'\G; *************************** 1. row *************************** Host: % User: root Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: NULL x509_issuer: NULL x509_subject: NULL max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: caching_sha2_password authentication_string: $A$005$<s@ 5 -~x)5vX^&Z 4McimTpbmpBjlFbizcqImcWg3dlLfNDvw2QL4U/Wxo8 password_expired: N password_last_changed: 2021-06-11 11:15:43 password_lifetime: NULL account_locked: N Create_role_priv: Y Drop_role_priv: Y Password_reuse_history: NULL Password_reuse_time: NULL Password_require_current: NULL User_attributes: NULL 1 row in set (0.00 sec) ERROR: No query specified mysql> |
此时,只能通过skip-grant-tables来解决问题
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[root@DB8 etc]# vi /etc/my.cnf [client] socket=/data/db/mysql/2587/mysql.sock [mysqld] datadir=/data/db/mysql/2587 socket=/data/db/mysql/2587/mysql.sock port = 2587 basedir=/usr/local/mysql user = mysql symbolic-links=0 ## 增加skip-grant-tables参数 skip-grant-tables ## 顺便修改一下加密算法的默认值 default-authentication-plugin=mysql_native_password [mysqld_safe] log-error=/data/db/mysql/2587/mysqlerr.log pid-file=/data/db/mysql/2587/mysql.pid |
重启数据库服务器
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
## 因为增加skip-grant-tables参数,所以,mysql登录时无需写用户名和密码 [root@DB8 etc]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.25 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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> mysql> ## 直接修改密码,依旧报错 mysql> alter user 'dev'@'%' identified with mysql_native_password by 'oracle'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement mysql> mysql> ## 刷新一下权限后,则可成功执行修改操作 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> mysql> alter user 'dev'@'%' identified with mysql_native_password by 'oracle'; Query OK, 0 rows affected (0.01 sec) mysql> |
最后,记得修改参数,将skip-grant-tables参数去掉(或注释掉),重启服务器