Re: Replication and user privileges

On 2/26/2019 1:57 PM, Jim wrote:
> On 2/26/2019 9:44 AM, shawn l.green wrote:
>> 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,
>>
>
> Thanks, Shawn.
>
> super-read-only looks perfect for what I want. I can keep my slaves 
> with all the potential users needed to take over as master without 
> risking unwanted writes.
>
> Given how you read:
> "If the |read_only| 
> <https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only> 
> system variable is enabled, the server permits client updates only 
> from users who have the |SUPER| 
> <https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super> 
> privilege. If the |super_read_only| 
> <https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only> 
> system variable is also enabled, the server prohibits client updates 
> even from users who have |SUPER| 
> <https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super>."
> One somewhat gets the impression that in order to enable 
> super_read_only, one must also enable read_only.
>
> However, based on:
> https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only 
>
> I see:
> "Setting |super_read_only| 
> <https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only> 
> to |ON| implicitly forces |read_only| 
> <https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only> 
> to |ON|. "
> So that reinforces that one only needs set super_read_only.
>
> Is it appropriate to configure this in my.cnf, eg:
> super-read-only=ON
>
> Thanks again for the incredibly helpful suggestion.
>
> Jim
>
>

Shawn,

We tested your super-read-only suggestion today. It worked perfectly. It 
allowed replication while blocking any direct writes to the slave.

Thanks again for the help.

Jim

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql