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

Roles and Users longer than 6 characters

    XMLWordPrintable

    Details

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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                cvicentiu Vicentiu Ciorbaru
                Reporter:
                wchiquito William Chiquito
              • Votes:
                1 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: