“Access denied for user” on views only – after MySQL database restore

We encountered this issue when moving the database from the old server to the new one. Everything looked fine, there was access to tables (select, insert, update, delete) but when trying to access views, such error occurred:

[2022-03-09 09:52:54] [28000][1045] Access denied for user 'DBUser'@'%' (using password: YES)

It surfaced, that views were defined using a user account that does not exist on the new server:

create definer = OldServerUser@`%` view MyViewName as
select something, somethingElse, oneMoreThing
from `myTable`
where something = somethingElse;

Have you noticed that in the View definition, there is “definer” mentioned? This definer is the name of the user who created this view. When moving views from one server to another, we have to make sure that the definer user is created on the new server. This user should also have privileges to access tables and databases required by the view.

Leave a Reply

Your email address will not be published.