Any news on that compatibility issue ? maxscale trying to manage failover now, it may be an important feature. to protect switchover against super user !

Yes, I hope to have the super read only too.

And make it changeable on a session level. I opened a feature request about this at MySQL as well...
Scenario: re-set-up of a slave with mysqldump | mysql without any interference of 3rd party.

Still no update here?

Agree with Oli here SUPER_READ_ONLY could be bypass from session perspective when sql_log_bin=0. Enabling some data interaction with a user that respect the replication position !

Hi, SUPER_READ_ONLY is a very important and useful feature for us.

Is there any estimate when it will be implemented into MariaDB? Thank you

As far as I understand, the whole reason for super-read-only to exist,why it was added in the first place, was that one had to give the user SUPER privilege for the user to be able to do something, but one didn't want the user to bypass read-only.

But since 10.5 we have fine-grained privileges that replace SUPER. So

• one doesn't need to give a user SUPER, one can give whatever privileges the user actually needs, and simply not give READ_ONLY ADMIN.
• also we can make READ_ONLY ADMIN the only required privilege for bypassing read-only, thus removing SUPER from the check. It'll break applications that use new privilege tables and only SUPER. Old privilege tables will work as before (SUPER converted to READ_ONLY ADMIN on load. mariadb-upgrade will also grand READ_ONLY ADMIN to everyone, who has SUPER.

either way, one does not need super-read-only with new fine-grained privileges

From my experience, the most useful use case that I've encountered is to prevent system users (e.g. authenticating as root, then accessing MySQL by the peercred socket) from accidentally committing writes to the dataset of a read-only replica in a streaming replication configuration when performing an intervention.

That's only one use case for the feature, but the lack of a feature that neuters admin's capability (even if only synthetically) makes MariaDB somewhat more fragile that other databases that have such a feature.

Replying to Terry Burton:

• 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 like changing engine for a table (with ALTER TABLE ... ENGINE=...) or solving replication issues that may require one to delete a conflicting row from a table)
• To avoid the issue with root and read only, one can create a new user, for example 'admin', with all rights of root except READ_ONLY_ADMIN and use this when one needs to login and do adjustments to the server.

This is much better and safer setup than using --super-readonly as it gives one more ability to track the admin and root users and check that the accounts are used correctly (ie, people using 'root' login needs a very good reason why they are using root and not admin)

Agree one big reason was the lack of other fine grain privileges like the most important BINLOG_FORMAT and ending with application users given SUPER privileges breaking read only constraint. One lacking grant option ( from what i know ?) is the SET GLOBAL vs SET SESSION variable distinction we all remenber about the infamous set global sql_log_bin=0 that surely break a replication, regarding setting binary log format at the session level i don't see any valid reason to protect such change when it transit form statement -> row or mixed > row as it's always safer an can save workload many lock issues like in CREATE OR REPLACE T SELECT /*costly query */ (network performance ok but what about just bad SQL database don't deny to write queries just because they can be badly written

Another reason to not use 'super_read_only' is that a super user can change the value and still do updates. This means that the protection
provided is in essence just a warning and can easily be silently bypassed.
This is not possible if one uses the admin user solution instead.

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

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.

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.

The problem is solved thanks to MDEV-7389