2.6.1.3.6. When IP access restriction is enabled, access to Views is lost

Reason

When creating a View, by default DEFINER = example_db@% (user with access from any IP) is fixed in it. The access restrictions settings do not matter.

After enabling access restriction, the user from this DEFINER has no access to SELECT queries (he can connect to the database, but without access to tables). Because of this View stops working and when trying to open it, it causes an error like "#1143 - SELECT command is denied to user 'example_db'@'%' for column 'column_name' in table 'tbl_name'".

Solution

Two ways of solving the problem are available:

  • Disable and do not use access restrictions.
  • Recreate View with the SQL SECURITY INVOKER flag.

SQL query to recreate View: 

ALTER SQL SECURITY INVOKER VIEW view_name AS SELECT * FROM tbl_name;

Replacing DEFINER with INVOKER helps because it starts using the privileges of the user who connected (and he can only connect from the IP address from which he has access).

Content