Ain’t No User Here

Here’s a new one. I was debugging a problem with a web server that hadn’t been used in a while that was allowing users to log in, but failing to perform a number of other functions (gotta love an API that happily returns a 200 OK even though it’s clearly not working because there’s no data in the response). No one could think of anything that had changed, but clearly something had.

After some spelunking through logs, I found some DB permissions errors. Obviously some DB calls were working, so that was odd, as I knew credentials were only specified in one place.

Another hour of research later, I discovered that MySQL views run as the user who created them, not the user who queries them. This actually makes sense for use cases where you want to give controlled read access to parts of the DB to a less-privileged user. Logically then, if that user is deleted, the view fails to run. And in this situation that’s exactly what happened.

There’s a number of lessons to be learned from this situation:

1. A user probably should fail to delete if it owns other objects in the database. Otherwise unexpected side-effects occur.
2. Accurate error messaging is essential to debugging. When trying to run the view, I got a simple “user does not have permission” error, which told me nothing about the underlying problem, which meant another hour or two of research. This goes for APIs especially. Please know when to use a 4XX vs. 5XX error especially, and even better learn the subtle differences between 502, 503, and 504, and when each should be used.
3. Don’t try to diagnose problems on Friday afternoons before holiday weekends. You’re asking for trouble.