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

WITH ADMIN OPTION privilege is missing from SHOW PRIVILEGES

Details

    Description

      MDEV-4397 added support for roles. As part of that, the following syntax is valid:

      GRANT role_name TO user_name WITH ADMIN OPTION;
      

      For example:

      MariaDB [(none)]> CREATE USER my_user@localhost;
      Query OK, 0 rows affected (0.010 sec)
       
      MariaDB [(none)]> CREATE ROLE my_role;
      Query OK, 0 rows affected (0.004 sec)
       
      MariaDB [(none)]> GRANT my_role TO my_user@localhost WITH ADMIN OPTION;
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [(none)]> SHOW GRANTS FOR my_user@localhost;
      +------------------------------------------------------------+
      | Grants for my_user@localhost                               |
      +------------------------------------------------------------+
      | GRANT `my_role` TO `my_user`@`localhost` WITH ADMIN OPTION |
      | GRANT USAGE ON *.* TO `my_user`@`localhost`                |
      +------------------------------------------------------------+
      2 rows in set (0.000 sec)
      

      However, this WITH ADMIN OPTION privilege does not seem to be present in SHOW PRIVILEGES, even though the similar WITH GRANT OPTION privilege is listed:

      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 commit 0c303b52a96e24c4e59378e3f6e011d67e27b104 includes

            CREATE ROLE xxx WITH ADMIN yyy
            GRANT xxx TO yyy WITH ADMIN OPTION
            REVOKE ADMIN OPTION FOR xxx FROM yyy

            danblack Daniel Black added a comment - also commit 0c303b52a96e24c4e59378e3f6e011d67e27b104 includes CREATE ROLE xxx WITH ADMIN yyy GRANT xxx TO yyy WITH ADMIN OPTION REVOKE ADMIN OPTION FOR xxx FROM yyy
            serg Sergei Golubchik added a comment - - edited

            ADMIN OPTION is not a privilege, it's an option. It's questionable whether SHOW PRIVILEGES should show it.

            Note that historically GRANT OPTION is a privilege.

            serg Sergei Golubchik added a comment - - edited ADMIN OPTION is not a privilege, it's an option. It's questionable whether SHOW PRIVILEGES should show it. Note that historically GRANT OPTION is a privilege.
            danblack Daniel Black added a comment -

            When using 'show privileges' programmatically like https://github.com/MariaDB/server/commit/86a7fab1755238710ed301578554d2c7d2d10d13, I wouldn't expect "ADMIN OPTION" to be used in the same way.

            If it where included in this list, the context would need to be in "Revoke Role", but even this form of "REVOKE ADMIN OPTION FOR role FROM grantee" is syntactically different from the normal revoke.

            As "WITH ADMIN OPTION" is the only GRANT ROLE option I'd suggest we leave this out of "SHOW PRIVILEGES".

            danblack Daniel Black added a comment - When using 'show privileges' programmatically like https://github.com/MariaDB/server/commit/86a7fab1755238710ed301578554d2c7d2d10d13 , I wouldn't expect "ADMIN OPTION" to be used in the same way. If it where included in this list, the context would need to be in "Revoke Role", but even this form of "REVOKE ADMIN OPTION FOR role FROM grantee" is syntactically different from the normal revoke. As "WITH ADMIN OPTION" is the only GRANT ROLE option I'd suggest we leave this out of "SHOW PRIVILEGES".

            People

              serg Sergei Golubchik
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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