Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13235

CLONE - Granted as a whole to roles, databases are not shown in SHOW DATABASES

Details

    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

          Activity

            mysolo Thierry Laurier added a comment - - edited

            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

            mysolo Thierry Laurier added a comment - - edited 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

            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.

            serg Sergei Golubchik added a comment - 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.

            People

              cvicentiu Vicențiu Ciorbaru
              mysolo Thierry Laurier
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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