maria-discuss team mailing list archive

[Robin Peiremans <robin.peiremans@xxxxxx>]

Hi

I've created a role with both upper and lowercase name, resulting in some weird behavior (output and test case below).

The first thing I noticed is that you can create roles with the same name but different case (test_role and test_ROLE in below example), but you can't grant both to the same user as that results in an error 1961 when granting the second role. Imo this indicates that not all the roles code is case-sensitive.

The second weird thing is that when you get the error 1961 in above scenario, the first role (that was granted successfully) is gone from the show grants output. It is not revoked (the user can still use it), it just isn't shown anymore until I flush privileges.

Can this be considered a bug, or am I missing something here?
MariaDB version: 10.1.13-MariaDB
OS: Red Hat 7.2

Kind regards
Robin



Output and test case:
MariaDB [mysql]> create role test_ROLE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> create role test_role;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> grant select on mysql.* to test_role;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user, host from user where is_role='y' and user like 'test%';
+-----------+------+
| user      | host |
+-----------+------+
| test_ROLE |      |
| test_role |      |
+-----------+------+
2 rows in set (0.00 sec)

MariaDB [mysql]> grant test_role to testuser;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show grants for testuser;
+------------------------------------------------------------------------+
| Grants for testuser@%                                                   |
+------------------------------------------------------------------------+
| GRANT test_role TO 'testuser'@'%'                                       |
| GRANT USAGE ON *.* TO 'testuser'@'%' IDENTIFIED VIA pam USING 'mariadb' |
+------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [mysql]> grant test_ROLE to testuser;
ERROR 1961 (HY000): Cannot grant role 'test_ROLE' to: 'testuser'.
MariaDB [mysql]> show grants for testuser;
+------------------------------------------------------------------------+
| Grants for testuser@%                                                   |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%' IDENTIFIED VIA pam USING 'mariadb' |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [mysql]> select * from roles_mapping where user='testuser';
+-----------+---------+-----------+--------------+
| Host      | User    | Role      | Admin_option |
+-----------+---------+-----------+--------------+
| %         | testuser | test_role | N            |
+-----------+---------+-----------+--------------+
3 rows in set (0.00 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show grants for testuser;
+------------------------------------------------------------------------+
| Grants for testuser@%                                                   |
+------------------------------------------------------------------------+
| GRANT test_role TO 'testuser'@'%'                                       |
| GRANT USAGE ON *.* TO 'testuser'@'%' IDENTIFIED VIA pam USING 'mariadb' |
+------------------------------------------------------------------------+
2 rows in set (0.00 sec)


This e-mail and any attached files are confidential and may be legally privileged. If you are not the addressee, any disclosure, reproduction, copying, distribution, or other dissemination or use of this communication is strictly prohibited. If you have received this transmission in error please notify KBC immediately and then delete this e-mail.
KBC does not accept liability for the correct and complete transmission of the information, nor for any delay or interruption of the transmission, nor for damages arising from the use of or reliance on the information.
All e-mail messages addressed to, received or sent by KBC or KBC employees are deemed to be professional in nature. Accordingly, the sender or recipient of these messages agrees that they may be read by other KBC employees than the official recipient or sender in order to ensure the continuity of work-related activities and allow supervision thereof.