[MDEV-13235] CLONE - Granted as a whole to roles, databases are not shown in SHOW DATABASES Created: 2017-07-03  Updated: 2017-07-03  Resolved: 2017-07-03

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.1.14, 10.1.17, 10.1.22, 10.1.24
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Thierry Laurier Assignee: Vicențiu Ciorbaru
Resolution: Cannot Reproduce Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-10463 Granted as a whole to roles, database... Closed

 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



 Comments   
Comment by Thierry Laurier [ 2017-07-03 ]

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

Comment by Sergei Golubchik [ 2017-07-03 ]

10.1.25 is not released yet. Your tests show "10.1.24" were the bug is definitely not yet fixed — it's fixed in 10.0.32 and 10.1.25.

Generated at Thu Feb 08 08:03:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.