Multiple scripts in MySQL Proxy

MySQL Proxy is being beefed up, to make it ready for prime time. New features are being added, and old ones are improved and tested.
Testing the Proxy is an adventure in its own right. It's a tool that was designed to get in the middle of the scene and change things. Testing its features is more challenging than testing a straightforward application, even more complex like a database server. So the test suite for MySQL Proxy is much more complex and feature rich than the corresponding suite for the database server.
While researching to create the next edition of the test suite, able to cope with more features and corresponding testing requests, I developed a Lua script that enhances the current Proxy features and allows you to load several scripts, and use them all at once.
The script (load-multi.lua) is available in the latest Subversion tree. The usage is not difficult.
From any client, you need to send a query

PLOAD script_name;

The script you indicate will then be available to all the clients.
If your script contains functions that are used at session start (connect_server, read_handshake, read_auth, read_auth_result), they will be available when the next client connects. If your script uses read_query and read_query_result, they will be available immediately.
The mechanics of how this works is simple. You load one or more script containing one or more of the above mentioned functions that hook to the Proxy, and the load-multi module will stack each function in a list. For each hook, the load-multi module loops through the loaded functions, executes each one and tests the result. If the function returns a non null value, then that value is passed back to the Proxy.

This means that if you have two scripts that can handle a particular query, only the first one that has been loaded will get a chance to evaluate the query.
When loading modules, you must check the order in which you are loading them. If you load first a script that handles every query, such as a logging application, subsequent scripts would be just filling memory.
There are more goodies.
Your scripts can use some global functions that load-multi prepares for you. Each of these routines is in the proxy.global namespace, so they are available to all clients and their loaded scripts.
One first useful application that can be plugged to this module is a script that lists the status of loaded functions:

-- show_handlers.lua
function read_query (packet)
if packet:byte() ~= proxy.COM_QUERY then
 return
end
local query = packet:sub(2)
  if query:match('select pload status') then
      local header = { 'module', 'handler' }
      local rows = {}
      for id, lmodule in pairs(proxy.global.handler_status) do
          for i,h in pairs(lmodule) do
              table.insert(rows, { id, h.func } )
          end
      end
      return proxy.global.make_dataset(header,rows)
  end
end

Load this script with PLOAD show_handlers, and any client will be able to get a list of modules, with the function that each one introduced.

pload show_handlers;
+-------------------------------+
| info                          |
+-------------------------------+
| module "show_handlers" loaded |
+-------------------------------+
1 row in set (0.01 sec)

pload auth1;
+-----------------------+
| info                  |
+-----------------------+
| module "auth1" loaded |
+-----------------------+
1 row in set (0.03 sec)

select pload status;
+---------------+-------------------+
| module        | handler           |
+---------------+-------------------+
| show_handlers | read_query        |
| auth1         | read_auth         |
| auth1         | disconnect_client |
| auth1         | read_handshake    |
| auth1         | connect_server    |
| auth1         | read_auth_result  |
+---------------+-------------------+
6 rows in set (0.01 sec)

This new module has the ability of extending its own behavior. If you like playing with new features, you should try this one!

Blocking specific queries

Imagine having a database with 100 tables and you want to allow a user to read from all of it, except one.

But of the table of the knowledge of good and evil, thou shalt not select of it

That's an old problem, with an ugly solution. You must either move the forbidden table to another database or to explicitly authorize the user to read each one of the other 99 tables.

A friend of mine had a similar problem. She has a huge database with thousand of tables, and she wants to prevent the users from issuing a "SHOW TABLES" command. Why? Because, with thousands of tables, the response time of MySQL can easily become very slow. It is a design problem, not easily solvable, and the best course of action here is to deny access to this command.

The bad news is that MySQL has no provision for this kind of restrictions.
The good news is that you can solve this problem with an easy MySQL Proxy script.
And you don't even need to write it. Just download the appropriate script from MySQL Forge and use it.
It's quite easy to customize. The interesting part is in the following lines:

local SHOW_REGEXP       = make_regexp_from_command('show')

queries_to_filter = {
  {
      prefix = SHOW_REGEXP,
      keywords = { 'SHOW', 'TABLES'} ,
  },
}

SHOW_REGEXP is a variable containing a regular expression built from the command you want to consider. For performance reasons, before tokenizing every query, a quick search is pergormed, and only if that search is positive the query is analyzed further. In this case, the script will first check if there is a 'SHOW' at the start of the query. Then it will tokenize the query, and check if all the elements (SHOW and TABLES) are present. The tokenizer can separate literal values from strings. Therefore, if your query contains something like SELECT "SHOW TABLES" as X, it won't block the query.
To make the restriction more robust, there are also rules for 'SHOW TABLE STATUS', 'SELECT FROM INFORMATION_SCHEMA.TABLES", and "EXECUTE" (to prevent smart users from using prepared statements as a workaround).

mysql> show tables;
ERROR 7777 (X7777): command <SHOW TABLES> is  not allowed
mysql> show table status;
ERROR 7777 (X7777): command <SHOW TABLE STATUS> is  not allowed
mysql> select * from information_schema.tables;
ERROR 7777 (X7777): command <SELECT FROM INFORMATION_SCHEMA TABLES> is  not allowed

mysql> select schema_name from information_schema.schemata;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

Now, if you want to prevent someone from accessing the good_and_evil table, you can do the following:

local SELECT_REGEXP       = make_regexp_from_command('select')

queries_to_filter = {
  {
      prefix = SELECT_REGEXP,
      keywords = { 'SELECT', 'FROM', 'GOOD_AND_EVIL'} ,
  },
}

And your beloved table becomes taboo.
Try it!