Sample Header Ad - 728x90

Can I grant access to a MySQL user for a single partition of a table?

0 votes
1 answer
510 views
Is there a way, in MySQL, to grant access to a single partition of a table to a specific user? I see no way in the doc for that, but maybe I'm missing it... Here's a test case:
CREATE TABLE testing (
	id INT UNSIGNED NOT NULL
)
PARTITION BY RANGE(id) (
	PARTITION p0 VALUES LESS THAN (1000),
	PARTITION p2 VALUES LESS THAN MAXVALUE
);
INSERT INTO testing (id) VALUES (2),(20),(200),(2000),(20000),(200000);
SELECT * FROM testing;

CREATE USER 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT USAGE ON *.* TO 'foo'@'localhost';
GRANT SELECT ON my_test.testing TO 'foo'@'localhost';
FLUSH PRIVILEGES;

-- Now use foo@localhost: you shouldn't see 2k, 20k and 200k values
SELECT * FROM testing;
What would the proper GRANT syntax be?
Asked by Xenos (195 rep)
Dec 20, 2019, 09:25 AM
Last activity: May 15, 2025, 01:09 AM