In my MySQL database I have my regular tables, and a couple of VIEWS.
Everything runs like clockwork, until I remove Remote Access to the database.
I cannot access any of my VIEWS. Accessing any regular tables still work 100%.
The thing is, the PHP script accessing the VIEW is on LOCALHOST. So no remote connection needed.
The error I get is "Access denied for user 'some_username'@'localhost' (using password: YES)
Any insight will be greatly appreciated.
Are you still accessing these view using the same definer that created them ? MySQL uses DEFINER for stored proc's and views and any change to this [may] result in something simalar to that. I personally have not encoutered such but it's worth having a look at MySQL stored procedures security guide in the link below.
MySQL stored procedures security
Hope this helps
Check your priviledge table. Also, check the IP address of the computer which is used to access to the server. Access denied is either from priviledge is not met (not being declared as allow to work with certain databases/tables) or your IP address does not match the one in the priviledge table.
Thanks people. I read that page on "MySQL stored procedures security". Some of the stuff is a bit over my head, but, it turns out that the VIEW has all the privileges of the user who created them, not the user who uses them.
What I did was, I re-created the views as root. Hopefully some clever people can't take advantage of a view and exploit the privileges I gave it.
Still a bit puzzled about the exact reason why it didn't work when I removed remote access to the MySQL server. All access to the database is from PHP on LOCALHOST connecting to the MySQL server also the same machine.
I did initially create the views over a remote connection to the server though.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.