TIL: MySQL view security context after a migration

Background: I had set up a MySQL view on a server. Then done a server migration by doing the classic dump and import dance.

After the migration I was suddenly unable to access the view, getting errors like this:

SQLSTATE[28000]: Invalid authorization specification: 
1045 Access denied for user '<user>'@'<host>' (using password: YES) 
(Connection: mysql, Host: <host>, Port: 3306, Database: <database>,
SQL: [SELECT * FROM `view`])

My initial thought was that I had made a mistake when defining the users or GRANTS on the new host, but no:

mysql> SELECT user, host FROM mysql.user WHERE user = '<user>';
+-----------+----------+
| user      | host     |
+-----------+----------+
| <user>    | <host>   |
+-----------+----------+

mysql> SHOW GRANTS FOR '<user>'@'<host>';
+----------------------------------------------------------------+
| Grants for <user>@<host>                                        |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `<user>`@`<host>`                        |
| GRANT ALL PRIVILEGES ON `<database>`.* TO `<user>`@`<host>`    |
+----------------------------------------------------------------+

That’s when I learned about SQL SECURITY for views. It lets you set the security context that a view or stored program runs under.

I had originally created the view with:

CREATE VIEW `view` AS
-- view definition here

The default security context for views is DEFINER, and the default definer is whichever account ran the CREATE VIEW. I’d done that on the old host, pre-migration, and mysqldump writes the DEFINER clause out into the dump, so it came across to the new server unchanged:

mysql> SELECT DEFINER, SECURITY_TYPE
    -> FROM information_schema.VIEWS
    -> WHERE TABLE_SCHEMA = '<database>' AND TABLE_NAME = '<view>';
+-------------------+---------------+
| DEFINER           | SECURITY_TYPE |
+-------------------+---------------+
| <user>@<old-host> | DEFINER       |
+-------------------+---------------+

See that? <user>@<old-host> is the definer, and the view runs in that account’s security context. That account doesn’t exist on the new host, so the view is what MySQL calls an orphan.

The fix is then quite straightforward:

CREATE OR REPLACE SQL SECURITY INVOKER VIEW `view` AS
-- view definition here

I chose to change the security context to INVOKER rather than repointing the definer at the new account. With INVOKER, MySQL never consults the definer account: it runs the view with the calling connection’s own privileges, which in my case is ALL PRIVILEGES on the database. The trade-off is that the invoker now needs privileges on the underlying tables too, not just on the view itself, so INVOKER isn’t the right call if you’re using views to expose a restricted slice of a table to an account that shouldn’t see the whole thing.