How can I restrict on the MASTER what a slave can replicate?


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?
krytenxpertAsked:
Who is Participating?
 
akshah123Connect With a Mentor Commented:
>>>It appears that the REPLICATION privilege is a GLOBAL one and as such can only be granted on *.*
Yes, that is because mysql basically grants the replication user permission to the binlog and cannot specify which exact lines of the binlog will the replication user see.

The only thing here you can do is setup your client's machine with options to only lookat a particular table

http://dev.mysql.com/doc/refman/5.0/en/replication-options.html

However, this is of course not secure since your client can simply update the my.cnf file on their server to basically start looking at everything.  
0
 
chigs20Connect With a Mentor Commented:
So, is there a way I can get this to work?
No, a mysql slave uses two threads. One to get updates from the master's binlogs and a second to execute the statements once its received them. My guess is, MySQL AB didn't want to have many replication threads for each slave.

If not, anyone got an alternative solution?
The only level of granularity mysql allows is on the database level (not the table level)
http://dev.mysql.com/doc/refman/5.0/en/replication-rules.html
0
 
Raynard7Connect With a Mentor Commented:
Hi,

There is an alternative solution - you can set on the slave what data it should replicate and what it should not.
This is done through

replicate-do-table
replicate-ignore-table
replicate-do-db
replicate-ignore-db

http://www.databasejournal.com/features/mysql/article.php/10897_3355201_2

The only catch with this is all the binary logs are sent over - but then only particular parts are executed.  I'm thinking however it is a much better alternative than the others.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
krytenxpertAuthor Commented:
Thanks for the replies so far.

akshah123/Raynard7 - I know the slave can restrict what it replicates but these are _client_ machines over which we have no access and there is no way I would trust them to only grab what we told them too!  I need a way to restrict this on the master!

chigs20 - I'd also found that a MASTER can restrict at the database level however there is no way to restrict what a SLAVE can replicate as the privilege is given to '*.*', so all tables in _any_ database that is able to be replicated can be grabbed by all slaves.  If we setup a client1 database and a client2 database there is _nothing_ to stop client2 replicating everything and seeing the client1 data!

We already use multiple SLAVEs for various purposes (and the master in this case is actually going to be one of those slaves) but all the current machines are under our control and so the restrictions on what is replicated being enforced on the slave has not been an issue.

The problem is that we want to achieve a real-time link for data updates to multiple clients and none of them should be able to see each other's data (or our master copy).

I'm struggling to see a decent solution to this one.  In theory we could setup a mysql instance for each client however that will mean we can't offer this to all clients as there are too many to be able to run a separate instance for each.

As replication seems a definite no-no, has anyone got any alternative solutions for this type of problem?

I guess the only real option that comes to mind is modifying the original plan - use triggers to populate a client1 table and a client2 table and then give each client a username/password that is only allowed to access that table (I assume that user permissions for per-table access actually work?).  Then they can then retrieve the rows (and then I guess delete them once done) though it does rely on them doing the right select/update/delete and is prone to errors.

Are there any third party 'mirroring' type add-ons for MySQL?
0
 
Raynard7Commented:
The permissions do work if you wanted just to grant them access to a particular table you could do

grant select on schemaName.tableName to 'userName'@'%'

which would give the user userName select privilidges on the table called "tableName" which is nlocated in the "schemaName" schema.

I'm not sure about the 'mirroring' options for mysql and have not heard of them.

normally the practice is to have clients access their data through a web front end if they need live data - else they can work with a "reporting copy" where a nightly data extract pulls their relevant data down from the server and si sent securely to their location so they can restore the information themselves.

Similarly depending on their needs you may be able to schedule regular reports to run and give them access to those reports - although this sounds like a simplistic approach generally your clients will only want to look at certain pieces of information anyway and do not really need access to the raw data.

I agree there does not sound like there is a neat or elegant solution to this problem.

Also another note I would not give your users update or delete privledges onto your data - if anything needs to be added to the tables you should give them a tool to enable this to happen - you will get too many headaches if you start letting people who do not know what they are doing access to modify data in your database.  viewing what is there is different - they can not really hurt you with that one, but once people have unfettered access you get major problems - if you have built a tool for them to update records with you at least know that what they are doing has been tmpered by the logic in your code
0
 
lokusConnect With a Mentor Commented:
You can use another instance on the master server as a relay.

master -> relay (same server)   --->  slave  (remote)

On the relay, use the replicate-*, and enable binlog and log-slave-updates. Let the real slave to replicate from the relay instead of the real master.


The other method is to use binlog-do-db and so on.
But, this is bad because you will lose the information in binlog to do recovery.
0
 
krytenxpertAuthor Commented:
Sorry to have left this but have been developing a solution!

We've thought of the Master->SlaveA->SlaveB->Customer setup but for many customers that's a lot of maintenance (and more importantly masses or replication as the 'main' data table would be replicated to multiple slaves again and again).

We've actually gone for a Slave that breaks out the data into a customer specific table (one for each customer) and then we give each customer a login that lets them select and delete on that table only so they can grab records and then delete them.

This way we only have one extra copy of 'all customer' data.

Not sure I can really give points to anyone - if you feel I should then let me know!
0
 
Raynard7Commented:
I'd suggest that you go with the split suggested above, all suggestions were valid the fact they were not 100% what you implemented does not make them less valid.

Do you have any questions about your proposed solution?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.