[MDEV-10463] Granted as a whole to roles, databases are not shown in SHOW DATABASES Created: 2016-07-29  Updated: 2018-04-23  Resolved: 2017-06-15

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.1.14, 10.1.17
Fix Version/s: 10.1.25, 10.0.32, 10.2.7

Type: Bug Priority: Major
Reporter: Igor Pashev Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 4
Labels: None

Issue Links:
Blocks
is blocked by MDEV-15985 Granting roles to account does not wo... Closed
Relates
relates to MDEV-13235 CLONE - Granted as a whole to roles, ... Closed
Sprint: 10.1.18, 10.1.19, 10.1.22

 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 Igor Pashev [ 2016-07-29 ]

Dirty patch:

--- a/sql/sql_show.cc
 
+++ b/sql/sql_show.cc
 
@@ -4850,6 +4850,7 @@ int fill_schema_schemata(THD *thd, TABLE_LIST *tables, COND *cond)
 
   TABLE *table= tables->table;
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
 
   Security_context *sctx= thd->security_ctx;
 
+  ulong db_access= sctx->db_access;
 
 #endif
 
   DBUG_ENTER("fill_schema_shemata");
 
 
@@ -4891,9 +4892,20 @@ int fill_schema_schemata(THD *thd, TABLE_LIST *tables, COND *cond)
 
       continue;
 
     }
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
 
-    if (sctx->master_access & (DB_ACLS | SHOW_DB_ACL) ||
 
-       acl_get(sctx->host, sctx->ip, sctx->priv_user, db_name->str, 0) ||
 
-       !check_grant_db(thd, db_name->str))
 
+  if (test_all_bits(sctx->master_access, DB_ACLS))
 
+    db_access= DB_ACLS;
 
+  else
 
+  {
 
+    db_access= acl_get(sctx->host, sctx->ip, sctx->priv_user, db_name->str, FALSE);
 
+    if (sctx->priv_role[0])
 
+    {
 
+      /* include a possible currently set role for access */
 
+      db_access|= acl_get("", "", sctx->priv_role, db_name->str, FALSE);
 
+    }
 
+  }
 
+  if ((sctx->master_access & SHOW_DB_ACL) ||
 
+      (db_access & DB_ACLS) ||
 
+      !check_grant_db(thd, db_name->str))
 
 #endif
 
     {
 
       load_db_opt_by_name(thd, db_name->str, &create);

Comment by Vicențiu Ciorbaru [ 2016-10-26 ]

Investigated this a bit. It seems like no SHOW xxx command will work, as none are actually checking role privileges.

Comment by Vicențiu Ciorbaru [ 2017-05-22 ]

This issue affects 10.0 as well. Fixing the bug there, as it will get merged to 10.1 implicitly.

Generated at Thu Feb 08 07:42:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.