I want to be able to replicate parts of a database to a client.
I can separate out the data into a separate table easily enough using triggers and the plan was to have a table for each client and only allow them access to that one table.
However, when I come to do the GRANT I find that the only thing that works is a command like this:
mysql>GRANT REPLICATION SLAVE ON *.* TO 'a'@'b' IDENTIFIED BY 'pwd';
Both of these fail:
mysql>GRANT REPLICATION SLAVE ON mydb.mytable TO 'a'@'b' IDENTIFIED BY 'pwd';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql>GRANT REPLICATION SLAVE ON mydb.* TO 'a'@'b' IDENTIFIED BY 'pwd';
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
It appears that the REPLICATION privilege is a GLOBAL one and as such can only be granted on *.*
So, is there a way I can get this to work?
If not, anyone got an alternative solution?