Disable manually setting auto-incremented ids in MySQL-CodePudding

I want to disallow users from inserting into a table product (which has auto-incremented ids) if they're setting the id manually.

So this would be valid (id generated automatically since it's set as AUTO_INCREMENT):

INSERT INTO product (name) VALUES ("Product1")

But this wouldn't (id being set manually):

INSERT INTO product (id, name) VALUES (10, "Product1")

Is this possible in any way?

CodePudding user response：

Trigger logic may help.

SET SESSION sql_mode := '';

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  val INT
) AUTO_INCREMENT = 123;

CREATE TRIGGER fail_explicit_id
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
  IF NEW.id <> 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Explicit ''id'' value is not allowed.';
  END IF;
END

INSERT INTO test (val) VALUES (111);
INSERT INTO test VALUES (NULL, 222);
INSERT INTO test VALUES (0, 333);
INSERT INTO test VALUES (DEFAULT, 444);

INSERT INTO test VALUES (456, 555);

Explicit 'id' value is not allowed.

SET SESSION sql_mode := CONCAT_WS(',', @@sql_mode, 'NO_AUTO_VALUE_ON_ZERO');
SELECT @@sql_mode;
INSERT INTO test VALUES (0, 666);

INSERT INTO test VALUES (0, 777);

Duplicate entry '0' for key 'test.PRIMARY'

SELECT * FROM test;

id	val
0	666
123	111
124	222
125	333
126	444

fiddle

CodePudding user response：

Give the user column-level permissions.

grant insert(`name`) on yourdatabase.product to theuser@thehost;

Then the user tries these:

mysql> INSERT INTO product (name) VALUES ("Product1");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO product (id, name) VALUES (10, "Product1");
ERROR 1143 (42000): INSERT command denied to user 'theuser'@'thehost' for column 'id' in table 'product'