INSERT ... RETURNING exposes columns for which the user lacks SELECT privilege

-- Login as ROOT user

CREATE USER regular;

GRANT INSERT ON *.* TO regular;

CREATE DATABASE test;

DROP TABLE IF EXISTS test.t_trigger_test;

CREATE TABLE test.t_trigger_test (

  id INT AUTO_INCREMENT PRIMARY KEY,

  name VARCHAR(50),

  note VARCHAR(100)

);

-- Create trigger (ROOT user)

DELIMITER //

CREATE TRIGGER test.trg_before_insert

BEFORE INSERT ON test.t_trigger_test

FOR EACH ROW

BEGIN

  SET NEW.name = CONCAT('BEFORE_', NEW.name);

END //

DELIMITER ;

-- Login as regular user

-- INSERT command (regular user)

INSERT INTO test.t_trigger_test (name) VALUES ('Alice') RETURNING *;

-- ERROR 1143 (42000): SELECT command denied to user 'regular'@'localhost' for column 'id' in table 't_trigger_test' (correct behavior)

INSERT INTO test.t_trigger_test (name) VALUES ('Alice') RETURNING id, name, note;

-- Expected behavior: SELECT command denied

-- Actual:

/* 

1. Returns the auto-increment id, which was not included in the INSERT statement. 

2. Returns 'BEFORE_Alice', the name rewritten by the trigger. 

3. Since 'regular' only has INSERT permission, it should not have access to this data.

+----+--------------+------+

| id | name         | note |

+----+--------------+------+

|  1 | BEFORE_Alice | NULL |

+----+--------------+------+

*/