Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24362

Privilege aliases are missing from SHOW PRIVILEGES

Details

    Description

      MariaDB Server currently supports several privilege aliases:

      • READ ONLY ADMIN is an alias for READ_ONLY ADMIN.
      • REPLICATION REPLICA is an alias for REPLICATION SLAVE.
      • REPLICATION REPLICA ADMIN is an alias for REPLICATION SLAVE ADMIN.
      • REPLICATION CLIENT is an alias for BINLOG MONITOR.

      However, these privilege aliases do not seem to be present in SHOW PRIVILEGES:

      MariaDB [(none)]> SHOW PRIVILEGES;
      +--------------------------+---------------------------------------+--------------------------------------------------------------------+
      | Privilege                | Context                               | Comment                                                            |
      +--------------------------+---------------------------------------+--------------------------------------------------------------------+
      | Alter                    | Tables                                | To alter the table                                                 |
      | Alter routine            | Functions,Procedures                  | To alter or drop stored functions/procedures                       |
      | Create                   | Databases,Tables,Indexes              | To create new databases and tables                                 |
      | Create routine           | Databases                             | To use CREATE FUNCTION/PROCEDURE                                   |
      | Create temporary tables  | Databases                             | To use CREATE TEMPORARY TABLE                                      |
      | Create view              | Tables                                | To create new views                                                |
      | Create user              | Server Admin                          | To create new users                                                |
      | Delete                   | Tables                                | To delete existing rows                                            |
      | Delete history           | Tables                                | To delete versioning table historical rows                         |
      | Drop                     | Databases,Tables                      | To drop databases, tables, and views                               |
      | Event                    | Server Admin                          | To create, alter, drop and execute events                          |
      | Execute                  | Functions,Procedures                  | To execute stored routines                                         |
      | File                     | File access on server                 | To read and write files on the server                              |
      | Grant option             | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess                |
      | Index                    | Tables                                | To create or drop indexes                                          |
      | Insert                   | Tables                                | To insert data into tables                                         |
      | Lock tables              | Databases                             | To use LOCK TABLES (together with SELECT privilege)                |
      | Process                  | Server Admin                          | To view the plain text of currently executing queries              |
      | Proxy                    | Server Admin                          | To make proxy user possible                                        |
      | References               | Databases,Tables                      | To have references on tables                                       |
      | Reload                   | Server Admin                          | To reload or refresh tables, logs and privileges                   |
      | Binlog admin             | Server                                | To purge binary logs                                               |
      | Binlog monitor           | Server                                | To use SHOW BINLOG STATUS and SHOW BINARY LOG                      |
      | Replication master admin | Server                                | To monitor connected slaves                                        |
      | Replication slave admin  | Server                                | To start/monitor/stop slave and apply binlog events                |
      | Replication slave        | Server Admin                          | To read binary log events from the master                          |
      | Select                   | Tables                                | To retrieve rows from table                                        |
      | Show databases           | Server Admin                          | To see all databases with SHOW DATABASES                           |
      | Show view                | Tables                                | To see views with SHOW CREATE VIEW                                 |
      | Shutdown                 | Server Admin                          | To shut down the server                                            |
      | Super                    | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.                |
      | Trigger                  | Tables                                | To use triggers                                                    |
      | Create tablespace        | Server Admin                          | To create/alter/drop tablespaces                                   |
      | Update                   | Tables                                | To update existing rows                                            |
      | Set user                 | Server                                | To create views and stored routines with a different definer       |
      | Federated admin          | Server                                | To execute the CREATE SERVER, ALTER SERVER, DROP SERVER statements |
      | Connection admin         | Server                                | To bypass connection limits and kill other users' connections      |
      | Read_only admin          | Server                                | To perform write operations even if @@read_only=ON                 |
      | Usage                    | Server Admin                          | No privileges - allow connect only                                 |
      +--------------------------+---------------------------------------+--------------------------------------------------------------------+
      39 rows in set (0.008 sec)
      

      Attachments

        Issue Links

          Activity

            danblack Daniel Black added a comment -

            Also missing as an alias is the obvious old time one of "ALL PRIVILEGES".

            Attempting to use the output of SHOW PRIVILEGES programmatically like https://github.com/MariaDB/server/commit/86a7fab1755238710ed301578554d2c7d2d10d13 to ensure that a mariadb-dump --system=users contains a portable set of grants currently requires a special case for "ALL PRIVILEGES".

            The other missing aliases would be problematic if using a 10.5 mariadb-dump --system=users with the above commit against an older MariaDB version. In this case the missing grants would be marked as MySQL-8.0+ grants and be ignored on import rather than letting the compatibility later take care of the translation.

            As such, I'd like to include these aliases too. ralf.gebhardt@mariadb.com, serg is that ok with you?

            danblack Daniel Black added a comment - Also missing as an alias is the obvious old time one of "ALL PRIVILEGES". Attempting to use the output of SHOW PRIVILEGES programmatically like https://github.com/MariaDB/server/commit/86a7fab1755238710ed301578554d2c7d2d10d13 to ensure that a mariadb-dump --system=users contains a portable set of grants currently requires a special case for "ALL PRIVILEGES". The other missing aliases would be problematic if using a 10.5 mariadb-dump --system=users with the above commit against an older MariaDB version. In this case the missing grants would be marked as MySQL-8.0+ grants and be ignored on import rather than letting the compatibility later take care of the translation. As such, I'd like to include these aliases too. ralf.gebhardt@mariadb.com , serg is that ok with you?

            No, I'd rather not include aliases, but only the default name. READ_ONLY ADMIN is the correct name, The spelling without underscore is a user-friendly alias to account for typical typos.

            SLAVE/REPLICA — we should, again, show only one spelling, the default one. The default is supposed to become configurable or change to REPLICATE eventually.

            And, again, BINLOG MONITOR is the privilege name and REPLICATION CLIENT is the compatibility alias, that we support, but don't recommend to use.

            serg Sergei Golubchik added a comment - No, I'd rather not include aliases, but only the default name. READ_ONLY ADMIN is the correct name, The spelling without underscore is a user-friendly alias to account for typical typos. SLAVE/REPLICA — we should, again, show only one spelling, the default one. The default is supposed to become configurable or change to REPLICATE eventually. And, again, BINLOG MONITOR is the privilege name and REPLICATION CLIENT is the compatibility alias, that we support, but don't recommend to use.

            People

              Unassigned Unassigned
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.