CREATE TABLE secretData ( secretValue int COMMENT 'If this goes over 5, WWIII will start' ); CREATE SQL SECURITY DEFINER VIEW censoredData AS SELECT * FROM secretData WHERE secretValue < 5 WITH CHECK OPTION; GRANT SELECT, INSERT ON test.censoredData TO 'evilFiend'@'%';
<telephone> ring ring
<sysadmin> "Hello?"
<evilFiend> "I'd like to create an insertable view on some tables I already have rights to. I don't know just yet what I'll use for my select statement."
<sysadmin> "Ok. I'll set it up so you can do what you'd like."
CREATE SQL SECURITY INVOKER VIEW evilFiendsView AS SELECT 1; GRANT SELECT, INSERT, ALTER ON test.evilFiendsView TO 'evilFiend'@'%';
evilFiend connects to the server, while twiddling the end of his handlebar mustache.
ALTER VIEW evilFiendsView AS SELECT * FROM censoredData WITH LOCAL CHECK OPTION; INSERT INTO evilFiendsView VALUES (42);
Muhahaha!