patternsqlMinor
MySQL replication and master users on slave server
Viewed 0 times
serverslavereplicationmysqlmasterandusers
Problem
Do the users initiating inserts on the master server also have to be on the slave with at least as many permissions for the replicated queries to be correctly executed? My understanding is that you only have to have a replication user with enough permissions but I still have a doubt.
Moreover, let's say that the slave server has the same users as the master server, in addition to a replication-dedicated user, but with less permissions, non writes for instance. Would there be any kind of conflicts resulting in the replicated queries not being executed?
The idea is to have a read-only slave with only
Moreover, let's say that the slave server has the same users as the master server, in addition to a replication-dedicated user, but with less permissions, non writes for instance. Would there be any kind of conflicts resulting in the replicated queries not being executed?
The idea is to have a read-only slave with only
SELECT perm users to access to data, not necessarily the same as the master's users, plus a replication-dedicated user on the slave with REPLICATION SLAVE perm. Would all replicated queries then supposed to be executed normally? That's my actual wondering.Solution
-
The "user" that the Slave connects to the Master with -- It needs only
-
The Slave should have
-
"Users" (either human or application), other than the system admin, should not have
-
Another hacking prevention is to
-
Users need write permissions when writing to the Master. Don't worry about whether the users have write perm to the Slave; the Slave user bypasses that kind of check. (This is probably the answer you were looking for.)
-
Users need only read permission when reading from the Slave.
-
The "user" that the Slave connects to the Master with -- It needs only
REPLICATION SLAVE, no other privileges. Replication's goal is to keep the Slave identical to the Master. This goal overrides any privilege issues.-
The Slave should have
read_only = ON as an extra protection.-
"Users" (either human or application), other than the system admin, should not have
SUPER privilege. This keeps you out of trouble, and helps prevent hacking.-
Another hacking prevention is to
GRANT users access only to dbname., not .*. The latter would include the system tables where all the Grants are stored.-
Users need write permissions when writing to the Master. Don't worry about whether the users have write perm to the Slave; the Slave user bypasses that kind of check. (This is probably the answer you were looking for.)
-
Users need only read permission when reading from the Slave.
-
TEMPORARY tables follow different rules. (It is something you might want to use on a read-only Slave.) I hesitate to spell them out here, because I am not sure of the rules.Context
StackExchange Database Administrators Q#131829, answer score: 2
Revisions (0)
No revisions yet.