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

SELECT Grant restricts visibility to referential_constraints table

Details

    Description

      After upgrading from 10.3.34 to 10.11.8, a SELECT Granted user is no more able to read rows from the referential_constraints table.

      I needed to change the GRANT from :

      CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly';
      		 
      GRANT SELECT ON *.* TO 'readonly'@'%';
      		 
      FLUSH PRIVILEGES;

      To :

      CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly';
      		 
      GRANT USAGE ON *.* TO `readonly`@`%`;
      		 
      GRANT SELECT, REFERENCES, CREATE TEMPORARY TABLES, LOCK TABLES, SHOW VIEW ON `some_db`.* TO `readonly`@`%`;
      		 
      FLUSH PRIVILEGES;

      With SELECT GRANT, this query returns empty set : 

      SELECT * FROM `REFERENTIAL_CONSTRAINTS

      With the new GRANT, the same query returns the expected results.

      Maybe this is a new feature, but I could not find any reference of this change in the changelogs.

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32500 Information schema leaks table names and structure to unauthorized users

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            nguyenk Kevin NGUYEN created issue -
            nguyenk Kevin NGUYEN made changes -
            Field Original Value New Value
            Description After upgrading from 10.3.34 to 10.11.8, a SELECT Granted user is no more able to read rows from the referential_constraints table.

            I needed to change the GRANT from :

            {{CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly';
            GRANT SELECT ON *.* TO 'readonly'@'%';
            FLUSH PRIVILEGES;}}

            To :

            {{CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly';
            GRANT USAGE ON *.* TO `readonly`@`%`;
            GRANT SELECT, REFERENCES, CREATE TEMPORARY TABLES, LOCK TABLES, SHOW VIEW ON `some_db`.* TO `readonly`@`%`;
            FLUSH PRIVILEGES;}}

            With SELECT GRANT, this query returns empty set :
            {{SELECT * FROM `REFERENTIAL_CONSTRAINTS}}

            With new GRANST, the same query returns the expected results.

            Maybe this is a new feature, but I cound not find any reference of this change in the changelogs.
            		After upgrading from 10.3.34 to 10.11.8, a SELECT Granted user is no more able to read rows from the referential_constraints table.

            I needed to change the GRANT from :

            {{CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly';
            GRANT SELECT ON *.* TO 'readonly'@'%';
            FLUSH PRIVILEGES;}}

            To :

            {{CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly';
            GRANT USAGE ON *.* TO `readonly`@`%`;
            GRANT SELECT, REFERENCES, CREATE TEMPORARY TABLES, LOCK TABLES, SHOW VIEW ON `some_db`.* TO `readonly`@`%`;
            FLUSH PRIVILEGES;}}

            With SELECT GRANT, this query returns empty set :
            {{SELECT * FROM `REFERENTIAL_CONSTRAINTS}}

            With the new GRANT, the same query returns the expected results.

            Maybe this is a new feature, but I cound not find any reference of this change in the changelogs.
            serg Sergei Golubchik made changes -
            Description After upgrading from 10.3.34 to 10.11.8, a SELECT Granted user is no more able to read rows from the referential_constraints table.

            I needed to change the GRANT from :

            {{CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly';
            GRANT SELECT ON *.* TO 'readonly'@'%';
            FLUSH PRIVILEGES;}}

            To :

            {{CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly';
            GRANT USAGE ON *.* TO `readonly`@`%`;
            GRANT SELECT, REFERENCES, CREATE TEMPORARY TABLES, LOCK TABLES, SHOW VIEW ON `some_db`.* TO `readonly`@`%`;
            FLUSH PRIVILEGES;}}

            With SELECT GRANT, this query returns empty set :
            {{SELECT * FROM `REFERENTIAL_CONSTRAINTS}}

            With the new GRANT, the same query returns the expected results.

            Maybe this is a new feature, but I cound not find any reference of this change in the changelogs.
            		After upgrading from 10.3.34 to 10.11.8, a SELECT Granted user is no more able to read rows from the referential_constraints table.

            I needed to change the GRANT from :

            {code:sql}CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly';
            GRANT SELECT ON *.* TO 'readonly'@'%';
            FLUSH PRIVILEGES;{code}

            To :

            {code:sql}CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly';
            GRANT USAGE ON *.* TO `readonly`@`%`;
            GRANT SELECT, REFERENCES, CREATE TEMPORARY TABLES, LOCK TABLES, SHOW VIEW ON `some_db`.* TO `readonly`@`%`;
            FLUSH PRIVILEGES;{code}

            With SELECT GRANT, this query returns empty set :
            {code:sql}SELECT * FROM `REFERENTIAL_CONSTRAINTS{code}

            With the new GRANT, the same query returns the expected results.

            Maybe this is a new feature, but I could not find any reference of this change in the changelogs.
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Component/s Authentication and Privilege System [ 13101 ]
            Fix Version/s N/A [ 14700 ]
            Assignee Sergei Golubchik [ serg ]
            Resolution Not a Bug [ 6 ]
            Status Open [ 1 ] Closed [ 6 ]

            People

              serg Sergei Golubchik
              nguyenk Kevin NGUYEN
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.