Re: Row-level security in MySQL?

[Bill Karwin]

The equivalent is to define a view restricted to a subset of rows, and let a user query the view, even though she does not have privilege to query the base table.

USE test;

CREATE TABLE `foo` (
  `i` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `foo` (i) VALUES (10), (20);

CREATE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `foo_v` 
  AS select `foo`.`i` AS `i` from `foo` where (`foo`.`i` < 15)';

GRANT ALL on test.foo_v TO 'bill'@'localhost' identified by 'password';

Then log in to the test database as 'bill', and notice that the view `foo_v` is visible, but the table `foo` is not. Querying `foo_v` shows only that which is shown by the view.