Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.1.14, 10.1.17, 10.1.22, 10.1.24
-
None
Description
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 used (set as default, SHOW TABLES IN ..., DESCRIBE ...)
Test case:
--error 0,ER_CANNOT_USER
|
drop user beep@'%';
|
|
--error 0,ER_CANNOT_USER
|
drop role r1;
|
|
drop database if exists db;
|
|
create role r1;
|
create user beep@'%';
|
|
create database db;
|
create table db.t1 (i int);
|
grant select on db.* to r1;
|
grant r1 to beep@'%';
|
|
--connect (con1,localhost,beep,,)
|
show databases;
|
set role r1;
|
show databases;
|
Expected result (hopefully):
drop user beep@'%';
|
drop role r1;
|
drop database if exists db;
|
Warnings:
|
Note 1008 Can't drop database 'db'; database doesn't exist
|
create role r1;
|
create user beep@'%';
|
create database db;
|
create table db.t1 (i int);
|
grant select on db.* to r1;
|
grant r1 to beep@'%';
|
show databases;
|
Database
|
information_schema
|
test
|
set role r1;
|
show databases;
|
Database
|
db
|
information_schema
|
test
|
Failing:
@@ -16,6 +16,5 @@
|
set role r1;
|
show databases;
|
Database
|
-db
|
information_schema
|
test
|
|
mysqltest: Result length mismatch
|
Attachments
Issue Links
- relates to
-
MDEV-10463 Granted as a whole to roles, databases are not shown in SHOW DATABASES
-
- Closed
-
Not resolved on version 10.1.22, 10.1.24
16:55:36 root@localhost [(none)]> create user test identified by 'test';
Query OK, 0 rows affected (0.00 sec)
16:55:46 root@localhost [(none)]> show grants for test;
1 +-----------------------------------------------------------------------------------------------------+
2 | Grants for test@% |
3 +-----------------------------------------------------------------------------------------------------+
4 | GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
5 +-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
16:57:38 root@localhost [(none)]> create role role_test;
Query OK, 0 rows affected (0.00 sec)
16:57:53 root@localhost [(none)]> grant select on mysql.* to 'role_test';
Query OK, 0 rows affected (0.01 sec)
16:57:59 root@localhost [(none)]> show grants for role_test;
1 +------------------------------------------+
2 | Grants for role_test |
3 +------------------------------------------+
4 | GRANT USAGE ON *.* TO 'role_test' |
5 | GRANT SELECT ON `mysql`.* TO 'role_test' |
6 +------------------------------------------+
2 rows in set (0.00 sec)
16:58:45 root@localhost [(none)]> grant role_test to 'test';
Query OK, 0 rows affected (0.00 sec)
16:59:10 root@localhost [(none)]> show grants for test;
1 +-----------------------------------------------------------------------------------------------------+
2 | Grants for test@% |
3 +-----------------------------------------------------------------------------------------------------+
4 | GRANT role_test TO 'test'@'%' |
5 | GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
6 +-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql -hfr-laz-dev-maria-01 -utest -ptest
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17624
Server version: 10.1.24-MariaDB-1~jessie mariadb.org binary distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show grants;
+-----------------------------------------------------------------------------------------------------+
| Grants for test@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT role_test TO 'test'@'%' |
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> select current_role;
+--------------+
| current_role |
+--------------+
| NULL |
+--------------+
1 row in set (0.00 sec)
MariaDB [(none)]> set role role_test;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
fix patch in issues is not in source of 10.1.25