How can I programmatically grant (and revoke) temporary database access in MySQL?
It depends upon how sophisticated you want to be with your user management. 

In MySQL I would create a small selection of "temporary" users with no privileges, not even to log in. When a user requests access to some part of the database, I would have a script run GRANT statements to allow them access to the resources they need, and have a cron (or other scheduled) script to automatically run the corresponding REVOKE statements.  

In a more sophisticated database (like Oracle), the user can be set to expire automatically after a set period of time. Once their time has expired, they are no longer allowed to log in until you (the DBA) re-enables their account.