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

Roles and Users longer than 6 characters

Details

    • 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'

      Attachments

        Issue Links

          Activity

            ip1981 Igor Pashev added a comment -

            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

            ip1981 Igor Pashev added a comment - 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
            ip1981 Igor Pashev added a comment -

            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 */
            

            ip1981 Igor Pashev added a comment - 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 */
            ip1981 Igor Pashev added a comment -

            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.

            ip1981 Igor Pashev added a comment - 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.

            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.

            cvicentiu Vicențiu Ciorbaru added a comment - 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.

            Fixed with
            5fd8087

            cvicentiu Vicențiu Ciorbaru added a comment - Fixed with 5fd8087

            People

              cvicentiu Vicențiu Ciorbaru
              wchiquito William Chiquito
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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