Disable manually setting auto-incremented ids in MySQL

Question

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?

Are the users working with the database directly? No backend/CURL program? — Jetto Martínez, Jan 04 '23 at 21:02

score 2 · Answer 1 · answered Jan 04 '23 at 21:24

2

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'

answered Jan 04 '23 at 21:24

Valerie Parham-Thompson

1,516
1
11
21

That's pretty good, thank you! I didn't know there were column-level permissions. I accepted Akina's answer because it seemed a bit cleaner, hope you understand! – Matías Santurio Jan 05 '23 at 02:08
2

Yes, of course. Be careful with triggers, though... they have been known to cause serious performance issues. – Valerie Parham-Thompson Jan 05 '23 at 02:17
1

For anyone wondering, it's not currently possible to grant table- or database-wide insert permission and then make an exception for specific columns. See [How to "subtract" privileges in MySQL](https://stackoverflow.com/q/8131849/1662230) and [ALL PRIVILEGES except one table in MySQL](https://stackoverflow.com/q/24204504/1662230) for more details. – Magnus Lind Oxlund Mar 03 '23 at 12:17

score 1 · Accepted Answer · answered Jan 04 '23 at 21:24

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

Disable manually setting auto-incremented ids in MySQL

2 Answers2