krytenxpert
asked on
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry to have left this but have been developing a solution!
We've thought of the Master->SlaveA->SlaveB->Cu
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!
We've thought of the Master->SlaveA->SlaveB->Cu
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!
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?
Do you have any questions about your proposed solution?
ASKER
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?