Mysql – grant access to a MySQL user for a single partition of a table

access-controlMySQLpartitioning

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?

Best Answer

I'm not entirely sure you can do what you are after.

I would have thought a better option would be to create a view that only shows the data you want:

CREATE VIEW view_testing AS SELECT id FROM testing WHERE id <= 1000;

Then give your user only permission to access that view:

CREATE USER 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT SELECT ON my_test.view_testing TO 'foo'@'localhost';

(n.b. the user creating the view needs access to the underlying table)

Related Question