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

Role permissions don't work with CTEs

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.2.6, 10.2.7, 10.2.8
    • N/A
    • Optimizer - CTE
    • RHEL7.

    Description

      When I grant a select on a table directly to the user, the user is able to use CTEs successfully.

      However, when I grant that same select to a role, and then grant the role to the user, the user CANNOT run CTEs successfully.

      This user (eg4) has been granted select on the Managers table through the mqm_read role:

       GRANT SELECT ON `lahman2016`.`Managers` TO 'mqm_read';
      

      But when the user tries to use a CTE, she gets a SELECT command denied error:

      MariaDB [lahman2016]> WITH stt as (SELECT playerID, rank FROM Managers LIMIT 10) SELECT * FROM stt;
      ERROR 1142 (42000): SELECT command denied to user 'eg49'@'localhost' for table 'stt'
       
      Granting SELECT on the table directly to user, the user can do the CTE:
       
      MariaDB [lahman2016]> grant select on Managers to 'eg49'@'%';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [lahman2016]> WITH stt as (SELECT playerID, rank FROM Managers LIMIT 10) SELECT * FROM stt;
      +-----------+------+
      | playerID  | rank |
      +-----------+------+
      | wrighha01 |    3 |
      | woodji01  |    2 |
      ...
      

      This seems to defeat the whole purpose of roles.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              bethgood Elizabeth Good
              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.