date:20190226

Re: Replication and user privileges

2019-02-26 Thread Jim

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|

system variable is enabled, the server permits client updates only
from users who have the |SUPER|

privilege. If the |super_read_only|

system variable is also enabled, the server prohibits client updates
even from users who have |SUPER|
."
One somewhat gets the impression that in order to enable
super_read_only, one must also enable read_only.

However, based on:

Re: Replication and user privileges

2019-02-26 Thread Jim

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

Thank you.
Jim

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)

===

Thanks, Shawn. Your response confirms what I had assumed was happening.

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

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|

system variable is enabled, the server permits client updates only from
users who have the |SUPER|

privilege. If the |super_read_only|

However, based on:

Re: Replication and user privileges

2019-02-26 Thread shawn l.green

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

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)

===

Thanks, Shawn. Your response confirms what I had assumed was happening.

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

ANN: Upscene releases Database Workbench 5.6.0

2019-02-26 Thread Martijn Tonies (Upscene Productions)

Upscene Productions is proud to announce the availability of
the next version of the popular multi-DBMS development tool:

“ Database Workbench 5.6.0 "

This new release brings you the Command Line Data Pump & Favorites feature (Pro 
Edition) and a few important bugfixes. 

Version 5.5 brought you support for the latest versions of supported database 
systems, that includes PostgreSQL 11, InterBase 2017 and MySQL 8.

Database Workbench 5 comes in multiple editions with different pricing models, 
there's always a version that suits you!

Here's the full list of changes
http://www.upscene.com/go/?go=tracker=5.6.0=12
and for version 5.5.0
http://www.upscene.com/go/?go=tracker=5.5.0=12

For more information, see What's new in Database Workbench 5?
( http://www.upscene.com/database_workbench/whatsnew )


Database Workbench supports MySQL, MariaDB, Firebird, Oracle, MS SQL Server,
SQL Anywhere, NexusDB, PostgreSQL and InterBase, comes in multiple editions and 
is licensed based on selectable modules.

It includes tools for database design, database maintenance, testing, data 
transfer,
data import & export, database migration, database compare and numerous other 
tools.


About Database Workbench
Database Workbench is a database developer tool, over 12 years in the making and
is being used by thousands of developers across the globe who have come to rely 
on it
every day. From database design, implementation, to testing and debugging, it 
will aid you 
in your daily database work.

About Upscene Productions
Based in The Netherlands, Europe, this small but dedicated company has been 
providing
database developers with useful tools for over 14 years. Slowly expanding the 
product portfolio
and gaining recognition amongst InterBase and Firebird database developers, 
they now offer
tools for a whole range of database systems, including Oracle and Microsoft SQL 
Server.

Re: Replication and user privileges

Re: Replication and user privileges

Re: Replication and user privileges

ANN: Upscene releases Database Workbench 5.6.0

4 matches

Site Navigation

Mail list logo

Footer information