Shulou(Shulou.com)05/31 Report--

MySQL show fileds from example analysis without permission. For this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more small partners who want to solve this problem find a simpler and easier way.

Background:

MySQL-5.7.12, show fields error in development environment;

Scene:

the business side reported that it had reported an error in executing the show fields command.

In the test environment, the error scenario is constructed, and the actual error content is similar to:

Click here to fold or open

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

intuitively, it's easy to see that it's a problem of authority.

But there is one doubt:

The column for users reporting error content is empty;

Analysis:

The first thing to be sure is that this is caused by permissions, which is obvious in the error message;

The second point is that the user bar is empty, which may mean that the user does not exist, or there may be some problems with the associated user information, resulting in failure to display;

Check:

1. Check the login user's permissions, log in to the online environment, find that the business account does report an error, and then change to the root account is the same...

The business account has DB corresponding to View, and ALL permission corresponding to DB in Select of View, not to mention root, which means that the error related to this permission is not caused by the login user;

2. Since it's not a question of logged-in user permissions, look at the permissions of the view itself;

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Related information:

In MySQL-5.7, permissions for views are specified when the view is created. For example, in the test environment, the creation statement of the problematic view:

Click here to fold or open

CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`%` SQL SECURITY DEFINER VIEW test_view_xxx_roles AS select xxx;

Red:

Defines the creator (definer) of this view, which means that the permission used when this view is created is provided by `test`@`%`;

Green section:

Define which user to check when accessing this view, when verifying permissions, DEFINER or INVOKER;

------------------------------------------------------------------------------------------------------------------------------------------------------------------

From the relevant information can be learned, show fields from view_stack will report no permission error, because according to SQL SECURITY to verify the user's permissions, found that the permissions are gone;

From the content of the view, just need the select permission of the corresponding library, then look at the current library, create this view of the test user permissions:

Yes, it's true. In the development environment, the user corresponding to this view has disappeared..._ (:з」∠)_

Then, after creating this user, see if it will not report wrong:

as expected, as long as this definer is added, there will be no more false reports.

Treatment:

The final processing method is to modify the definer user of the relevant view (super permission is required to modify this field of view);

Important additional information:

Strangely enough, this phenomenon cannot be reproduced in a "clean" library. This problem occurs only when the database of the development environment is imported into the test environment. There may be some other factors affecting the processing logic of mysql;

About MySQL show fileds from tips for example analysis without permission The answer to the question is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts, you can pay attention to the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.