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