mysql 导出 error 1143_mysql忘記誰登錄:命令拒絕用戶“@”%

Running show grants; indicates that I am logged in as a user with all privileges on a database.

運行顯示撥款;指示我作為數據庫上具有所有特權的用戶登錄。

Running show table status; results in an error. And the error does not show the username I am logged in as!

運行顯示表狀態;會導致錯誤。並且錯誤沒有顯示我登錄的用戶名為as!

It's as if, for this command, mysql forgets who I am. Other select statements work fine. Can anyone explain this? How to fix? Thanks.

對於這個命令，mysql好像忘記了我是誰。其他選擇語句工作正常。誰能解釋這個?如何修復?謝謝。

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

Your MySQL connection id is 2

Server version: 5.5.13-log Source distribution

Copyright (c) 2000, 2010, 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 grants;

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

| Grants for php@localhost |

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

| GRANT ALL PRIVILEGES ON *.* TO 'php'@'localhost' IDENTIFIED BY PASSWORD '*8F5FF90079BC601F8EA7C148475658E65A0C029D' |

| GRANT ALL PRIVILEGES ON `sunflower_work`.* TO 'php'@'localhost' |

| GRANT ALL PRIVILEGES ON `news_demo`.* TO 'php'@'localhost' |

| GRANT ALL PRIVILEGES ON `news_base`.* TO 'php'@'localhost' |

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

4 rows in set (0.00 sec)

mysql> show table status from sunflower_work;

ERROR 1143 (42000): SELECT command denied to user ''@'%' for column 'uid' in table 'users'

mysql>

update... as suggested by Tomalak, I deleted the user and recreated with fuller privileges and no password. Still the problem persists. Now it looks like this:

更新……按照Tomalak的建議，我刪除了用戶，並重新創建了更完整的特權，沒有密碼。仍然存在的問題。現在看起來是這樣的:

mysql> show grants;

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

| Grants for php@localhost |

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

| GRANT ALL PRIVILEGES ON *.* TO 'php'@'localhost' |

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

1 row in set (0.00 sec)

mysql> show table status;

ERROR 1143 (42000): SELECT command denied to user ''@'%' for column 'uid' in table 'users'

mysql>

2 个解决方案

#1

20

The issue is probably that you have VIEWS in your database. The views are probably created with specific rights.

問題可能是您的數據庫中有視圖。視圖可能是用特定的權利創建的。

As you can tell by your error message, it complains about a different user than the one you are logged in is. This is because for a view you can specify how to determine what rights the view has to look at data.

從錯誤消息中可以看出，它會對不同於登錄的用戶進行抱怨。這是因為對於視圖，您可以指定如何確定視圖查看數據的權限。

When you go to your database, try typing:

當你進入數據庫時，試着輸入:

SHOW FULL TABLES IN sunflower_work WHERE TABLE_TYPE NOT LIKE '%table%';

Then you may wish to look into the rights of the specific views that are there.

然后，您可能希望查看那里的特定視圖的權利。

#2

5

The answers here helped me with my specific problem. Many thanks! A view was the culprit as described above.

這里的答案幫助我解決了我的具體問題。很多謝謝!如上所述，一種觀點是罪魁禍首。

I got into trouble because the database in question was created from a backup of a remote database which had different users. The 'broken' view was 'defined' by a user I didn't have locally. Even root was unable to run the crashing query.

我遇到了麻煩，因為問題數據庫是由一個擁有不同用戶的遠程數據庫的備份創建的。“broken”視圖是由我在本地沒有的用戶定義的。甚至root也無法運行崩潰查詢。

Changed the view's 'DEFINER' to a valid local user and the problem was solved!

將視圖的“定義者”更改為有效的本地用戶，問題就解決了!

ALTER

DEFINER = 'a_valid_user'@'localhost'

VIEW my_view

AS

SELECT .....

查看關於MySQL 5.5的ALTER VIEW文檔

Many thanks again!

多謝了!