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

Trigger on normal table can't insert into CONNECT engine table - Access Denied

Details

    • 10.0.25

    Description

      If you add an AFTER INSERT trigger on an Innodb table that simply tries to insert a record into a CONNECT table, adding a record to the Innodb table fails with:

      Access denied for user 'root'@'localhost' (using password: YES)

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          Thanks for the report.

          create table tcon (i int) engine=Connect table_type=DOS file_name='tcon.dos';
          		 
          # Query OK, 0 rows affected (0.14 sec)
          		 
           
          		 
          create table tin (i int) engine=InnoDB;
          		 
          # Query OK, 0 rows affected (0.49 sec)
          		 
           
          		 
          create trigger tr after insert on tin for each row insert into tcon values (new.i);
          		 
          # Query OK, 0 rows affected (0.04 sec)
          		 
           
          		 
          insert into tin values (1);
          		 
          # ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
          		 
           
          		 
          # insert into tcon works all right:
          		 
          insert into tcon values (1);
          		 
          # Query OK, 1 row affected (0.00 sec)

          elenst Elena Stepanova added a comment - Thanks for the report. create table tcon (i int) engine=Connect table_type=DOS file_name='tcon.dos'; # Query OK, 0 rows affected (0.14 sec)   create table tin (i int) engine=InnoDB; # Query OK, 0 rows affected (0.49 sec)   create trigger tr after insert on tin for each row insert into tcon values (new.i); # Query OK, 0 rows affected (0.04 sec)   insert into tin values (1); # ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)   # insert into tcon works all right: insert into tcon values (1); # Query OK, 1 row affected (0.00 sec)
          bertrandop Olivier Bertrand added a comment -

          The error occurs in the Connect function check_privileges in which a test is done to check whether the user has the FILE privilege. This test is made against the value of table->grant.privilege that is supposed to contain a bitmap of all the user privileges.

          What happens is in the case of the table written from a trigger, this variable is apparently not set. This is why the writing fails and Connect issues a message saying "This operation requires the FILE privilege" that can be seen on the log file or on the console.

          This reaction is normal and this does not seem to be a Connect bug but a MariaDB bug.

          bertrandop Olivier Bertrand added a comment - The error occurs in the Connect function check_privileges in which a test is done to check whether the user has the FILE privilege. This test is made against the value of table->grant.privilege that is supposed to contain a bitmap of all the user privileges. What happens is in the case of the table written from a trigger, this variable is apparently not set. This is why the writing fails and Connect issues a message saying "This operation requires the FILE privilege" that can be seen on the log file or on the console. This reaction is normal and this does not seem to be a Connect bug but a MariaDB bug.
          rdyas Robert Dyas added a comment -

          Any idea when this might be resolved? Important to some of our use cases.

          rdyas Robert Dyas added a comment - Any idea when this might be resolved? Important to some of our use cases.
          serg Sergei Golubchik added a comment -

          Hopefully, in the next 10.1 release

          serg Sergei Golubchik added a comment - Hopefully, in the next 10.1 release

          People

            serg Sergei Golubchik
            rdyas Robert Dyas
            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.