Why not allow arbitrary SQL queries on most of the database tables?
Let's see, only a few, like the user table, have much confidential information, and even only a few columns of it too.
So api.php could drop its read privileges for (parts of?) that table before running any queries.
Motivation example:
It comes the time when all websites should check for link lint. OK, so I need a list of external links that are present in my wikis.
$ echo "SELECT DISTINCT el_to FROM wiki_externallinks ORDER BY el_to;"| mysql -B my_database gets it for me all with one command.
Can api.php get all the external links, for all namespaces, all in one shot? Can Special:Linksearch get them all either, all in one shot?
The sysop could also customize what tables/columns to restrict, and how many rows to output. Also set the total row output limit too.
No need for only allowing SELECT, as api.php would first drop all other privileges than read-only privileges, including the privilege to GRANT its privileges back to itself... No need to even filter against SQL injection attacks (but as I don't even know how to spell SQL, don't quote me on that.)
Anyway, being able to do arbitrary SQL would greatly simplify many api.php queries. Let's see, for the URL perhaps use: api.php?sql=SELECT+DISTINCT... (maybe use no CAPS in the examples to "sell the ease of the idea".)