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