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

Standard SELECT privilege allows access to temporal data and automatic columns

    XMLWordPrintable

Details

    Description

      I suppose all of this is by design, but none is currently documented. So, if everything looks as expected, please confirm and re-redirect to Documentation.

      1. Standard SELECT privilege allows access to historical data in system-versioned tables. It's not as obvious as it seems, because deleting history requires a special privilege other than ordinary DELETE, so one could expect the same for viewing history. However, it is not so, and there seems to be no way around it – whoever has SELECT access to a column, can also see all earlier versions of the data.

      create database db;
      create table db.t (a int) with system versioning;
      insert into db.t values (1);
      update db.t set a = 2;
      create user foo;
      grant select (a) on db.t to foo;
       
      --connect (con1,localhost,foo,,)
      select a from db.t for system_time all;
       
      # Cleanup
      --disconnect con1
      --connection default
      drop database db;
      drop user foo;
      

      10.5 6cee9b19

      MariaDB [(none)]> select a from db.t for system_time all;
      +------+
      | a    |
      +------+
      |    2 |
      |    1 |
      +------+
      2 rows in set (0.001 sec)
      

      2. Even with only column access, a user can also access the automatically generated period columns (row_start, row_end), which may look like a violation of the privilege system:

      MariaDB [(none)]> select a, row_start, row_end from db.t for system_time all;
      +------+----------------------------+----------------------------+
      | a    | row_start                  | row_end                    |
      +------+----------------------------+----------------------------+
      |    2 | 2020-07-03 19:57:29.516885 | 2038-01-19 05:14:07.999999 |
      |    1 | 2020-07-03 19:57:29.509583 | 2020-07-03 19:57:29.516885 |
      +------+----------------------------+----------------------------+
      2 rows in set (0.001 sec)
       
      MariaDB [(none)]> show grants;
      +-------------------------------------------+
      | Grants for foo@%                          |
      +-------------------------------------------+
      | GRANT USAGE ON *.* TO `foo`@`%`           |
      | GRANT SELECT (a) ON `db`.`t` TO `foo`@`%` |
      +-------------------------------------------+
      2 rows in set (0.001 sec)
      

      A workaround for this is creating the columns explicitly rather than relying on the automatic ones:

      create database db;
      create table db.t (a int, row_start timestamp(6) as row start invisible, row_end timestamp(6) as row end invisible, period for system_time(row_start,row_end)) with system versioning;
      insert into db.t values (1);
      update db.t set a = 2;
      create user foo;
      grant select (a) on db.t to foo;
       
      --connect (con1,localhost,foo,,)
      select a, row_start, row_end from db.t for system_time all;
       
      # Cleanup
      --disconnect con1
      --connection default
      drop database db;
      drop user foo;
      

      MariaDB [(none)]> select a, row_start, row_end from db.t for system_time all;
      ERROR 1143 (42000): SELECT command denied to user 'foo'@'localhost' for column 'row_start' in table 't'
      

      Attachments

        Activity

          People

            serg Sergei Golubchik
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.