Assign permissions to a user on tables with a certain prefix

Question

I'd like to assign a user all privileges on tables which have a specific prefix, eg. 'abc_'

I'm aware of the use of the wildcard to select all tables thus:

GRANT ALL ON dbname.* TO ...

Essentially, what I'd like to do is:

GRANT ALL ON dbname.abc_* TO ...

This doesn't work so I'm wondering if there is a solution, perhaps using LIKE? (Which I've tried; as yet to no avail).

Answer 1

Nope, sorry. Have to do them one at a time (with, of course, the option to do so programatically).

Answer 2

$ php -f pcre_grant.php -- localhost root password database user1 abc_ ALL


pcre_grant.php will look like:

<?php

list($script, $db_host, $db_username, $db_password, $db_name, $regexp, $username, $perms) = $argv;

$link = mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name);


$result = mysql_query("SHOW TABLES");
while($row = mysql_fetch_row($result)){
    if(preg_match('/'.$regexp.'/',$row[0]))
     mysql_query("GRANT ".$perms." ON `".$db_name."`.`".$row[0]."` TO ".$username);
}

mysql_close($link);
?>