0

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?

  • 2
    There is no such possibility. Partitions are not privileges scope. – Akina Dec 20 '19 at 10:53
0

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)

  • Indeed, this is an alternative, but since it's a VIEW, it seems like you cannot make a FK pointing to it (which I would need for integrity checks), leading to my SO equivalent question – Xenos Dec 20 '19 at 10:27
  • @Xenos - Won't it suffice to have the FK point to the underlying table (testing)? – Rick James Dec 21 '19 at 1:56
  • No, because it would allow the value 200000 to be used (while not being in the allowed view) while still refusing the value 201000 (not in the testing table): so it would make the restricted DB user able to guess the ids in the testing table, meaning the rights are too "relaxed" – Xenos Dec 21 '19 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.