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?

  • 3
    Did you mean 'username'@'remote_address' in that GRANT command? 'localhost' and 'remote_address' are different users to mysql.
    – DerfK
    Oct 29 '10 at 22:19
  • That was it. I didn't realize that MySQL considers the host as well as the username. Thanks! If you add that as an answer I'll credit you
    – gillonba
    Nov 1 '10 at 15:53
  • But now I can't seem to access anything ELSE in the database as that user
    – gillonba
    Nov 1 '10 at 16:13
  • what does SHOW GRANTS FOR 'username'@'remote_address' tell you? Aug 18 '11 at 15:43

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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.