Re: Replication and user privileges
On 2/25/2019 5:46 PM, shawn l.green wrote: Hello Jim, On 2/25/2019 5:04 PM, Jim wrote: I have a question about mysql replication. I believe I understand most of it, but have a question about user privileges. I understand on the master, the replication user must have the Repl_slave_priv privilege as described here: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave My question is about what replication-related users and privileges must exist on the slave. So, for example, if an insert on the master that is to be replicated is performed by user 'abc' with proper insert permissions on the master, does that same 'abc' user with same insert permissions need to exist on the slave as well? In other words, what user is performing the replication operation on the slave? I don't see any indication of users referenced in the bin logs that I have examined on the master. Are user and privileges regarding replicated queries irrelevant on the slave and that is handled all internally via the replication thread with no regard to user privileges? Thank you. Jim Your final supposition is correct. All privileges were checked and verified on the master when the original command was executed. The Replication system on the slave is going to repeat that change as well as possible given the state of its copy of the data without regards to "who originally performed this change" on the upstream master. We do not store credentials in the Binary Log because they are not important to either of the purposes of the Binary Log * point-in-time recovery or * Replication (which is very much like an automated, continuous point-in-time recovery) === That replication account you mentioned, on the master, is required to give a slave (and you could have several) enough rights to read the Binary Log and not much else. This allows you to create an account that can login from a remote location with the "least privileges" necessary to do its job. This minimizes your data's exposure should that account become compromised. Many other accounts could also have the REPL_SLAVE_PRIV privilege and any of those could be used by a slave to do the same job. However losing control over one of those more privileged accounts could pose a higher risk to your data. Thanks, Shawn. Your response confirms what I had assumed was happening. So bottom line... what I plan to do is strip the various insert/update/delete privileges from appropriate db users on my slaves. I had placed them there originally because I thought they would be needed for the replicated queries, but not true based on your response. I only want the various mysql users used by my code to have select privs on the slaves so that if somehow a slave was mistakenly written to via a bug in my code, that write would fail and I would receive the error. The slaves should only be used for selects and should never experience a write. That would make sense based on our discussion, correct? Thanks again. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication and user privileges
Hello Jim, On 2/25/2019 5:04 PM, Jim wrote: I have a question about mysql replication. I believe I understand most of it, but have a question about user privileges. I understand on the master, the replication user must have the Repl_slave_priv privilege as described here: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave My question is about what replication-related users and privileges must exist on the slave. So, for example, if an insert on the master that is to be replicated is performed by user 'abc' with proper insert permissions on the master, does that same 'abc' user with same insert permissions need to exist on the slave as well? In other words, what user is performing the replication operation on the slave? I don't see any indication of users referenced in the bin logs that I have examined on the master. Are user and privileges regarding replicated queries irrelevant on the slave and that is handled all internally via the replication thread with no regard to user privileges? Thank you. Jim Your final supposition is correct. All privileges were checked and verified on the master when the original command was executed. The Replication system on the slave is going to repeat that change as well as possible given the state of its copy of the data without regards to "who originally performed this change" on the upstream master. We do not store credentials in the Binary Log because they are not important to either of the purposes of the Binary Log * point-in-time recovery or * Replication (which is very much like an automated, continuous point-in-time recovery) === That replication account you mentioned, on the master, is required to give a slave (and you could have several) enough rights to read the Binary Log and not much else. This allows you to create an account that can login from a remote location with the "least privileges" necessary to do its job. This minimizes your data's exposure should that account become compromised. Many other accounts could also have the REPL_SLAVE_PRIV privilege and any of those could be used by a slave to do the same job. However losing control over one of those more privileged accounts could pose a higher risk to your data. -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Replication and user privileges
I have a question about mysql replication. I believe I understand most of it, but have a question about user privileges. I understand on the master, the replication user must have the Repl_slave_priv privilege as described here: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave My question is about what replication-related users and privileges must exist on the slave. So, for example, if an insert on the master that is to be replicated is performed by user 'abc' with proper insert permissions on the master, does that same 'abc' user with same insert permissions need to exist on the slave as well? In other words, what user is performing the replication operation on the slave? I don't see any indication of users referenced in the bin logs that I have examined on the master. Are user and privileges regarding replicated queries irrelevant on the slave and that is handled all internally via the replication thread with no regard to user privileges? Thank you. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
