[MDEV-9614] Roles and Users longer than 6 characters Created: 2016-02-23  Updated: 2016-06-24  Resolved: 2016-06-24

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, Authentication and Privilege System
Affects Version/s: 10.0.23, 10.1.11
Fix Version/s: 10.0.26, 10.1.15

Type: Bug Priority: Major
Reporter: William Chiquito Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 1
Labels: None
Environment:

Ubuntu 15.10 (64 bits)

Issue Links:
Duplicate
is duplicated by MDEV-9782 The "at" symbol (@) in user names bre... Closed
Sprint: 10.0.25, 10.0.26

 Description    

$ mysql -u root -p
 
Enter password:
 
 
 
MariaDB [(none)]> DROP DATABASE IF EXISTS `test_db`;
 
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> -- DROP ROLE `client`;
 
MariaDB [(none)]> -- DROP USER `john`@`%`;
 
MariaDB [(none)]> -- DROP USER `usertestjohn`@`%`;
 
 
 
MariaDB [(none)]> CREATE USER `john`@`%`;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> CREATE USER `usertestjohn`@`%`;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> CREATE ROLE `client`;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> CREATE DATABASE `test_db`;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> CREATE TABLE `test_db`.`t0`(`c0` INT);
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> GRANT SELECT ON `test_db`.`t0` TO `client`;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> GRANT `client` TO `john`@`%`;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> GRANT `client` TO `usertestjohn`@`%`;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW GRANTS FOR `john`@`%`;
 
+----------------------------------+
 
| Grants for john@%                |
 
+----------------------------------+
 
| GRANT client TO 'john'@'%'       |
 
| GRANT USAGE ON *.* TO 'john'@'%' |
 
+----------------------------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW GRANTS FOR `usertestjohn`@`%`;
 
+------------------------------------------+
 
| Grants for usertestjohn@%                |
 
+------------------------------------------+
 
| GRANT client TO 'usertestjohn'@'%'       |
 
| GRANT USAGE ON *.* TO 'usertestjohn'@'%' |
 
+------------------------------------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW GRANTS FOR `client`;
 
+--------------------------------------------+
 
| Grants for client                          |
 
+--------------------------------------------+
 
| GRANT USAGE ON *.* TO 'client'             |
 
| GRANT SELECT ON `test_db`.`t0` TO 'client' |
 
+--------------------------------------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW CREATE TABLE mysql.user\G
 
*************************** 1. row ***************************
 
       Table: user
 
Create Table: CREATE TABLE `user` (
 
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
 
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
 
  `ssl_cipher` blob NOT NULL,
 
  `x509_issuer` blob NOT NULL,
 
  `x509_subject` blob NOT NULL,
 
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
 
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
 
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
 
  `max_user_connections` int(11) NOT NULL DEFAULT '0',
 
  `plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
 
  `authentication_string` text COLLATE utf8_bin NOT NULL,
 
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `max_statement_time` decimal(12,6) NOT NULL DEFAULT '0.000000',
 
  PRIMARY KEY (`Host`,`User`)
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> SELECT user, host FROM mysql.user\G
 
*************************** 1. row ***************************
 
user: client
 
host: 
*************************** 2. row ***************************
 
user: john
 
host: %
 
*************************** 3. row ***************************
 
user: usertestjohn
 
host: %
 
*************************** 4. row ***************************
 
user: root
 
host: 127.0.0.1
 
*************************** 5. row ***************************
 
user: root
 
host: ::1
 
*************************** 6. row ***************************
 
user: debian-sys-maint
 
host: localhost
 
*************************** 7. row ***************************
 
user: root
 
host: localhost
 
*************************** 8. row ***************************
 
user: root
 
host: ubuntu
 
8 rows in set (0.00 sec)
 
 
 
$ mysql -u john -p
 
Enter password:
 
 
 
MariaDB [(none)]> SHOW DATABASES;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
+--------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> SELECT `c0` FROM `test_db`.`t0`;
 
ERROR 1142 (42000): SELECT command denied to user 'john'@'localhost' for table 't0'
 
 
 
MariaDB [(none)]> SHOW GRANTS;
 
+----------------------------------+
 
| Grants for john@%                |
 
+----------------------------------+
 
| GRANT client TO 'john'@'%'       |
 
| GRANT USAGE ON *.* TO 'john'@'%' |
 
+----------------------------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SET ROLE `client`;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW GRANTS;
 
+--------------------------------------------+
 
| Grants for john@%                          |
 
+--------------------------------------------+
 
| GRANT client TO 'john'@'%'                 |
 
| GRANT USAGE ON *.* TO 'john'@'%'           |
 
| GRANT USAGE ON *.* TO 'client'             |
 
| GRANT SELECT ON `test_db`.`t0` TO 'client' |
 
+--------------------------------------------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW DATABASES;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
| test_db            |
 
+--------------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SELECT `c0` FROM `test_db`.`t0`;
 
Empty set (0.00 sec)
 
 
 
MariaDB [(none)]> USE `test_db` -A;
 
Database changed
 
MariaDB [test_db]>
 
 
 
$ mysql -u usertestjohn -p
 
Enter password:
 
 
 
MariaDB [(none)]> SHOW DATABASES;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
+--------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> SELECT `c0` FROM `test_db`.`t0`;
 
ERROR 1142 (42000): SELECT command denied to user 'usertestjohn'@'localhost' for table 't0'
 
 
 
MariaDB [(none)]> SHOW GRANTS;
 
+------------------------------------------+
 
| Grants for usertestjohn@%                |
 
+------------------------------------------+
 
| GRANT client TO 'usertestjohn'@'%'       |
 
| GRANT USAGE ON *.* TO 'usertestjohn'@'%' |
 
+------------------------------------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SET ROLE `client`;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW GRANTS;
 
+--------------------------------------------+
 
| Grants for usertestjohn@%                  |
 
+--------------------------------------------+
 
| GRANT client TO 'usertestjohn'@'%'         |
 
| GRANT USAGE ON *.* TO 'usertestjohn'@'%'   |
 
| GRANT USAGE ON *.* TO 'client'             |
 
| GRANT SELECT ON `test_db`.`t0` TO 'client' |
 
+--------------------------------------------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW DATABASES;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
+--------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> SELECT `c0` FROM `test_db`.`t0`;
 
Empty set (0.00 sec)
 
 
 
MariaDB [(none)]> USE `test_db`;
 
ERROR 1044 (42000): Access denied for user 'usertestjohn'@'%' to database 'test_db'



 Comments   
Comment by Elena Stepanova [ 2016-02-23 ]

I don't have any viable theory at the moment, suggestions below are just fishing. The output in the description, if it's unabridged, clearly indicates a problem, but the very same set of commands works just fine for me, so we must be missing something important.

If by any chance you could grant us temporary access to the running server (either to the box via ssh, or to the MariaDB instance by creating a superuser and giving us credentials), it would most likely make the digging easier.

Meanwhile, could you please

  • run and paste the output of 

    SHOW CREATE TABLE mysql.user;
    		 
    SELECT user, host FROM mysql.user; # you can obfuscate rows that you find irrelevant and confidential

  • add SHOW GRANTS before and after you run SET ROLE
  • attach your cnf files;
  • check your error log for any errors or warnings, and paste them if there are any.

Thanks.

Comment by William Chiquito [ 2016-02-23 ]
  • Description updated with statements requested.
  • It's a test machine with a fresh install both Ubuntu and MariaDB (10.1.11), unchanged in cnf files.
  • Nothing in the error log.
Comment by Elena Stepanova [ 2016-02-23 ]

Okay, thanks, I can reproduce it now.
The reason why I wasn't able to before is apparently that in a bintar-like installation test_% databases are open for everyone, but Debian/Ubuntu packages are a bit more secure in this regard and disable this test access.

Comment by Elena Stepanova [ 2016-02-23 ]

cvicentiu,

It can be reproduced as described, but before doing this, you also need to execute the following (assuming you are on a source build or alike):

delete from mysql.user where user='';
 
delete from mysql.db where db like 'test%';
 
flush privileges;

Comment by Igor Pashev [ 2016-04-12 ]

It's likely related: "long" user get SELECT command denied to user if database (not table - not checked) does not exist, while it should be Table 'foo.bar' doesn't exist

Comment by Igor Pashev [ 2016-04-18 ] 

diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
 
index d34f04c..cbb9c7e 100644
 
--- a/sql/sql_acl.cc
 
+++ b/sql/sql_acl.cc
 
@@ -7383,7 +7383,7 @@ bool check_grant_db(THD *thd, const char *db)
 
 
   /*
 
      If a role is set, we need to check for privileges
 
-     here aswell
 
+     here as well
 
   */
 
   if (sctx->priv_role[0])
 
   {
 
@@ -7409,7 +7409,7 @@ bool check_grant_db(THD *thd, const char *db)
 
     }
 
     if (sctx->priv_role[0] &&
 
         len2 < grant_table->key_length &&
 
-        !memcmp(grant_table->hash_key,helping2,len) &&
 
+        !memcmp(grant_table->hash_key,helping2,len2) &&
 
         (!grant_table->host.hostname || !grant_table->host.hostname[0]))
 
     {
 
       error= FALSE; /* Found role match */

Comment by Igor Pashev [ 2016-04-30 ]

There is one more issue: if a role is granted on the whole database (SELECT ON db.* TO role1;), and a user is granted this role (GRANT role1 TO user1;), then that databases is not listed by SHOW DATABASES;, but still can be SELECT'ed and set as default database.

Comment by Vicențiu Ciorbaru [ 2016-05-09 ]

Ah, the len2 typo is subtle. The way it leads to the failure is very specific.

If the role name is longer or equal to the user name, then the memcmp call will compare less bytes than it has to, in which case the comparison will pass, if there is a grant that matches. Note however that, due to the nature of our key encoding, we will also match for a grant to a database table that shares the prefix of our desired database.

On the other hand, if the role name is shorter than the user name, as is the case here, it might fail. We end up comparing more bytes than necessary in memcmp. Depending on garbage memory layout it might pass or not.

Comment by Vicențiu Ciorbaru [ 2016-06-24 ]

Fixed with
5fd8087

Generated at Thu Feb 08 07:36:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.