Mysql – Cannot select from new view in MySQL (can select from everything else)

MySQLSecurity

I am a newbie to MySQL, being more accustomed to MSSQL. However, we have recently purchased the LabTech management software, which uses a MySQL back end. While writing a custom report to keep track of the status of the network, I added a new view. The new view works just fine when executed as root, but when running it as a user I get:

Error Code: 1142
SELECT command denied to user 'username'@'remote_address' for table 'new_view'

I am using MySQL Workbench to administer the database and I cannot see anywhere to view the permissions of the view. I have tried

GRANT SELECT ON labtech.new_view TO 'username'@'localhost';
flush privileges;

but that has not helped. What am I missing?

Best Answer

I think I have the same problem as you! When I recover the mysql data, it happens. Then I found its DEFINER user was not the one I have set. Just use:

SELECT DEFINER FROM information_schema.views WHERE table_schema='labtech' LIMIT 10;

to show the DEFINER , you will find it was the older user. To modify it, I think you should look at this link on dba.stackexchange.com.

Related Topic