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

'Roles' are created incorrectly on an install that was previously MySQL 5.7

    XMLWordPrintable

    Details

      Description

      When moving from MySQL 5.7 to MariaDB 10.2, roles are not usable. This seems to persist, even if the MariaDB instance is further upgraded (first noticed on a production 10.3 server).

      Steps I have taken to replicate this issue:

      • Create fresh EC2 instance, running Ubuntu 18.04 LTS
      • Run apt update / apt upgrade
      • Install mysql-server (installs 5.7.30-0ubuntu0.18.04.1)
      • Proceed through installation process accepting defaults, ensure once finished server has started
      • Stop the server, uninstall the MySQL package, install the MariaDB package as per here
      • Stop the server, start without grant tables, update root user to use 'unix_socket' instead of 'auth_socket' (via FLUSH PRIVILEGES; ALTER USER `root`@`localhost` IDENTIFIED VIA unix_socket;)

      The below test script now shows roles don't work (on a straight MariaDB install, `is_role` would be 'Y' and the `SET ROLE` would work as expected:

      MariaDB [(none)]> CREATE ROLE `aRole`;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> SET ROLE `aRole`;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> FLUSH PRIVILEGES;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> SET ROLE `aRole`;
      ERROR 1959 (OP000): Invalid role specification `aRole`
      MariaDB [(none)]> SELECT `User`, `is_role` FROM `mysql`.`user`;
      +------------------+---------+
      | User             | is_role |
      +------------------+---------+
      | root             | N       |
      | mysql.session    | N       |
      | mysql.sys        | N       |
      | debian-sys-maint | N       |
      | aRole            | N       |
      +------------------+---------+
      5 rows in set (0.00 sec)
      

      (Running the select prior to the FLUSH PRIVILEGES; has the same result)

        Attachments

          Activity

            People

            Assignee:
            danblack Daniel Black
            Reporter:
            rmhumphries Robert Humphries
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration