3

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?

3

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.

2

For MySQL in addition to the GRANT command, there is also a REVOKE command. However, REVOKE has limitations.

If you GRANT ALL ON mydb.* TO 'someuser'@'somehost' you cannot remove one table by running REVOKE ALL ON mydb.mytbl3 TO 'someuser'@'somehost'.

This is because REVOKE will only remove rights as they are granted. That is, you could:

REVOKE ALL ON mydb.* TO 'someuser'@'somehost' 

Of course you could create scripts that would automatically GRANT access to each table individually. Then you could have a special script that would REVOKE the table access that you need to remove from 'troublesomeuser'@'somehost'.

Note that the most recently run command, whether GRANT or REVOKE is the command that is in effect.

There is no DENY command in the MySQL syntax. This is a feature defined in some other SQL implementations. However, denying rights has its own pitfalls that many have stumbled over.

1

Google's MySQL tools provide support for this: https://code.google.com/p/google-mysql-tools/source/browse/#svn%2Ftrunk%2Fpermissions_lib

It's a really complex solution; you have to transform your SQL permissions into a domain-specific Python language, but then it offers flexibility like revoking databases/tables/columns, templating, etc. You run the Python file through an interpreter that connects to the database to list databases/tables/columns and pushes permissions to it or dumps SQL output.

  • This is an interesting solution! Thanks for sharing. – ZillGate Jun 27 '14 at 1:04
0

This could be achieved using a stored procedure that queries for the table names, excluding the sensitive ones and then looping them and setting the Grant on each.

Also checkout Roles for easily assigning the same Grant to multiple users.

https://dev.mysql.com/doc/refman/8.0/en/roles.html

There are better examples at that link but for your use case perhaps:

CREATE ROLE 'app_admin', 'app_user';

GRANT ALL ON app_db.* TO 'app_admin';
GRANT ALL ON app_db.table1, app_db.table2 TO 'app_user';

GRANT 'app_admin' TO 'admin1'@'localhost';
GRANT 'app_user' TO 'user1'@'localhost';

SET DEFAULT ROLE 'app_admin' to 'admin1'@'localhost';
SET DEFAULT ROLE 'app_user' to 'user1'@'localhost';

The default role is so it works on every connection.

Also don't miss the mandatory roles feature.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.