1drop table if exists t1,t1aa,t2aa;
2set sql_mode="";
3DROP DATABASE IF EXISTS testdb;
4CREATE DATABASE testdb;
5CREATE TABLE testdb.t1 (
6c1 INT,
7c3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
8CREATE VIEW testdb.v1 AS
9SELECT * FROM testdb.t1;
10GRANT CREATE VIEW, SHOW VIEW ON testdb.v1 TO 'show_view_tbl'@'localhost';
11SHOW GRANTS FOR 'show_view_tbl'@'localhost';
12Grants for show_view_tbl@localhost
13GRANT USAGE ON *.* TO `show_view_tbl`@`localhost`
14GRANT CREATE VIEW, SHOW VIEW ON `testdb`.`v1` TO `show_view_tbl`@`localhost`
15
16GRANT SELECT(c1) on testdb.v1 to 'select_only_c1'@localhost;
17SHOW GRANTS FOR 'select_only_c1'@'localhost';
18Grants for select_only_c1@localhost
19GRANT USAGE ON *.* TO `select_only_c1`@`localhost`
20GRANT SELECT (c1) ON `testdb`.`v1` TO `select_only_c1`@`localhost`
21
22"after fix privs"
23SHOW GRANTS FOR 'show_view_tbl'@'localhost';
24Grants for show_view_tbl@localhost
25GRANT USAGE ON *.* TO `show_view_tbl`@`localhost`
26GRANT CREATE VIEW, SHOW VIEW ON `testdb`.`v1` TO `show_view_tbl`@`localhost`
27
28SHOW GRANTS FOR 'select_only_c1'@'localhost';
29Grants for select_only_c1@localhost
30GRANT USAGE ON *.* TO `select_only_c1`@`localhost`
31GRANT SELECT (c1) ON `testdb`.`v1` TO `select_only_c1`@`localhost`
32
33DROP USER 'show_view_tbl'@'localhost';
34DROP USER 'select_only_c1'@'localhost';
35DROP VIEW testdb.v1;
36DROP TABLE testdb.t1;
37DROP DATABASE testdb;
38