bar can you please add an estimation?

The SUPER privilege is tested in the following scenarios in MariaDB:

• Setting global system variables
• Execution of SHOW SLAVE STATUS statements
• Execution of CREATE FUNCTION functions when binlog is enabled
• Executions of BINLOG statements
• Skip check for max_user_connection
• Ignore init-connect command failures
• Ignore the --read-only option (for non-replication related statements).
• Execution of the C client function mysql_dump_debug_info()
• Execution of CREATE statements for a stored routine using a different DEFINER
• Execution of PURGE LOGS and PURGE LOGS...BEFORE statements
• Execution of CHANGE MASTER statements
• Execution of SHOW MASTER STATUS statements
• Execution of SHOW BINARY LOGS statements
• Execution of CREATE SERVER statements
• Execution of ALTER SERVER statements
• Execution of DROP SERVER statement
• Execution of KILL statements for threads owned by other users
• Execution START SLAVE statements
• Execution of STOP SLAVE statements
• Execution of CREATE TRIGGER and DROP TRIGGER when binary logging is enabled even if log-bin-trust-function-creators=0
• Execution of DROP TRIGGER even if the server is running with --read-only
• Setting the following session system variables:
  • binlog_format
  • biglog_direct_check
  • debug
  • debug_dbug
  • pseudo_thread_id
  • gtid_domain_id
  • gtid_seq_no
  • server_id
  • enforce_storage_engine
  • debug_sync
  • sql_log_off
  • sql_log_bin
  • timestamp
  • log_disabled_statements
  • wsrep_on
• Execution of CREATE VIEW .. SQL SECURITY DEFINER statements for other users
• Execution of START TRANSACTION READ WRITE statements if the server is running with --read-only option.

The following scenarios do not need changes as they already have a dedicated privilege in MariaDB:

• REPLICATION CLIENT - a dedicated privilege (in addition to SUPER though)
  • Execution of SHOW SLAVE STATUS statements
  • Execution of SHOW MASTER STATUS statements
  • Execution of SHOW BINARY LOGS statements
• REPLICATION SLAVE - a dedicated privilege
  • Execution START SLAVE statements
  • Execution of STOP SLAVE statements
  • Execution of CHANGE MASTER statements

The following scenarios allow only SUPER and do not have dedicated privileges in MariaDB.

MySQL-8.0 distributes all these scenarios about as follows:

• Scenarios when MySQL still uses the SUPER privilege without adding a new dedicated one:
  • Execution of the C client function mysql_dump_debug_info()
  • Execution of CREATE FUNCTION when binlog is enabled
  • Execution of CREATE SERVER statements
  • Execution of ALTER SERVER statements
  • Execution of DROP SERVER statement
• BINLOG_ADMIN
  • Executions of BINLOG statements
  • Execution of PURGE LOGS and PURGE LOGS...BEFORE statements
• SYSTEM_VARIABLES_ADMIN
  • Setting global system variables
• CONNECTION_ADMIN
  • Ignore init-connect command failures
  • Skip check for max_user_connection
  • Execution of KILL statements to kill threads owned by other accounts
  • Execution of DROP TRIGGER even if the server is running with --read-only
  • Execution of START TRANSACTION READ WRITE statements if the server is running with --read-only option.
  • Ignore the --read-only option (for non-replication related statements).
• SESSION_VARIABLES_ADMIN - setting the restricted session system variables:
  • binlog_format
  • biglog_direct_check
  • debug
  • debug_dbug
  • pseudo_thread_id
  • gtid_domain_id
  • gtid_seq_no
  • server_id
  • enforce_storage_engine
  • debug_sync
  • sql_log_off
  • sql_log_bin
  • timestamp
  • log_disabled_statements
  • wsrep_on
• SET_USER_ID
  • Execution of CREATE VIEW .. SQL SECURITY DEFINER statements for other users
  • Execution of CREATE statements for a stored routine using a different DEFINER
  • Execution of CREATE TRIGGER and DROP TRIGGER when binary logging is enabled even if log-bin-trust-function-creators=0

Functional splitting of SUPER

Note: privilege names are for illustrative purposes only, I do not suggest these names as such

• SET_USER_ID:
  • Execution of CREATE VIEW .. SQL SECURITY DEFINER statements for other users
  • Execution of CREATE statements for a stored routine using a different DEFINER
  • Execution of CREATE TRIGGER and DROP TRIGGER when binary logging is enabled even if log-bin-trust-function-creators=0

• REPLICATION SLAVE:
  • Execution of CREATE FUNCTION for non-deterministic functions when binlog is enabled
  • Execution START SLAVE statements
  • Execution of STOP SLAVE statements
  • Execution of CHANGE MASTER statements
  • Executions of BINLOG statements
  • Setting the following session system variables:
    • binlog_format
    • biglog_direct_check
    • gtid_domain_id
    • gtid_seq_no
    • sql_log_off
    • sql_log_bin
    • wsrep_on
  • Setting the following global system variables:
    • binlog%
    • expire_logs_days
    • gtid%
    • init_slave
    • log_bin%
    • master%
    • max_binlog%
    • read_binlog_speed_limit
    • relay%
    • replicate%
    • rpl%
    • slave%
    • sync_binlog
    • sync_master_info
    • sync_relay_log%
    • wsrep%

• REPLICATION CLIENT:
  • Execution of SHOW SLAVE STATUS statements
  • Execution of SHOW MASTER STATUS statements
  • Execution of SHOW BINARY LOGS statements

• SERVER (federated):
  • Execution of CREATE SERVER statements
  • Execution of ALTER SERVER statements
  • Execution of DROP SERVER statement

• DEBUG
  • Execution of the C client function mysql_dump_debug_info()
  • Setting the following session system variables:
    • debug
    • debug_dbug
    • debug_sync
  • Setting the following global system variables:
    • %debug%

• SUPER CONNECTION:
  • Skip check for max_user_connection
  • Ignore init-connect command failures

• CONNECTION ADMIN
  • Setting the following global system variables:
    • connect_timeout
    • disconnect_on_expired_password
    • extra_max_connections
    • init_connect
    • max_connections
    • max_connect_errors
    • max_password_errors
    • proxy_protocol_networks
    • secure_auth
    • slow_launch_time
    • thread_pool%

• SUPER READONLY:
  • Ignore the --read-only option (for non-replication related statements).
  • Execution of slave side DROP TRIGGER even if the slave is running with --read-only
  • Execution of START TRANSACTION READ WRITE statements if the server is running with --read-only option.
  • Setting the following global system variables:
    • read_only

• SUPER PROCESS
  • Execution of KILL statements for threads owned by other users

• SUPER LOGS
  • Execution of PURGE LOGS and PURGE LOGS...BEFORE statements
  • Setting the following session system variables:
    • log_disabled_statements
  • Setting the following global system variables:
    • general_log
    • general_log_file
    • log_output
    • slow_query_log_file

• SUPER
  • Remaining global system variables
  • Remaining session system variables:
    • pseudo_thread_id
    • server_id
    • enforce_storage_engine
    • timestamp

Out of the above, I think DEBUG doesn't need a privilege of its own, and can be part of SUPER.
SERVER, perhaps, too.

SUPER PROCESS, CONNECTION ADMIN, and SUPER CONNECTION can be one privilege.

Let's add SHOW BINLOG STATUS as a synonym for SHOW MASTER STATUS, and promote the new variant as the main one in the manual.

Rationale:

• It's more logical, as the command only shows binlog information, nothing replication related. The server might not even be a master.
• It makes privileges easier to remember, the new BINLOG ADMIN privilege will apply to all %BINLOG% statements.
• We were going to rename it anyway under terms of MDEV-18777, to SHOW PRIMARY STATUS (for political correctness purposes).

The manual should be modified mention about SHOW MASTER STATUS: "use SHOW BINLOG STATUS instead".

The 2020-03-01 edition:

Actions whose privileges did not change and still require the SUPER privilege:

• SUPER
  • Calling DES_DECRYPT() with one argument
  • Creating triggers and stored functions when binary log is enabled and log_bin_trust_function_creators=0
  • Executing the C client function mysql_dump_debug_info()
  • Setting global system variables
  • Setting restricted session system variables

Actions that now have new privileges and required SUPER prior to this task:

• SET USER
  • Allow to set an object definer in CREATE DEFINER=xxx (TRIGGER|VIEW|FUNCTION|PROCEDURE).
  • Reveal the exact VIEW definer if the definer was droppped after the view creation:

    ERROR: The user specified as a definer ('definer1'@'localhost') doesn't exist

    instead of a generic access denied error.

• READ ONLY ADMIN
  • Execution of DROP TRIGGER even if the server is running with --read-only
  • Execution of START TRANSACTION READ WRITE statements if the server is running with --read-only option.
  • Ignore the --read-only option (for non-replication related statements)
• CONNECTION ADMIN
  • Skip the init-connect initialization
  • Bypass the test for max_connections
  • Bypass the test for max_user_connections
  • Bypass the test for max_password_errors
  • Kill threads owned by other users
• FEDERATED ADMIN
  • Execution of CREATE SERVER statements
  • Execution of ALTER SERVER statements
  • Execution of DROP SERVER statement
• BINLOG ADMIN
  • PURGE LOGS
  • PURGE LOGS..BEFOR

Binlog and replication related actions whose privileges did not change:

• REPLICATION SLAVE
  • Execution of COM_REGISTER_SLAVE
  • Execution of COM_BINLOG

Binlog and replication related actions that now have a new privilege:

• REPLICATION MASTER ADMIN (new)
  • SHOW SLAVE HOSTS (required REPLICATION SLAVE prior to this task)
• REPLICATION SLAVE ADMIN (new)
  • START SLAVE (required SUPER prior this this task)
  • STOP SLAVE (required SUPER prior this this task)
  • CHANGE Master (required SUPER prior this this task)
  • SHOW SLAVE STATUS (required SUPER or REPLICATION CLIENT prior to this task)
  • BINLOG (required SUPER_ACL prior to this task
  • SHOW RELAYLOG EVENTS (required REPLICATION SLAVE prior to this task)

Binlog and replication actions whose privileges change to a different previously known privilege:

• REPLICATION CLIENT
  • SHOW BINLOG STATUS (required SUPER or REPLICATION CLIENT prior to this task)
  • SHOW BINARY LOGS (required SUPER or REPLICATION CLIENT prior to this task)
• PROCESS
  • SHOW BINLOG EVENTS (required REPLICATION SLAVE prior to this task)

bar, serg,
SHOW BINLOG STATUS (a.k.a SHOW MASTER STATUS) capability seems to be badly misplaced. The justification of the new synonym is "the command only shows binlog information, nothing replication related", which is true. But then, how did it become a part of REPLICATION CLIENT privilege? It's neither replication nor client; and it means that without configuring a user with REPLICATION CLIENT privilege on a server which may have nothing to do with replication, nobody can execute SHOW BINLOG STATUS.

Same for SHOW BINARY LOGS, and even worse, as it's a more important action than SHOW MASTER STATUS.

Maybe all three (SHOW BINLOG STATUS, SHOW BINARY LOGS and SHOW BINLOG EVENTS) should belong to BINLOG ADMIN?

SHOW MASTER STATUS and SHOW BINARY LOGS have always been a part of REPLICATION CLIENT.
I guess this is because of the main purpose of these statements: to allow a human-run client from the slave machine to connect to the master and check the status of the binary logs, to start the automatically-run replication thread properly.

Sergei proposed to rename REPLICATION CLIENT to BINLOG MONITOR. It much better reflects what this privilege is about.

Let's rename, but keep also the old syntax for compatibility.

It will provide a better name, but won't solve the problem. Before MDEV-21743, users with SUPER privilege had SHOW MASTER STATUS and SHOW BINARY LOGS capabilities. After upgrade to the new version, they won't, because these capabilities are not given to any new privilege which is assigned to former SUPER users via the means of access/version_id mechanism. It is removed from SUPER and kept only for an existing privilege, which the users might not have, and probably won't have, because who would assign REPLICATION CLIENT privilege to an admin of a master server or a non-replication instance? So, this part will actually break existing access rights upon upgrade.

Summary on bb-10.5-bar 7ce6e93fb

Conformity to requirements

There were two requirements in the initial description:

• "The SUPER privilege should be split up to multiple admin privileges" – This is partially done. SUPER still remains a separate privilege with a smaller scope than before; so it hasn't been split up entirely.
• "The SUPER privilege as such should still remain as an alias for all these smaller privileges" – This is not done.

In my opinion, we should have done at least one of two things: either keep SUPER as an alias of what it had been before (and start deprecating it), then it's not that important whether we do all splitting in this release or leave something for the next ones; or, much more aggressively, split the privilege entirely into new ones, so that users would have to change their scripts and code only once. We would get a lot of heat with the aggressive approach, but it would still be more user-friendly than splitting it now partially, making users review all their scripts/code and make necessary changes, and then do it again in the next release.

From a discussion on Slack, the decision was that the implemented solution is acceptable and the result should be documented.

Problems

• (mentioned in the previous comment) SUPER privilege has lost SHOW BINLOG STATUS and SHOW BINARY LOGS capabilities. Unlike other ones which were moved to new privileges and are still assigned to SUPER accounts from previous versions via access/version_id magic, these ones are not, they now only belong to an old privilege REPLICATION CLIENT which old SUPER accounts are unlikely to have. So, access will be broken after upgrade. The same consideration applies to REPLICATION CLIENT privilege, which has lost SHOW SLAVE STATUS capability, and to REPLICATION SLAVE privilege, which has lost SHOW RELAYLOG EVENTS capability, but these are probably less important.

• Replication OM => NM involving user operations will cause discrepancy in privileges. E.g. GRANT SUPER ON *.* TO `u` executed on 10.4 and replicated to 10.5 will end up with the user `u` having less privileges on 10.5 than on 10.4.

• The check for access value doesn't always detect a wrong format, the result can be dangerous:

  MariaDB [(none)]> update mysql.global_priv set priv = '{"access":111111111111111111111,"version_id":100502,"plugin":"mysql_native_password","authentication_string":"","password_last_changed":1583421540}' where user = 'u';

  Query OK, 1 row affected (0.030 sec)

  Rows matched: 1 Changed: 1 Warnings: 0

  MariaDB [(none)]> flush privileges;

  Query OK, 0 rows affected (0.005 sec)

  MariaDB [(none)]> show grants for u;

  +----------------------------------------------------------+

  | Grants for u@% |

  +----------------------------------------------------------+

  | GRANT ALL PRIVILEGES ON *.* TO `u`@`%` WITH GRANT OPTION |

  +----------------------------------------------------------+

  1 row in set (0.000 sec)

  There are no warnings in the error log, and the account is given maximum permissions. The latter is the reason why it is a problem and not just cosmetics.

Usability

• Privilege errors like

  $ client/mysqladmin --protocol=tcp --port=3307 -usuper stop-slave

  client/mysqladmin: Error stopping slave: Access denied for user 'super'@'%' (using password: NO)

  are unhelpful. They are not new, it was the same way before, but given the likely misconfiguration which will come after the incompatible change (shrunk SUPER privileges), it would be more user-friendly to return instead a typical message like "requires at least REPLICATION SLAVE ADMIN privilege" etc.

• Replaying binary logs has become more complicated. Neither SUPER alone nor REPLICATION SLAVE ADMIN alone can do it – SUPER can no longer execute BINLOG records, and REPLICATION SLAVE ADMIN cannot set certain session variables which are a part of the binlog.

• This task would be a good reason to start explicitly forbidding downgrade to previous major versions. It never works well anyway, and for downgrade from 10.5.2 it is de-facto already rejected on InnoDB level, but it can still be done by disabling InnoDB. Technically it now presents a security risk, since in older versions certain users will have wider permissions than they were configured with in 10.5.

• (related to MDEV-21704) If possible, it would be good to throw a warning about a wrong access value or version_id upon FLUSH PRIVILEGES – not just in the error log where nobody will see it. The most likely situation for the problem in real life is when somebody tries to (re-)configure a user manually in mysql.global_priv and makes a mistake. So, FLUSH PRIVILEGES after doing so is quite likely, and a warning would be informative.
  Also, it is questionable what should happen upon further actions on such users. MDEV-21704 says "ignore records" (with wrong access and/or version_id values), which is ambiguous. It may mean that the records wouldn't be used at all (as if the user/role doesn't exist), but in fact only global privileges are ignored and considered to be none, while the user/role itself still works, and non-global privileges work as well. And further any GRANT or even ALTER quietly overrides the previous wrong access value, only with a warning in the error log. Again, there should be at least a warning in the client.

Cosmetics

• (discussed in previous comments) REPLICATION CLIENT is a misleading name, especially now as it only has two remaining capabilities, SHOW BINLOG STATUS and SHOW BINARY LOGS, none of which has anything to do with replication.
  The suggestion above is to rename it to BINLOG MONITOR while keeping the old alias for compatibility.

• REPLICATION SLAVE is documented as intended for configuring slave access (not human access). But now after upgrade previous accounts configured as REPLICATION SLAVE also get REPLICATION MASTER ADMIN privilege (I suppose because SHOW SLAVE HOSTS capability is needed for replication?). It looks weird that a technical account for slave replication access gets a "master admin" privilege.

• It looks like there are no more capabilities which belong to more than one privilege. Possibly it was even a goal in itself. If it is indeed the case, the error message

  Access denied; you need (at least one of) the REPLICATION SLAVE ADMIN privilege(s) for this operation

  can be modified to remove the "at least one of" part and plural in privileges, just to make it look cleaner.

Documentation

• Documentation effort will require much more than just listing the changes in one place. Casual references to different privileges, especially to SUPER, are spread around documentation like this:

  Description: Maximum simultaneous connections permitted for each user account. When set to 0, there is no per user limit. From MariaDB 5.3, setting it to -1 stops users without the SUPER privilege from connecting to the server.

• Typos in the comment "The 2020-03-01 edition" (in case it is used for documentation):
  • DES_DESCRIPT => DES_DECRYPT
  • Creating non-deterministic triggers and stored functions when binary log is enabled and log_bin_trust_function_creators=0 => I think non-deterministic doesn't play a role here, it relates to any functions when binary log enabled and log_bin_trust_function_creators=0
  • "Bypass the test for max_connections" => probably use "max_connections + 1" connection, not bypass entirely?
  • "Bypass the test for max_password_errors" – CONNECTION ADMIN doesn't bypass the check (and neither does SUPER in previous versions). Hopefully it's just a typo, I don't think we want an admin account to be easily brute-force-able.
  • "Execution of COM_BINLOG" – I can't find any references to COM_BINLOG. Probably COM_BINLOG_DUMP?

bar,

Did you happen to fix the privilege bug involving ALTER FUNCTION and log_bin_trust_function_creators=OFF and log_bin=ON (see MDEV-19088) as part of this change?

Also, would it be appropriate for RocksDB backups (see MDEV-20577) to fall under any of these new privileges?

GeoffMontee, no I did not fix MDEV-19088, neither did I handle MDEV-20577.

Note, I'm binding some other variables to smaller-than-SUPER privileges at the moment, so possibly it will be easier to address MDEV-20577 soon.