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

SHOW CREATE TABLE needs INSERT privilege when using sequence as default

    XMLWordPrintable

Details

    • Not for Release Notes

    Description

      Since at least 10.11.13, possibly 10.11.12, issuing a `SHOW CREATE TABLE` statement requires `INSERT` privileges if a column using a sequence for its default values.

      Sequence and table:

      create sequence s;
      create table t (v int default nextval(s));
      

      MariaDB 10.11.11:

      MariaDB [test]> show grants;
      +------------------------------------------+
      | Grants for foo@localhost                 |
      +------------------------------------------+
      | GRANT SELECT ON *.* TO `foo`@`localhost` |
      +------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> show create table t\G
      *************************** 1. row ***************************
             Table: t
      Create Table: CREATE TABLE `t` (
        `v` int(11) DEFAULT nextval(`test`.`s`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
      1 row in set (0.000 sec)
      

      MariaDB 10.11.13

      MariaDB [test]> show create table t\G
      ERROR 1142 (42000): INSERT command denied to user 'foo'@'localhost' for table `test`.`s`
      

      This breaks for example the snapshotting in Debezium if any such table is present and the user for Debezium does not have `INSERT` privileges.

      This is possibly caused by the fix for https://jira.mariadb.org/browse/MDEV-36413

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              dotdash Björn Steinbrink
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.