Solved

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

Posted on 2006-10-27
10
759 Views
Last Modified: 2012-06-27

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?
0
Comment
Question by:krytenxpert
10 Comments
 
LVL 17

Accepted Solution

by:
akshah123 earned 125 total points
Comment Utility
>>>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
 
LVL 6

Assisted Solution

by:chigs20
chigs20 earned 125 total points
Comment Utility
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
 
LVL 35

Assisted Solution

by:Raynard7
Raynard7 earned 125 total points
Comment Utility
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
 

Author Comment

by:krytenxpert
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 35

Expert Comment

by:Raynard7
Comment Utility
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
 
LVL 8

Assisted Solution

by:lokus
lokus earned 125 total points
Comment Utility
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
 

Author Comment

by:krytenxpert
Comment Utility
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
 
LVL 35

Expert Comment

by:Raynard7
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now