Menu
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.
339
8. What cannot be done on a view? Explanation: In MySQL, 'Views' act as virtual tables. It is not possible to create indexes on a view.
The basic syntax for creating a view in MySQL is as follows: CREATE VIEW [db_name.] view_name [(column_list)] AS select-statement; [db_name.] is the name of the database where your view will be created; if not specified, the view will be created in the current database.
In MySQL, views are not only query-able but also updatable. It means that you can use the INSERT or UPDATE statement to insert or update rows of the base table through the updatable view. In addition, you can use DELETE statement to remove rows of the underlying table through the view.
To add super privileges to MySQL database, the following is the syntax. mysql> GRANT SUPER ON *. * TO user@'localhost' IDENTIFIED BY 'passwordName'; After executing the above query, do not forget to end it with the following command.
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.
123
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
