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

SET ROLE does not properly grant privileges.

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0, 10.1, 10.2, 10.2.8
    • Fix Version/s: 10.0.34, 10.1.30, 10.2.12
    • Labels:
    • Environment:
      Docker:
      Version 17.06.0-ce-mac18 (18433)
      Channel: stable
      d9b66511e0

      MacOS Sierra 10.12.6

      MariaDB Docker Image ID 58730544b81b (mariadb:latest at time of filing).
    • Sprint:
      10.1.30

      Description

      Running SET ROLE statement still yields permission denied when trying to create a database.

      root@6d623b7b5bcd:/# mysql -u root -ppassword
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 8
      Server version: 10.2.8-MariaDB-10.2.8+maria~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)]> create role s;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> grant select, insert, update, delete, lock tables, execute on t.* to s;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> create role a;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> grant s to a;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> grant all on t.* to a;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> create user u;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> grant a to u;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> grant s to u;
      Query OK, 0 rows affected (0.00 sec)
      

      Then,

      root@6d623b7b5bcd:/# mysql -u u
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 9
      Server version: 10.2.8-MariaDB-10.2.8+maria~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 grants;
      +-------------------------------+
      | Grants for u@%                |
      +-------------------------------+
      | GRANT a TO 'u'@'%'            |
      | GRANT s TO 'u'@'%'            |
      | GRANT USAGE ON *.* TO 'u'@'%' |
      +-------------------------------+
      3 rows in set (0.00 sec)
       
      MariaDB [(none)]> create database t;
      ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't'
      MariaDB [(none)]> set role a;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> create database t;
      ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't'
      MariaDB [(none)]> show grants;
      +----------------------------------------------------------------------------+
      | Grants for u@%                                                             |
      +----------------------------------------------------------------------------+
      | GRANT a TO 'u'@'%'                                                         |
      | GRANT s TO 'u'@'%'                                                         |
      | GRANT USAGE ON *.* TO 'u'@'%'                                              |
      | GRANT s TO 'a'                                                             |
      | GRANT USAGE ON *.* TO 'a'                                                  |
      | GRANT ALL PRIVILEGES ON `t`.* TO 'a'                                       |
      | GRANT USAGE ON *.* TO 's'                                                  |
      | GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `t`.* TO 's' |
      +----------------------------------------------------------------------------+
      8 rows in set (0.00 sec)
       
      MariaDB [(none)]> select current_role;
      +--------------+
      | current_role |
      +--------------+
      | a            |
      +--------------+
      1 row in set (0.00 sec)
      

      Granting all privileges directly to the user allows the user to create the database.

        Attachments

          Activity

            People

            • Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              dvonhand Dusty VonHandorf
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: