Re: Replication and user privileges

Hello Jim,

On 2/25/2019 7:29 PM, Jim wrote:
> 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
>

As masters and slaves can exchange "positions" or "roles" (it depends on 
how you like to mentally visualize the relationship) within a 
replication graph in a failover situation, adding time to re-establish 
actual permissions using GRANT commands to reset user accounts to their 
old privileges may not be time you want to spend.

A cleaner, simpler solution is to set the --super-read-only flag in the 
server:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only

That way, you get the behavior you want (no writes to a read-only slave) 
without forcing differences to the content of your privileges tables 
within different nodes of your Replication setup.  Each node will remain 
a transactionally consistent copy of all the others (within the temporal 
limits of replication being an asynchronous process).

Yours,

-- 
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