MySQL Block User Access to Certain Tables – How to Implement

access-controlMySQLSecurity

DBMS allows the admin to grant table access to a user using something like:

GRANT ALL ON mydb.mytbl1, mydb.mytbl2 TO 'someuser'@'somehost';

However, is it possible to grant all tables to a user and explicitly block access to some of them? For example (The BLOCK key word is a fake one and is only used for illustration.):

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
BLOCK ALL ON mydb.mytbl3 TO 'someuser'@'somehost';

Another idea is to add a where clause to the GRANT statement. However, it seems that the GRANT statement syntax does not allow that (http://dev.mysql.com/doc/refman/5.1/en/grant.html).

This kind of access control might be convenient if the admin trust the user in most cases (e.g. 1000 tables) and only want to block access to a small number of top sensitive tables.

Could you please tell me whether such kind of mechanism exists?

Best Answer

I've had to solve this specific problem and realized there's not way to say "all tables except these".

What I did to work around this was create an internal database, restricted_tables.

It had a couple of simple table schema:

CREATE TABLE `tables` (
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `authorized_users` (
  `authorization_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user` char(16) NOT NULL,
  `table_schema` char(64) NOT NULL,
  `table_name` char(64) NOT NULL,
  `authorization_ticket` char(16) NOT NULL,
  `authorization_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `authorization_expiration` date DEFAULT NULL,
  PRIMARY KEY (`authorization_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The tables table you see is just a pair of table schema (db name) and table name you wish to exclude for the "everything but these" list.

The authorized users tables came in a second iteration to allow certain users access to otherwise default restricted tables. Some other columns there include a ticket column which is a reference to our internal ticketing system for audit trail purposes, and a couple of date columns for create and expire.

I then have a user maintence script which runs a query like

$q="select i.table_schema, i.table_name from information_schema.tables i left outer join
             restricted_tables.tables r using(table_schema, table_name)
                                                 where
            i.table_schema not in (".buildEscapedList($db,$excludedDbs).") and r.table_schema is null
        union
        select table_schema, table_name from restricted_tables.authorized_users where user=?";

(This is a perepared statement written in PHP if that helps). The $excludedDbs was just a list of schemas to completely ignore like mysql, information_schema, etc.

It then starts by running a "revoke all privileges" for the user (this just leaves the usage user@host identified by '' entry remaining.

It then loops through and grant select on every table getting returned by that query.

This effectively implements your everything but these

Things that suck

When a new table is added it's not explicitly included for users as a db.* would be. To mitigate that a version of the script runs on a cron to periodically readd grants to these users.

It makes looking at a show grants for user@host a little daunting to quickly grok what a user does or doesn't have access to (but nothing a little grep can't solve)

Known limitations

The script was purpose built for a scenario where accounts being managed by this would only need select access so there's no provisioning for various other CRUD privs per table

Alternate Solution

If you have a subset of tables in mydb that are restricted then move only those tables into a mydb_restricted databases. Then only can keep db.* style grants, giving mydb_restricted.* access to the privileged accounts.

Related Question