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

GRANT permission missing if provided via role

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.5.18
    • N/A
    • None
    • Kubernetes 1.25.5, Worker Nodes with Flatcar Linux 3227.2.1, Ubuntu 20.04 base image, MariaDB 10.5.18 packages, Galera 26.4.13

    Description

      I have configured a fullaccess role and i have granted it to the root@% user. The default root@localhost has the same privilege but directly granted.

      SELECT user,is_role,host,Grant_priv,Super_priv,default_role,plugin FROM mysql.user ORDER BY user;
      +----------------+---------+-------------+------------+------------+----------------+-----------------------+
      | User           | is_role | Host        | Grant_priv | Super_priv | default_role   | plugin                |
      +----------------+---------+-------------+------------+------------+----------------+-----------------------+
      | fullaccess     | Y       |             | Y          | Y          |                |                       |
      | mariadb.sys    | N       | localhost   | N          | N          |                | mysql_native_password |
      | monitor        | N       | %           | N          | N          | mysql_exporter | mysql_native_password |
      | monitor        | N       | ::1         | N          | N          | mysql_exporter | mysql_native_password |
      | monitor        | N       | localhost   | N          | N          | mysql_exporter | mysql_native_password |
      | mysql_exporter | Y       |             | N          | N          |                |                       |
      | root           | N       | localhost   | Y          | Y          |                |                       |
      | root           | N       | mariadb-g-0 | Y          | Y          |                |                       |
      | root           | N       | 127.0.0.1   | Y          | Y          |                |                       |
      | root           | N       | ::1         | N          | N          | fullaccess     | mysql_native_password |
      | root           | N       | %           | N          | N          | fullaccess     | mysql_native_password |
      +----------------+---------+-------------+------------+------------+----------------+-----------------------+
      11 rows in set (0.001 sec)
       
      SHOW GRANTS FOR fullaccess;
      +------------------------------------------------------+
      | Grants for fullaccess                                |
      +------------------------------------------------------+
      | GRANT SUPER ON *.* TO `fullaccess` WITH GRANT OPTION |
      +------------------------------------------------------+
      1 row in set (0.000 sec)
       
      SHOW GRANTS FOR 'root'@'%';
      +-----------------------------------------------------------------------------------------------------+
      | Grants for root@%                                                                                   |
      +-----------------------------------------------------------------------------------------------------+
      | GRANT `fullaccess` TO `root`@`%` WITH ADMIN OPTION                                                  |
      | GRANT USAGE ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD 'xyz'                                       |
      | SET DEFAULT ROLE `fullaccess` FOR `root`@`%`                                                        |
      +-----------------------------------------------------------------------------------------------------+
      3 rows in set (0.000 sec)
       
      SHOW GRANTS FOR 'root'@'localhost';
      +---------------------------------------------------------------------+
      | Grants for root@localhost                                           |
      +---------------------------------------------------------------------+
      | GRANT `fullaccess` TO `root`@`localhost` WITH ADMIN OPTION          |
      | GRANT `mysql_exporter` TO `root`@`localhost` WITH ADMIN OPTION      |
      | GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION |
      | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION       |
      +---------------------------------------------------------------------+
      4 rows in set (0.000 sec)
      

      If i'm using the root@% user i'm unable to GRANT another role to another user.

      mysql --protocol=tcp --host=mariadb-g-0 --user=${MARIADB_ROOT_USER} --password=${MARIADB_ROOT_PASSWORD}
       
      SELECT CURRENT_USER;
      +--------------+
      | CURRENT_USER |
      +--------------+
      | root@%       |
      +--------------+
      1 row in set (0.000 sec)
       
      SELECT CURRENT_ROLE;
      +--------------+
      | CURRENT_ROLE |
      +--------------+
      | fullaccess   |
      +--------------+
      1 row in set (0.000 sec)
       
      GRANT mysql_exporter TO 'monitor'@'%';
      ERROR 1698 (28000): Access denied for user 'root'@'%'
      

      The root@localhost user is able to do that.

      mysql --protocol=tcp --host=localhost --user=${MARIADB_ROOT_USER}
       
      SELECT CURRENT_USER;
      +----------------+
      | CURRENT_USER   |
      +----------------+
      | root@localhost |
      +----------------+
      1 row in set (0.000 sec)
       
      SELECT CURRENT_ROLE;
      +--------------+
      | CURRENT_ROLE |
      +--------------+
      | NULL         |
      +--------------+
      1 row in set (0.000 sec)
       
      GRANT mysql_exporter TO 'monitor'@'%';
      Query OK, 0 rows affected (0.005 sec)
      

      Is this the expected behavior or a bug?

      regards,
      Birk

      PS: The same happens if i use

      GRANT ALL PRIVILEGES ON *.* TO `fullaccess` WITH GRANT OPTION
      

      instead of

      GRANT SUPER ON *.* TO `fullaccess` WITH GRANT OPTION
      

      Attachments

        Activity

          People

            anel Anel Husakovic
            businessbean Birk Bohne
            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.