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?

1
  • 2
    There is no such possibility. Partitions are not privileges scope.
    – Akina
    Dec 20, 2019 at 10:53

1 Answer 1

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)

3
  • 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, 2019 at 10:27
  • @Xenos - Won't it suffice to have the FK point to the underlying table (testing)?
    – Rick James
    Dec 21, 2019 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, 2019 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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