0

Is it possible to deny selecting from the partition for a specific user?

The thing is that I need to implement a feature: 1) A root user should have access to all data from 'products' table and its partitions 2) A noroot user should have access to 'products' table and its specific partition

I have such a schema:

CREATE TABLE `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

ALTER TABLE products  PARTITION BY RANGE (id)
  (
    PARTITION p1 VALUES LESS THAN (5),
    PARTITION p2 VALUES LESS THAN MAXVALUE 
  );

So in a perfect world, I'd like to GRANT ALL PRIVILEGES to root user so that they can select all data from the 'products' table(from p1, p2 partitions) like: 

SELECT * FROM products;

But for noroot user, I'd like to GRANT ALL PRIVILEGES to specific partition(p1) so that they can select data from the 'products' table. Selecting data from 'products' table means that MYSQL goes ONLY to granted partitions(for our case it is p1)

Guys, is it possible? Probably you have another solution, but keep in mind that I need 'common interface' for selecting data.

slip
  • 63
  • 6
  • 2
    Privileges on partitions are not supported. What you are trying to do is [row-level-security](https://stackoverflow.com/q/30701482), and the most common approach (if you need to do it in the database layer) would be to [use views](https://stackoverflow.com/q/15105052). – Solarflare Nov 16 '19 at 11:30
  • Use a VIEW if you have a new enough version to restrict a query to a specified partition. – Rick James Nov 18 '19 at 00:44

0 Answers0