Monday, November 19, 2007

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 = {
keywords = { 'SELECT', 'FROM', 'GOOD_AND_EVIL'} ,

And your beloved table becomes taboo.
Try it!

No comments: