ERROR 1044 (42000) 给mysql的root %用户添加grant、super权限，只能看到一个库

  • lhrbest

    2019-06-27 16:44:19
  • MySQL
  • 原创


ERROR 1044 (42000): Access denied for user 'root'@'%'

--给mysql的root %用户添加grant权限。并给创建的用户赋予权限
select user,host,grant_priv,super_priv from mysql.user;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'   IDENTIFIED BY 'lhr' WITH GRANT OPTION;
update mysql.user set grant_priv='Y',super_priv='Y' where user='root';
flush privileges
show grants for 'root'@'%';
--mysql_upgrade -u root -p
--insert into mysql.user(Host,User,Password) values("localhost","root",password("1234"));


从供应商那边接手一个 MySQL数据库（数据库版本为 5.7.21 MySQL Community Server (GPL)），在创建账号时遇到了 “ ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database xxx ” 错误，如下所示

 

 

mysql> grant all on xxx.* to xxx@'192.168.%' identified by 'xxx';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'xxxx'
mysql>

 

照理说， root用户应该有任何权限，那么为什么出现这个错误呢？ 查看当前用户为 root@localhost，顺便查看了一下各个 root账号的权限。如下所示：

 

 

mysql> select current_user() from dual;
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec

mysql> select host,user from user where user='root';
+-----------+----------+
| host      | user     |
+-----------+----------+
| %         | root     |
| 127.0.0.1 | root     |
| ::1       | root     |
| localhost | root     |
+-----------+----------+
7 rows in set (0.00 sec)

mysql>  show grants for root@'localhost';
+--------------------------------------------------------------+
| Grants for root@localhost                                    |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'            |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for root@'127.0.0.1';
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for root@'%';
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

 

 

 

    如上所示， root@localhost账号没有 WITH GRANT OPTION选项，关于 WITH GRANT OPTION选项，如果想让授权的用户，也可以将这些权限授予给其他用户，需要选项  “ WITH GRANT OPTION “   。也就是说有这个选项就可以将权限传递给第三方。这也是上面 root@localhost用户 给其它用后授权报错的原因，如果以  root@127.0.0.1登录（此账号拥有 WITHGRANT OPTION选项），创建用户并授权就不会有这个错误，如下所示：

 

 

# mysql -host 127.0.0.1 -u root -p
Enter password:




mysql>  grant all on xxx.* to xxx@'192.168.%' identified by 'test1249';
Query OK, 0 rows affected, 1 warning (0.00 sec)


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

 

当然还有其它方面的原因也可能会引起这个错误，不过在这个案例当中，确实仅仅是因为上面原因引起。特此记录一下这个案例 @！



     Mac 下的MySQL ERROR 1227(42000): Access denied; you need (at least    

one of)


Mac  下的 MySQL（ 只有连个库）

AC-IT002deMacBook-Pro:bin ac-it002$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 61

Server version: 5.6.17 MySQL Community Server (GPL)


Copyright (c) 2000, 2014, 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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| test |

+--------------------+

2 rows in set (0.00 sec)


mysql>


报错：

创建数据库的时候：

create user newUser;

:  ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation


ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation


解决办法：

都是在终端命令行下面操作的，建议在操作前重新设置一下 path 。

方法一：
1. 关闭 mysql
# service mysqld stop
2. 屏蔽权限
# mysqld_safe --skip-grant-table
屏幕出现：  Starting demo from .....
3. 新开起一个终端输入
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
mysql> FLUSH PRIVILEGES;// 记得要这句话，否则如果关闭先前的终端，又会出现原来的错误
mysql> \q

方法二：
1. 关闭 mysql
# service mysqld stop
2. 屏蔽权限
# mysqld_safe --skip-grant-table
屏幕出现：  Starting demo from .....
3. 新开起一个终端输入
# mysql -u root mysql
mysql> delete from user where USER='';
mysql> FLUSH PRIVILEGES;// 记得要这句话，否则如果关闭先前的终端，又会出现原来的错误
mysql> \q


mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| d0 |

| d7 |

| mysql |

| performance_schema |

| ptsub |

| test |

+--------------------+

7 rows in set (0.00 sec)

mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root';

ERROR 1046 (3D000): No database selected

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> UPDATE user SET Password=PASSWORD('123456') where USER='root';

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0


mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)


mysql> \q




mysql权限丢失只能看见information_schema一个库

真题1、为何root@'localhost'只能看到information_schema一个库？
答案：这是因为用户root@'localhost'权限缺失导致的，可以给于该用户grant option权限即可，赋权命令为：“grant grant option on *.* to root@localhost;”。
C:\Users\Administrator>mysql -uroot -plhr -P3311
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> select user,host,grant_priv,super_priv from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user'
mysql> show grants for 'root'@'%';
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'root'@'localhost';
+--------------------------------------------------------------+
| Grants for root@localhost                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost'                     |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> exit
Bye
C:\Users\Administrator>mysql -uroot -plhr -P3311 -h192.168.59.21
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> select user,host,grant_priv,super_priv from mysql.user;
+-----------+-----------+------------+------------+
| user      | host      | grant_priv | super_priv |
+-----------+-----------+------------+------------+
| root      | %         | Y          | Y          |
| mysql.sys | %         | N          | N          |
| root      | localhost | N          | N          |
+-----------+-----------+------------+------------+
3 rows in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'   IDENTIFIED BY 'lhr' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
--或grant grant option on *.* to root@localhost;
mysql> select user,host,grant_priv,super_priv from mysql.user;
+-----------+-----------+------------+------------+
| user      | host      | grant_priv | super_priv |
+-----------+-----------+------------+------------+
| root      | %         | Y          | Y          |
| mysql.sys | %         | N          | N          |
| root      | localhost | Y          | Y          |
+-----------+-----------+------------+------------+
3 rows in set (0.00 sec)
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'root'@'%';
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
C:\Users\Administrator>mysql -uroot -plhr -P3311
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)
USAGE表示仅仅只有连接MySQL的权限，而没有其它权限。而且，这里的USAGE没有GRANT OPTION的权限，否则依然可以查看所有的数据库。
mysql> show grants for root@localhost;
+--------------------------------------------------------------+
| Grants for root@localhost                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost' WITH GRANT OPTION   |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> revoke Grant option on *.* from root@localhost;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show grants for root@localhost;
+--------------------------------------------------------------+
| Grants for root@localhost                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost'                     |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.00 sec)
重新以root@localhost登陆查看：
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> show grants;
+--------------------------------------------------------------+
| Grants for root@localhost                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost'                     |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)


本人在第一次安装完mysql后修改了root的密码导致了权限丢失，上网查询了多种解决办法，互相整合才解决了我遇到的问题。解决方法如下：

首先，关闭mysql服务，在mysql安装目录下找到my.ini文件，有可能你会遇到下图的情况:
在这里插入图片描述
只需将my-huge.ini文件复制一份重命名为my.ini即可;

打开文件，在[mysqld]项下添加skip-grant-tables语句
在这里插入图片描述
然后保存文件，启动mysql服务。

接下来在cmd窗口登陆数据库，此时因为上一步skip-grant-tables语句的添加使用mysql -uroot -p登陆将会跳过权限检查，所以此时再输入语句show databases；就能看到所有的数据库了。

此时我们再输入如下语句：
use mysql；
update user set Update_priv=‘Y’ where user=‘root’;
update user set Grant_priv =’Y’ where user = ‘root’;//修改root权限
flush privileges;
grant all privileges on  . to root@‘localhost’ identified by “1234” with grant option;//1234为登陆密码
flush privileges;//刷新权限

语句运行成功后退出mysql并关闭mysql服务，再打开一开始的my.ini文件，将添加的语句删除，保存。
此时再次打开mysql服务登陆mysql时就能看到所有数据库了。

[日常填坑]mysql登陆后只出现information_schema和test两个数据库

在服务器只能使用【无密码】登陆，即

要么直接mysql进入数据库，要么mysql -u root -p，然后不写密码回车进入数据库（但是之前一开始有设置密码了！！！ 之前是按这个步骤来的），

然后，登陆后只能看到information_schema和test两个数据库，而且没了很多权限。

另外，能在本地连接远程服务器数据库可以使用root和密码登陆连接，能看到mysql数据库的user表。

也就是说，在服务器的数据库的root已经失去了超级权限！！！

 

原因：

一开始安装mysql的时候没有把localhost的空用户删除。

导致root有两种登录方式，一个是基于localhost的，另一个就是在其它任意主机登录的，

你设置的密码是基于IP地址登录时的密码，而你在本机运行mysql -u root -p时，默认检查的是localhost方式

 

如何解决：

解决具体操作步骤：

第一种方法：

在服务器中，

先关闭mysql：service mysqld stop

然后，mysqld_safe --skip-grant-tables

 

（如果是mariadb，则使用systemctl restart mariadb.service）

打开mysql

删掉已经存在了host为localhost的记录，则先（一定要一开始就！！！）删除该记录，delete  from user  where host= 'localhost';

 

第二种方法：

如果你已经在本地主机远程连接数据库的root用户，

那么直接在mysql.user里面删除localhost的空用户

然后再服务器上面重启数据库服务即可



About Me

........................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（ http://blog.itpub.net/26736162）、博客园（ http://www.cnblogs.com/lhrbest）和个人weixin公众号（ xiaomaimiaolhr）上有同步更新

● 本文itpub地址： http://blog.itpub.net/26736162

● 本文博客园地址： http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群号： 230161599（满） 、618766405

● weixin群：可加我weixin，我拉大家进群，非诚勿扰

● 联系我请加QQ好友 （ 646634621 ），注明添加缘由

● 于 2019-06-01 06:00 ~ 2019-06-30 24:00 在西安完成

● 最新修改时间：2019-06-01 06:00 ~ 2020-02-05 24:00

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

........................................................................................................................

● 小麦苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书： http://blog.itpub.net/26736162/viewspace-2142121/

● 小麦苗OCP、OCM、高可用网络班： http://blog.itpub.net/26736162/viewspace-2148098/

● 小麦苗腾讯课堂主页： https://lhr.ke.qq.com/

........................................................................................................................

使用 weixin客户端扫描下面的二维码来关注小麦苗的weixin公众号（ xiaomaimiaolhr）及QQ群（DBA宝典）、添加小麦苗weixin， 学习最实用的数据库技术。

........................................................................................................................

欢迎与我联系

 

 



请使用浏览器的分享功能分享到微信等