[MDEV-9614] Roles and Users longer than 6 characters - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.23, 10.1.11
Fix Version/s: 10.0.26, 10.1.15
Component/s: Admin statements, Authentication and Privilege System
Labels:
None
Environment:
Ubuntu 15.10 (64 bits)

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

is duplicated by

MDEV-9782 The "at" symbol (@) in user names breaks access to schemas

Closed

Activity

People

Assignee:: Vicențiu Ciorbaru

Reporter:: William Chiquito

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2016-02-23 15:46

Updated:: 2016-06-24 17:18

Resolved:: 2016-06-24 17:07

Git Integration

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