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




      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)




            danblack Daniel Black
            rmhumphries Robert Humphries
            0 Vote for this issue
            5 Start watching this issue



              Git Integration

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