Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.23, 10.1.11
-
None
-
Ubuntu 15.10 (64 bits)
-
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