> The intention should be that in a production environment the only reason to log in as 'root' is to do drastic changes that cannot be done by anyone else
This is ideally how things should be, but real life is far from ideal. In most envs where I worked-on, DBAs are logging to the db as root, and a commun mistake is thinking one is on the primary but in fact being on a replica and committing a trx. Super Read ONLY (sro) is a way to protect against this.
Arguably, another way to protect against this is to not log as a SUPER USER, but this would be a breaking change in most of the env I worked-on.
> Another reason to not use 'super_read_only' is that a super user can change the value and still do updates.
This is a misunderstanding of what sro is protecting against. sro is not protecting against a malicious user, it is protecting against a user about to do a mistake.
However, current sro implementation in MySQL 5.7 (and probably 8.0) is that if a SUPER user wants to actually write on a replica, he needs to disable sro before doing its write. This actually disable sro for all users, which is opening the mistake or writing on the db by mistake. A way clean to bypass sro should be provided to a super user, which does not involve disabling sro for all users. A session variable would be good for me.
Arguably, I have not checked the most recent user privileges in MariaDB, and I have not time for this right now. But I like the idea of fully disabling writes on a replica and failing all writes with a sro message when such mistake is done. I also like a way to bypass sro, which does not exist in MySQL (I have to writes on my replicas, I could explain this use-case if needed, no time now).
(About a way to cleanly bypass sro: I have to open this feature request in. MySQL, I have not had the time to do this yet, and I am disabling and re-enabling sro in my current env in at least 2 scripts.)
Jean-François Gagné
added a comment - > The intention should be that in a production environment the only reason to log in as 'root' is to do drastic changes that cannot be done by anyone else
This is ideally how things should be, but real life is far from ideal. In most envs where I worked-on, DBAs are logging to the db as root, and a commun mistake is thinking one is on the primary but in fact being on a replica and committing a trx. Super Read ONLY (sro) is a way to protect against this.
Arguably, another way to protect against this is to not log as a SUPER USER, but this would be a breaking change in most of the env I worked-on.
> Another reason to not use 'super_read_only' is that a super user can change the value and still do updates.
This is a misunderstanding of what sro is protecting against. sro is not protecting against a malicious user, it is protecting against a user about to do a mistake.
However, current sro implementation in MySQL 5.7 (and probably 8.0) is that if a SUPER user wants to actually write on a replica, he needs to disable sro before doing its write. This actually disable sro for all users, which is opening the mistake or writing on the db by mistake. A way clean to bypass sro should be provided to a super user, which does not involve disabling sro for all users. A session variable would be good for me.
Arguably, I have not checked the most recent user privileges in MariaDB, and I have not time for this right now. But I like the idea of fully disabling writes on a replica and failing all writes with a sro message when such mistake is done. I also like a way to bypass sro, which does not exist in MySQL (I have to writes on my replicas, I could explain this use-case if needed, no time now).
(About a way to cleanly bypass sro: I have to open this feature request in. MySQL, I have not had the time to do this yet, and I am disabling and re-enabling sro in my current env in at least 2 scripts.)
> Arguably, another way to protect against this is to not log as a SUPER USER, but this would be a breaking change in most of the env I worked-on.
a smaller and less breaking would be to REVOKE READ ONLY ADMIN ON *.* FROM root@'%'
it's practically the same as super read only, and a way to bypass it would be to have a separate roadmin user that has this privilege.
Sergei Golubchik
added a comment - jeanfrancois.gagne ,
> Arguably, another way to protect against this is to not log as a SUPER USER, but this would be a breaking change in most of the env I worked-on.
a smaller and less breaking would be to REVOKE READ ONLY ADMIN ON *.* FROM root@'%'
it's practically the same as super read only, and a way to bypass it would be to have a separate roadmin user that has this privilege.
Noting that the above comment is useful for versions 10.11.0 and later where the READ ONLY ADMIN privilege has been removed from SUPER, therefore resulting in the described behaviour. Thanks.
Terry Burton
added a comment - Noting that the above comment is useful for versions 10.11.0 and later where the READ ONLY ADMIN privilege has been removed from SUPER, therefore resulting in the described behaviour. Thanks.
This can be closed as MDEV-29596 Separate SUPER and READ ONLY ADMIN privileges solves this read-only issue n better manner than having a super read only mode.
Michael Widenius
added a comment - This can be closed as MDEV-29596 Separate SUPER and READ ONLY ADMIN privileges solves this read-only issue n better manner than having a super read only mode.
Task
Major
MDEV-36425 Extend read_only to also block share locks and super users
> The intention should be that in a production environment the only reason to log in as 'root' is to do drastic changes that cannot be done by anyone else
This is ideally how things should be, but real life is far from ideal. In most envs where I worked-on, DBAs are logging to the db as root, and a commun mistake is thinking one is on the primary but in fact being on a replica and committing a trx. Super Read ONLY (sro) is a way to protect against this.
Arguably, another way to protect against this is to not log as a SUPER USER, but this would be a breaking change in most of the env I worked-on.
> Another reason to not use 'super_read_only' is that a super user can change the value and still do updates.
This is a misunderstanding of what sro is protecting against. sro is not protecting against a malicious user, it is protecting against a user about to do a mistake.
However, current sro implementation in MySQL 5.7 (and probably 8.0) is that if a SUPER user wants to actually write on a replica, he needs to disable sro before doing its write. This actually disable sro for all users, which is opening the mistake or writing on the db by mistake. A way clean to bypass sro should be provided to a super user, which does not involve disabling sro for all users. A session variable would be good for me.
Arguably, I have not checked the most recent user privileges in MariaDB, and I have not time for this right now. But I like the idea of fully disabling writes on a replica and failing all writes with a sro message when such mistake is done. I also like a way to bypass sro, which does not exist in MySQL (I have to writes on my replicas, I could explain this use-case if needed, no time now).
(About a way to cleanly bypass sro: I have to open this feature request in. MySQL, I have not had the time to do this yet, and I am disabling and re-enabling sro in my current env in at least 2 scripts.)