CODEWITHSUNDEEP
mysqlamazon-ec2
ricick
ricick

Reputation: 5766

Mysql users can select from tables but not views

I have a mysql database on which I create users, and grant them select, update, insert and delete on a particular database.

The database contains both tables and views. For some reason these created users get the following error when trying to select from a view.

Access denied for user 'someuser'@'%' (using password: YES)

SELECT * FROM (`someview`) WHERE `somecolumn` = 0

I don't however get this error when selecting from a table in the same database.

I have deployed this application to multiple places and it's working everywhere else including using amazon rds. this particular instance is a mysql server running on amazon ec2.

Upvotes: 20

Views: 7681

Answers (2)

ricick
ricick

Reputation: 5766

Solved it. The error message is misleading.

The cause was the user who was the definer of the views no longer existed, not the user who was selecting from the view.

I re-created this user and all is well.

Upvotes: 46

John Woo
John Woo

Reputation: 263733

There are possible reasons why you encountered this:

  • It may be the user you have used to access this does not have enough privilege on this particular table.
  • It may be the username or password doesn't match.
  • It may be the user doesn't exist for the specified host.

Upvotes: 0

Related Questions