Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.13, 10.0(EOL), 10.1(EOL)
-
None
-
10.1.18, 10.1.19
Description
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.
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)
|
Thanks for the report and test case.
on a debug build the same scenario causes assertion failure:
Stack trace from 10.0 f81f985f37
mysqld: /data/src/10.0/sql/sql_acl.cc:2395: void undo_add_role_user_mapping(ACL_USER_BASE*, ACL_ROLE*): Assertion `role == *(ACL_ROLE**)pop' failed.160905 23:31:59 [ERROR] mysqld got signal 6 ;#7 0x00007fbaf643b312 in __GI___assert_fail (assertion=0xe7fd7e "role == *(ACL_ROLE**)pop", file=0xe7f4b0 "/data/src/10.0/sql/sql_acl.cc", line=2395, function=0xe81be0 <undo_add_role_user_mapping(ACL_USER_BASE*, ACL_ROLE*)::__PRETTY_FUNCTION__> "void undo_add_role_user_mapping(ACL_USER_BASE*, ACL_ROLE*)") at assert.c:101#8 0x00000000005c2a22 in undo_add_role_user_mapping (grantee=0x7fbaeed923b0, role=0x7fbaf13b6220) at /data/src/10.0/sql/sql_acl.cc:2395#9 0x00000000005ce346 in mysql_grant_role (thd=0x7fbaf1bdd070, list=..., revoke=false) at /data/src/10.0/sql/sql_acl.cc:6159#10 0x000000000064c261 in mysql_execute_command (thd=0x7fbaf1bdd070) at /data/src/10.0/sql/sql_parse.cc:4257#11 0x0000000000651e18 in mysql_parse (thd=0x7fbaf1bdd070, rawbuf=0x7fbaeecfa088 "grant test_ROLE to testuser", length=27, parser_state=0x7fbaf8700650) at /data/src/10.0/sql/sql_parse.cc:6576#12 0x0000000000644918 in dispatch_command (command=COM_QUERY, thd=0x7fbaf1bdd070, packet=0x7fbaf1bd3071 "grant test_ROLE to testuser", packet_length=27) at /data/src/10.0/sql/sql_parse.cc:1309#13 0x0000000000643bdb in do_command (thd=0x7fbaf1bdd070) at /data/src/10.0/sql/sql_parse.cc:999#14 0x0000000000761efa in do_handle_one_connection (thd_arg=0x7fbaf1bdd070) at /data/src/10.0/sql/sql_connect.cc:1378#15 0x0000000000761c6c in handle_one_connection (arg=0x7fbaf1bdd070) at /data/src/10.0/sql/sql_connect.cc:1293#16 0x00000000009faa88 in pfs_spawn_thread (arg=0x7fbaf0591370) at /data/src/10.0/storage/perfschema/pfs.cc:1860#17 0x00007fbaf833d0a4 in start_thread (arg=0x7fbaf8701700) at pthread_create.c:309#18 0x00007fbaf64f587d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:11110.2 is also affected.