Home / server / Questions / 196330
In Process
gillonba
Asked: 2010-10-30 13:50:42 +0800 CST

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

  • 772

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?

mysql security

2 Answers

  1. 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.

    • 2

  2. Make sure that you recognize that mysql recognizes the address as part of the username.

    • 1