diff options
| -rw-r--r-- | mysql-test/r/innodb.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/insert_select-binlog.result | 10 | ||||
| -rw-r--r-- | mysql-test/r/mix_innodb_myisam_binlog.result | 20 | ||||
| -rw-r--r-- | mysql-test/r/ps_1general.result | 14 | ||||
| -rw-r--r-- | mysql-test/r/ps_grant.result | 18 | ||||
| -rw-r--r-- | mysql-test/r/view.result | 304 | ||||
| -rw-r--r-- | mysql-test/r/view_grant.result | 304 | ||||
| -rw-r--r-- | mysql-test/t/client_xml.test | 3 | ||||
| -rw-r--r-- | mysql-test/t/flush_read_lock_kill.test | 3 | ||||
| -rw-r--r-- | mysql-test/t/grant3.test | 3 | ||||
| -rw-r--r-- | mysql-test/t/information_schema.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/innodb.test | 8 | ||||
| -rw-r--r-- | mysql-test/t/multi_update.test | 3 | ||||
| -rw-r--r-- | mysql-test/t/mysqlshow.test | 3 | ||||
| -rw-r--r-- | mysql-test/t/ps_1general.test | 16 | ||||
| -rw-r--r-- | mysql-test/t/ps_grant.test | 13 | ||||
| -rw-r--r-- | mysql-test/t/sp-security.test | 3 | ||||
| -rw-r--r-- | mysql-test/t/user_limits.test | 3 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 401 | ||||
| -rw-r--r-- | mysql-test/t/view_grant.test | 401 |
20 files changed, 782 insertions, 754 deletions
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 16f639962cd..3f9962144ff 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1606,7 +1606,7 @@ t2 CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2; create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb; -ERROR HY000: Can't create table './test/t2.frm' (errno: 150) +ERROR HY000: Can't create table './test/t2' (errno: 150) create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb; show create table t2; Table Create Table @@ -2360,7 +2360,7 @@ length(v) 65530 drop table t1; create table t1 (v varchar(65530), key(v)); -ERROR HY000: Can't create table './test/t1.frm' (errno: 139) +ERROR HY000: Can't create table './test/t1' (errno: 139) create table t1 (v varchar(65536)); Warnings: Note 1246 Converting column 'v' from VARCHAR to TEXT diff --git a/mysql-test/r/insert_select-binlog.result b/mysql-test/r/insert_select-binlog.result index bca28059787..76f460b1de2 100644 --- a/mysql-test/r/insert_select-binlog.result +++ b/mysql-test/r/insert_select-binlog.result @@ -6,9 +6,9 @@ reset master; insert into t1 select * from t2; ERROR 23000: Duplicate entry '2' for key 1 show binlog events; -Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.000001 4 Start 1 4 Server ver: VERSION, Binlog ver: 3 -master-bin.000001 79 Query 1 79 use `test`; insert into t1 select * from t2 +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 98 Server ver: VERSION, Binlog ver: 4 +master-bin.000001 98 Query 1 192 use `test`; insert into t1 select * from t2 select * from t1; a 1 @@ -20,6 +20,6 @@ reset master; create table t2(unique(a)) select a from t1; ERROR 23000: Duplicate entry '1' for key 1 show binlog events; -Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.000001 4 Start 1 4 Server ver: VERSION, Binlog ver: 3 +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 98 Server ver: VERSION, Binlog ver: 4 drop table t1; diff --git a/mysql-test/r/mix_innodb_myisam_binlog.result b/mysql-test/r/mix_innodb_myisam_binlog.result index 7821c074202..e4528450b31 100644 --- a/mysql-test/r/mix_innodb_myisam_binlog.result +++ b/mysql-test/r/mix_innodb_myisam_binlog.result @@ -11,7 +11,7 @@ Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 98 Query 1 # use `test`; BEGIN master-bin.000001 166 Query 1 # use `test`; insert into t1 values(1) master-bin.000001 253 Query 1 # use `test`; insert into t2 select * from t1 -master-bin.000001 347 Xid 1 # COMMIT /* xid=7 */ +master-bin.000001 347 Xid 1 # COMMIT /* xid=8 */ delete from t1; delete from t2; reset master; @@ -47,7 +47,7 @@ master-bin.000001 253 Query 1 # use `test`; savepoint my_savepoint master-bin.000001 338 Query 1 # use `test`; insert into t1 values(4) master-bin.000001 425 Query 1 # use `test`; insert into t2 select * from t1 master-bin.000001 519 Query 1 # use `test`; rollback to savepoint my_savepoint -master-bin.000001 616 Xid 1 # COMMIT /* xid=24 */ +master-bin.000001 616 Xid 1 # COMMIT /* xid=25 */ delete from t1; delete from t2; reset master; @@ -74,7 +74,7 @@ master-bin.000001 338 Query 1 # use `test`; insert into t1 values(6) master-bin.000001 425 Query 1 # use `test`; insert into t2 select * from t1 master-bin.000001 519 Query 1 # use `test`; rollback to savepoint my_savepoint master-bin.000001 616 Query 1 # use `test`; insert into t1 values(7) -master-bin.000001 703 Xid 1 # COMMIT /* xid=36 */ +master-bin.000001 703 Xid 1 # COMMIT /* xid=37 */ delete from t1; delete from t2; reset master; @@ -101,7 +101,7 @@ insert into t2 select * from t1; show binlog events from 98; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 98 Query 1 # use `test`; insert into t1 values(9) -master-bin.000001 185 Xid 1 # COMMIT /* xid=59 */ +master-bin.000001 185 Xid 1 # COMMIT /* xid=60 */ master-bin.000001 212 Query 1 # use `test`; insert into t2 select * from t1 delete from t1; delete from t2; @@ -112,18 +112,18 @@ insert into t2 select * from t1; show binlog events from 98; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 98 Query 1 # use `test`; insert into t1 values(10) -master-bin.000001 186 Xid 1 # COMMIT /* xid=65 */ +master-bin.000001 186 Xid 1 # COMMIT /* xid=66 */ master-bin.000001 213 Query 1 # use `test`; insert into t2 select * from t1 insert into t1 values(11); commit; show binlog events from 98; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 98 Query 1 # use `test`; insert into t1 values(10) -master-bin.000001 186 Xid 1 # COMMIT /* xid=65 */ +master-bin.000001 186 Xid 1 # COMMIT /* xid=66 */ master-bin.000001 213 Query 1 # use `test`; insert into t2 select * from t1 master-bin.000001 307 Query 1 # use `test`; BEGIN master-bin.000001 375 Query 1 # use `test`; insert into t1 values(11) -master-bin.000001 463 Xid 1 # COMMIT /* xid=67 */ +master-bin.000001 463 Xid 1 # COMMIT /* xid=68 */ alter table t2 engine=INNODB; delete from t1; delete from t2; @@ -137,7 +137,7 @@ Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 98 Query 1 # use `test`; BEGIN master-bin.000001 166 Query 1 # use `test`; insert into t1 values(12) master-bin.000001 254 Query 1 # use `test`; insert into t2 select * from t1 -master-bin.000001 348 Xid 1 # COMMIT /* xid=77 */ +master-bin.000001 348 Xid 1 # COMMIT /* xid=78 */ delete from t1; delete from t2; reset master; @@ -161,7 +161,7 @@ show binlog events from 98; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 98 Query 1 # use `test`; BEGIN master-bin.000001 166 Query 1 # use `test`; insert into t1 values(14) -master-bin.000001 254 Xid 1 # COMMIT /* xid=93 */ +master-bin.000001 254 Xid 1 # COMMIT /* xid=94 */ delete from t1; delete from t2; reset master; @@ -182,7 +182,7 @@ Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 98 Query 1 # use `test`; BEGIN master-bin.000001 166 Query 1 # use `test`; insert into t1 values(16) master-bin.000001 254 Query 1 # use `test`; insert into t1 values(18) -master-bin.000001 342 Xid 1 # COMMIT /* xid=104 */ +master-bin.000001 342 Xid 1 # COMMIT /* xid=105 */ delete from t1; delete from t2; alter table t2 type=MyISAM; diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 9a9da7cae09..0fe907ac8c1 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -410,18 +410,6 @@ create database mysqltest ; prepare stmt3 from ' drop database mysqltest '; ERROR HY000: This command is not supported in the prepared statement protocol yet drop database mysqltest ; -prepare stmt3 from ' grant all on test.t1 to drop_user@localhost -identified by ''looser'' '; -ERROR HY000: This command is not supported in the prepared statement protocol yet -grant all on test.t1 to drop_user@localhost -identified by 'looser' ; -prepare stmt3 from ' revoke all privileges on test.t1 from -drop_user@localhost '; -ERROR HY000: This command is not supported in the prepared statement protocol yet -revoke all privileges on test.t1 from drop_user@localhost ; -prepare stmt3 from ' drop user drop_user@localhost '; -ERROR HY000: This command is not supported in the prepared statement protocol yet -drop user drop_user@localhost; prepare stmt3 from ' describe t2 '; execute stmt3; Field Type Null Key Default Extra @@ -572,7 +560,7 @@ drop table t2; prepare stmt1 from ' rename table t5 to t6, t7 to t8 ' ; create table t5 (a int) ; execute stmt1 ; -ERROR HY000: Can't find file: './test/t7.frm' (errno: 2) +ERROR HY000: Can't find file: './test/t7' (errno: 2) create table t7 (a int) ; execute stmt1 ; execute stmt1 ; diff --git a/mysql-test/r/ps_grant.result b/mysql-test/r/ps_grant.result index 4c60bb03165..3a302ba3ba8 100644 --- a/mysql-test/r/ps_grant.result +++ b/mysql-test/r/ps_grant.result @@ -36,19 +36,19 @@ identified by 'looser' ; show grants for second_user@localhost ; Grants for second_user@localhost GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' +GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' drop table mysqltest.t9 ; show grants for second_user@localhost ; Grants for second_user@localhost GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' +GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' show grants for second_user@localhost ; Grants for second_user@localhost GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' +GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' prepare s_t1 from 'select a as my_col from t1' ; execute s_t1 ; my_col @@ -79,3 +79,15 @@ commit ; show grants for second_user@localhost ; ERROR 42000: There is no such grant defined for user 'second_user' on host 'localhost' drop database mysqltest; +prepare stmt3 from ' grant all on test.t1 to drop_user@localhost +identified by ''looser'' '; +ERROR HY000: This command is not supported in the prepared statement protocol yet +grant all on test.t1 to drop_user@localhost +identified by 'looser' ; +prepare stmt3 from ' revoke all privileges on test.t1 from +drop_user@localhost '; +ERROR HY000: This command is not supported in the prepared statement protocol yet +revoke all privileges on test.t1 from drop_user@localhost ; +prepare stmt3 from ' drop user drop_user@localhost '; +ERROR HY000: This command is not supported in the prepared statement protocol yet +drop user drop_user@localhost; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 3162a3b5061..417617c0e3c 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -192,15 +192,6 @@ c d 1 3 2 5 3 10 -grant create view on test.* to test@localhost; -show grants for test@localhost; -Grants for test@localhost -GRANT USAGE ON *.* TO 'test'@'localhost' -GRANT CREATE VIEW ON `test`.* TO 'test'@'localhost' -revoke create view on test.* from test@localhost; -show grants for test@localhost; -Grants for test@localhost -GRANT USAGE ON *.* TO 'test'@'localhost' drop view v100; ERROR 42S02: Unknown table 'test.v100' drop view t1; @@ -230,143 +221,6 @@ a a 2 2 drop view v1, v2; drop table t1; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); -grant select on mysqltest.t1 to mysqltest_1@localhost; -grant create view,select on test.* to mysqltest_1@localhost; -create view v1 as select * from mysqltest.t1; -alter view v1 as select * from mysqltest.t1; -ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' -create or replace view v1 as select * from mysqltest.t1; -ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' -create view mysqltest.v2 as select * from mysqltest.t1; -ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' -create view v2 as select * from mysqltest.t2; -ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 't2' -grant create view,drop,select on test.* to mysqltest_1@localhost; -use test; -alter view v1 as select * from mysqltest.t1; -create or replace view v1 as select * from mysqltest.t1; -revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; -revoke all privileges on test.* from mysqltest_1@localhost; -drop database mysqltest; -drop view test.v1; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -grant select (c) on mysqltest.v1 to mysqltest_1@localhost; -select c from mysqltest.v1; -c -select d from mysqltest.v1; -ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' -revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -grant select (c) on mysqltest.v1 to mysqltest_1@localhost; -select c from mysqltest.v1; -c -select d from mysqltest.v1; -ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' -revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); -create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1; -create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; -create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; -grant select on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.v2 to mysqltest_1@localhost; -grant select on mysqltest.v3 to mysqltest_1@localhost; -grant select on mysqltest.v4 to mysqltest_1@localhost; -select c from mysqltest.v1; -c -select c from mysqltest.v2; -c -select c from mysqltest.v3; -c -select c from mysqltest.v4; -c -show columns from mysqltest.v1; -Field Type Null Key Default Extra -c bigint(20) YES NULL -d bigint(20) YES NULL -show columns from mysqltest.v2; -Field Type Null Key Default Extra -c bigint(20) YES NULL -d bigint(20) YES NULL -explain select c from mysqltest.v1; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v1; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' -explain select c from mysqltest.v2; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v2; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' -explain select c from mysqltest.v3; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v3; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' -explain select c from mysqltest.v4; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v4; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' -grant select on mysqltest.t1 to mysqltest_1@localhost; -explain select c from mysqltest.v1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found -show create view mysqltest.v1; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' -explain select c from mysqltest.v2; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found -2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -show create view mysqltest.v2; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' -explain select c from mysqltest.v3; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v3; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' -explain select c from mysqltest.v4; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v4; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' -grant show view on mysqltest.* to mysqltest_1@localhost; -explain select c from mysqltest.v1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found -show create view mysqltest.v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED VIEW `mysqltest`.`v1` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` -explain select c from mysqltest.v2; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found -2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -show create view mysqltest.v2; -View Create View -v2 CREATE ALGORITHM=TEMPTABLE VIEW `mysqltest`.`v2` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` -explain select c from mysqltest.v3; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found -show create view mysqltest.v3; -View Create View -v3 CREATE ALGORITHM=UNDEFINED VIEW `mysqltest`.`v3` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` -explain select c from mysqltest.v4; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found -2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -show create view mysqltest.v4; -View Create View -v4 CREATE ALGORITHM=TEMPTABLE VIEW `mysqltest`.`v4` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` -revoke all privileges on mysqltest.* from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; create table t1 (a int); insert into t1 values (1), (2), (3), (1), (2), (3); create view v1 as select distinct a from t1; @@ -513,61 +367,6 @@ a b 50 10 drop table t1,t2; drop view v1,v2; -create database mysqltest; -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10); -create table mysqltest.t2 (x int); -insert into mysqltest.t2 values (3), (4), (5), (6); -create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; -create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; -grant update (a) on mysqltest.v2 to mysqltest_1@localhost; -grant update on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; -use mysqltest; -update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c; -select * from t1; -a b -13 2 -24 3 -35 4 -46 5 -50 10 -update v1 set a=a+c; -select * from t1; -a b -16 2 -28 3 -40 4 -52 5 -61 10 -update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c; -select * from t1; -a b -16 2 -31 3 -44 4 -57 5 -61 10 -update v2 set a=a+c; -select * from t1; -a b -18 2 -34 3 -48 4 -62 5 -71 10 -update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; -ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' -update v2 set c=a+c; -ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' -update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; -ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' -update v3 set a=a+c; -ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; create table t1 (a int, b int, primary key(b)); insert into t1 values (1,20), (2,30), (3,40), (4,50), (5,100); create view v1 (c) as select b from t1 where a<3; @@ -637,34 +436,6 @@ a b 5 10 drop table t1,t2; drop view v1,v2; -create database mysqltest; -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10); -create table mysqltest.t2 (x int); -insert into mysqltest.t2 values (3), (4), (5), (6); -create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1; -grant delete on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; -use mysqltest; -delete from v1 where c < 4; -select * from t1; -a b -2 3 -3 4 -4 5 -5 10 -delete v1 from t2,v1 where t2.x=v1.c; -select * from t1; -a b -5 10 -delete v2 from t2,v2 where t2.x=v2.c; -ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' -delete from v2 where c < 4; -ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; create table t1 (a int, b int, c int, primary key(a,b)); insert into t1 values (10,2,-1), (20,3,-2), (30,4,-3), (40,5,-4), (50,10,-5); create view v1 (x,y) as select a, b from t1; @@ -745,36 +516,6 @@ a b c 40 5 NULL drop table t1, t2; drop view v1,v2,v3,v4,v5; -create database mysqltest; -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (1,2), (2,3); -create table mysqltest.t2 (x int, y int); -insert into mysqltest.t2 values (3,4); -create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; -grant insert on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; -use mysqltest; -insert into v1 values (5,6); -select * from t1; -a b -1 2 -2 3 -5 6 -insert into v1 select x,y from t2; -select * from t1; -a b -1 2 -2 3 -5 6 -3 4 -insert into v2 values (5,6); -ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' -insert into v2 select x,y from t2; -ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; create table t1 (a int, primary key(a)); insert into t1 values (1), (2), (3); create view v1 (x) as select a from t1 where a > 1; @@ -819,44 +560,6 @@ a b 2 2 drop view v1; drop table t1; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); -grant update on mysqltest.t1 to mysqltest_1@localhost; -grant update(b) on mysqltest.t2 to mysqltest_1@localhost; -grant create view,update on test.* to mysqltest_1@localhost; -create view v1 as select * from mysqltest.t1; -create view v2 as select b from mysqltest.t2; -create view mysqltest.v1 as select * from mysqltest.t1; -ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' -create view v3 as select a from mysqltest.t2; -ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for column 'a' in table 't2' -create table mysqltest.v3 (b int); -grant create view on mysqltest.v3 to mysqltest_1@localhost; -drop table mysqltest.v3; -create view mysqltest.v3 as select b from mysqltest.t2; -grant create view, update on mysqltest.v3 to mysqltest_1@localhost; -drop view mysqltest.v3; -create view mysqltest.v3 as select b from mysqltest.t2; -grant create view, update, insert on mysqltest.v3 to mysqltest_1@localhost; -drop view mysqltest.v3; -create view mysqltest.v3 as select b from mysqltest.t2; -ERROR 42000: create view command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 'v3' -create table mysqltest.v3 (b int); -grant select(b) on mysqltest.v3 to mysqltest_1@localhost; -drop table mysqltest.v3; -create view mysqltest.v3 as select b from mysqltest.t2; -ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' -create view v4 as select b+1 from mysqltest.t2; -ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't2' -grant create view,update,select on test.* to mysqltest_1@localhost; -create view v4 as select b+1 from mysqltest.t2; -ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't2' -grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost; -create view v4 as select b+1 from mysqltest.t2; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; -drop view v1,v2,v4; set sql_mode='ansi'; create table t1 ("a*b" int); create view v1 as select "a*b" from t1; @@ -1629,13 +1332,6 @@ Field 3,'Field 4| |Field 6| | 'Field 7'| drop view v1; drop table t1; -create database mysqltest; -create table mysqltest.t1 (a int); -grant all privileges on mysqltest.* to mysqltest_1@localhost; -use mysqltest; -create view v1 as select * from t1; -revoke all privileges on mysqltest.* from mysqltest_1@localhost; -drop database mysqltest; create table t1 (s1 smallint); create view v1 as select * from t1 where 20 < (select (s1) from t1); insert into v1 values (30); diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result new file mode 100644 index 00000000000..df8e2b04f47 --- /dev/null +++ b/mysql-test/r/view_grant.result @@ -0,0 +1,304 @@ +grant create view on test.* to test@localhost; +show grants for test@localhost; +Grants for test@localhost +GRANT USAGE ON *.* TO 'test'@'localhost' +GRANT CREATE VIEW ON `test`.* TO 'test'@'localhost' +revoke create view on test.* from test@localhost; +show grants for test@localhost; +Grants for test@localhost +GRANT USAGE ON *.* TO 'test'@'localhost' +create database mysqltest; +create table mysqltest.t1 (a int, b int); +create table mysqltest.t2 (a int, b int); +grant select on mysqltest.t1 to mysqltest_1@localhost; +grant create view,select on test.* to mysqltest_1@localhost; +create view v1 as select * from mysqltest.t1; +alter view v1 as select * from mysqltest.t1; +ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' +create or replace view v1 as select * from mysqltest.t1; +ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' +create view mysqltest.v2 as select * from mysqltest.t1; +ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' +create view v2 as select * from mysqltest.t2; +ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 't2' +grant create view,drop,select on test.* to mysqltest_1@localhost; +use test; +alter view v1 as select * from mysqltest.t1; +create or replace view v1 as select * from mysqltest.t1; +revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; +revoke all privileges on test.* from mysqltest_1@localhost; +drop database mysqltest; +drop view test.v1; +create database mysqltest; +create table mysqltest.t1 (a int, b int); +create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; +grant select (c) on mysqltest.v1 to mysqltest_1@localhost; +select c from mysqltest.v1; +c +select d from mysqltest.v1; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' +revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +drop database mysqltest; +create database mysqltest; +create table mysqltest.t1 (a int, b int); +create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; +grant select (c) on mysqltest.v1 to mysqltest_1@localhost; +select c from mysqltest.v1; +c +select d from mysqltest.v1; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' +revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +drop database mysqltest; +create database mysqltest; +create table mysqltest.t1 (a int, b int); +create table mysqltest.t2 (a int, b int); +create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; +create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1; +create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; +create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; +grant select on mysqltest.v1 to mysqltest_1@localhost; +grant select on mysqltest.v2 to mysqltest_1@localhost; +grant select on mysqltest.v3 to mysqltest_1@localhost; +grant select on mysqltest.v4 to mysqltest_1@localhost; +select c from mysqltest.v1; +c +select c from mysqltest.v2; +c +select c from mysqltest.v3; +c +select c from mysqltest.v4; +c +show columns from mysqltest.v1; +Field Type Null Key Default Extra +c bigint(20) YES NULL +d bigint(20) YES NULL +show columns from mysqltest.v2; +Field Type Null Key Default Extra +c bigint(20) YES NULL +d bigint(20) YES NULL +explain select c from mysqltest.v1; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v1; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' +explain select c from mysqltest.v2; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v2; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' +explain select c from mysqltest.v3; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v3; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' +explain select c from mysqltest.v4; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v4; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' +grant select on mysqltest.t1 to mysqltest_1@localhost; +explain select c from mysqltest.v1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +show create view mysqltest.v1; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' +explain select c from mysqltest.v2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found +2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +show create view mysqltest.v2; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' +explain select c from mysqltest.v3; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v3; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' +explain select c from mysqltest.v4; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +show create view mysqltest.v4; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' +grant show view on mysqltest.* to mysqltest_1@localhost; +explain select c from mysqltest.v1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +show create view mysqltest.v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED VIEW `mysqltest`.`v1` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` +explain select c from mysqltest.v2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found +2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +show create view mysqltest.v2; +View Create View +v2 CREATE ALGORITHM=TEMPTABLE VIEW `mysqltest`.`v2` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` +explain select c from mysqltest.v3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +show create view mysqltest.v3; +View Create View +v3 CREATE ALGORITHM=UNDEFINED VIEW `mysqltest`.`v3` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` +explain select c from mysqltest.v4; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found +2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +show create view mysqltest.v4; +View Create View +v4 CREATE ALGORITHM=TEMPTABLE VIEW `mysqltest`.`v4` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +drop database mysqltest; +create database mysqltest; +create table mysqltest.t1 (a int, b int, primary key(a)); +insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10); +create table mysqltest.t2 (x int); +insert into mysqltest.t2 values (3), (4), (5), (6); +create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; +create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; +create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; +grant update (a) on mysqltest.v2 to mysqltest_1@localhost; +grant update on mysqltest.v1 to mysqltest_1@localhost; +grant select on mysqltest.* to mysqltest_1@localhost; +use mysqltest; +update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c; +select * from t1; +a b +13 2 +24 3 +35 4 +46 5 +50 10 +update v1 set a=a+c; +select * from t1; +a b +16 2 +28 3 +40 4 +52 5 +61 10 +update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c; +select * from t1; +a b +16 2 +31 3 +44 4 +57 5 +61 10 +update v2 set a=a+c; +select * from t1; +a b +18 2 +34 3 +48 4 +62 5 +71 10 +update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' +update v2 set c=a+c; +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' +update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' +update v3 set a=a+c; +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; +create database mysqltest; +create table mysqltest.t1 (a int, b int, primary key(a)); +insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10); +create table mysqltest.t2 (x int); +insert into mysqltest.t2 values (3), (4), (5), (6); +create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; +create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1; +grant delete on mysqltest.v1 to mysqltest_1@localhost; +grant select on mysqltest.* to mysqltest_1@localhost; +use mysqltest; +delete from v1 where c < 4; +select * from t1; +a b +2 3 +3 4 +4 5 +5 10 +delete v1 from t2,v1 where t2.x=v1.c; +select * from t1; +a b +5 10 +delete v2 from t2,v2 where t2.x=v2.c; +ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' +delete from v2 where c < 4; +ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; +create database mysqltest; +create table mysqltest.t1 (a int, b int, primary key(a)); +insert into mysqltest.t1 values (1,2), (2,3); +create table mysqltest.t2 (x int, y int); +insert into mysqltest.t2 values (3,4); +create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1; +create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; +grant insert on mysqltest.v1 to mysqltest_1@localhost; +grant select on mysqltest.* to mysqltest_1@localhost; +use mysqltest; +insert into v1 values (5,6); +select * from t1; +a b +1 2 +2 3 +5 6 +insert into v1 select x,y from t2; +select * from t1; +a b +1 2 +2 3 +5 6 +3 4 +insert into v2 values (5,6); +ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' +insert into v2 select x,y from t2; +ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; +create database mysqltest; +create table mysqltest.t1 (a int, b int); +create table mysqltest.t2 (a int, b int); +grant update on mysqltest.t1 to mysqltest_1@localhost; +grant update(b) on mysqltest.t2 to mysqltest_1@localhost; +grant create view,update on test.* to mysqltest_1@localhost; +create view v1 as select * from mysqltest.t1; +create view v2 as select b from mysqltest.t2; +create view mysqltest.v1 as select * from mysqltest.t1; +ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' +create view v3 as select a from mysqltest.t2; +ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for column 'a' in table 't2' +create table mysqltest.v3 (b int); +grant create view on mysqltest.v3 to mysqltest_1@localhost; +drop table mysqltest.v3; +create view mysqltest.v3 as select b from mysqltest.t2; +grant create view, update on mysqltest.v3 to mysqltest_1@localhost; +drop view mysqltest.v3; +create view mysqltest.v3 as select b from mysqltest.t2; +grant create view, update, insert on mysqltest.v3 to mysqltest_1@localhost; +drop view mysqltest.v3; +create view mysqltest.v3 as select b from mysqltest.t2; +ERROR 42000: create view command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 'v3' +create table mysqltest.v3 (b int); +grant select(b) on mysqltest.v3 to mysqltest_1@localhost; +drop table mysqltest.v3; +create view mysqltest.v3 as select b from mysqltest.t2; +ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' +create view v4 as select b+1 from mysqltest.t2; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't2' +grant create view,update,select on test.* to mysqltest_1@localhost; +create view v4 as select b+1 from mysqltest.t2; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't2' +grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost; +create view v4 as select b+1 from mysqltest.t2; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; +drop view v1,v2,v4; +create database mysqltest; +create table mysqltest.t1 (a int); +grant all privileges on mysqltest.* to mysqltest_1@localhost; +use mysqltest; +create view v1 as select * from t1; +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +drop database mysqltest; diff --git a/mysql-test/t/client_xml.test b/mysql-test/t/client_xml.test index dd7a812b54f..58e9178ef3a 100644 --- a/mysql-test/t/client_xml.test +++ b/mysql-test/t/client_xml.test @@ -1,3 +1,6 @@ +# Can't run with embedded server +-- source include/not_embedded.inc + # Test of the xml output of the 'mysql' and 'mysqldump' clients -- makes # sure that basic encoding issues are handled properly create table t1 ( diff --git a/mysql-test/t/flush_read_lock_kill.test b/mysql-test/t/flush_read_lock_kill.test index b711bc63e0e..02384357711 100644 --- a/mysql-test/t/flush_read_lock_kill.test +++ b/mysql-test/t/flush_read_lock_kill.test @@ -7,6 +7,9 @@ # -master.opt. But this test is designed to still pass then (though it # won't test anything interesting). +# This also won't work with the embedded server test +-- source include/not_embedded.inc + -- source include/have_debug.inc connect (con1,localhost,root,,); diff --git a/mysql-test/t/grant3.test b/mysql-test/t/grant3.test index 1488f910bc8..115586e807d 100644 --- a/mysql-test/t/grant3.test +++ b/mysql-test/t/grant3.test @@ -1,3 +1,6 @@ +# Can't run with embedded server +-- source include/not_embedded.inc + # Test of GRANT commands SET NAMES binary; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 1a9849869d9..e515cd8c77a 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1,3 +1,5 @@ +# This test uses grants, which can't get tested for embedded server +-- source include/not_embedded.inc # Test for information_schema.schemata & # show databases diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index c3dc251ce3a..cca3e7ed3ad 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -1125,7 +1125,10 @@ show create table t2; drop table t2; # Test error handling ---replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / + +# Clean up filename -- embedded server reports whole path without .frm, +# regular server reports relative path with .frm (argh!) +--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t2.frm t2 --error 1005 create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb; @@ -1280,6 +1283,9 @@ source include/varchar.inc; # Some errors/warnings on create # +# Clean up filename -- embedded server reports whole path without .frm, +# regular server reports relative path with .frm (argh!) +--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t1.frm t1 --error 1005 create table t1 (v varchar(65530), key(v)); create table t1 (v varchar(65536)); diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index f3b6216e3cf..0ca42e86204 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -2,6 +2,9 @@ # Test of update statement that uses many tables. # +# Requires grants, so won't work with embedded server test +-- source include/not_embedded.inc + --disable_warnings drop table if exists t1,t2,t3; drop database if exists mysqltest; diff --git a/mysql-test/t/mysqlshow.test b/mysql-test/t/mysqlshow.test index 8da29b91819..33ae8aef9a0 100644 --- a/mysql-test/t/mysqlshow.test +++ b/mysql-test/t/mysqlshow.test @@ -1,3 +1,6 @@ +# Can't run test of external client with embedded server +-- source include/not_embedded.inc + # ## Bug #5036 mysqlshow is missing a column # diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test index 38f03fe4cc2..1fa9d30eaba 100644 --- a/mysql-test/t/ps_1general.test +++ b/mysql-test/t/ps_1general.test @@ -436,20 +436,6 @@ create database mysqltest ; prepare stmt3 from ' drop database mysqltest '; drop database mysqltest ; -## grant/revoke + drop user ---error 1295 -prepare stmt3 from ' grant all on test.t1 to drop_user@localhost -identified by ''looser'' '; -grant all on test.t1 to drop_user@localhost -identified by 'looser' ; ---error 1295 -prepare stmt3 from ' revoke all privileges on test.t1 from -drop_user@localhost '; -revoke all privileges on test.t1 from drop_user@localhost ; ---error 1295 -prepare stmt3 from ' drop user drop_user@localhost '; -drop user drop_user@localhost; - #### table related commands ## describe prepare stmt3 from ' describe t2 '; @@ -606,7 +592,7 @@ prepare stmt1 from ' rename table t5 to t6, t7 to t8 ' ; create table t5 (a int) ; # rename must fail, t7 does not exist # Clean up the filename here because embedded server reports whole path ---replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / +--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t7.frm t7 --error 1017 execute stmt1 ; create table t7 (a int) ; diff --git a/mysql-test/t/ps_grant.test b/mysql-test/t/ps_grant.test index 06613072824..07bd70f6cff 100644 --- a/mysql-test/t/ps_grant.test +++ b/mysql-test/t/ps_grant.test @@ -116,4 +116,17 @@ show grants for second_user@localhost ; drop database mysqltest; +## grant/revoke + drop user +--error 1295 +prepare stmt3 from ' grant all on test.t1 to drop_user@localhost +identified by ''looser'' '; +grant all on test.t1 to drop_user@localhost +identified by 'looser' ; +--error 1295 +prepare stmt3 from ' revoke all privileges on test.t1 from +drop_user@localhost '; +revoke all privileges on test.t1 from drop_user@localhost ; +--error 1295 +prepare stmt3 from ' drop user drop_user@localhost '; +drop user drop_user@localhost; diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index aad5f4eaf9e..5a8dfc54920 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -2,6 +2,9 @@ # Testing SQL SECURITY of stored procedures # +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc + connect (con1root,localhost,root,,); connection con1root; diff --git a/mysql-test/t/user_limits.test b/mysql-test/t/user_limits.test index 729894a588a..af0f6545ac4 100644 --- a/mysql-test/t/user_limits.test +++ b/mysql-test/t/user_limits.test @@ -2,6 +2,9 @@ # Test behavior of various per-account limits (aka quotas) # +# Requires privileges to be enabled +-- source include/not_embedded.inc + # Prepare play-ground --disable_warnings drop table if exists t1; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 527dcda0f1b..8205680627d 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -122,12 +122,6 @@ alter view v1 (c,d) as select a,max(b) from t1 group by a; select * from v1; select * from v2; -# simple test of grants -grant create view on test.* to test@localhost; -show grants for test@localhost; -revoke create view on test.* from test@localhost; -show grants for test@localhost; - # try to drop nonexistent VIEW -- error 1051 drop view v100; @@ -163,182 +157,6 @@ drop table t1; # -# grant create view test -# -connect (root,localhost,root,,test); -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); - -grant select on mysqltest.t1 to mysqltest_1@localhost; -grant create view,select on test.* to mysqltest_1@localhost; - -connect (user1,localhost,mysqltest_1,,test); -connection user1; - -create view v1 as select * from mysqltest.t1; -# try to modify view without DROP privilege on it --- error 1142 -alter view v1 as select * from mysqltest.t1; --- error 1142 -create or replace view v1 as select * from mysqltest.t1; -# no CRETE VIEW privilege --- error 1142 -create view mysqltest.v2 as select * from mysqltest.t1; -# no SELECT privilege --- error 1142 -create view v2 as select * from mysqltest.t2; - -connection root; -grant create view,drop,select on test.* to mysqltest_1@localhost; - -connection user1; -# following 'use' command is workaround of bug #9582 and should be removed -# when that bug will be fixed -use test; -alter view v1 as select * from mysqltest.t1; -create or replace view v1 as select * from mysqltest.t1; - -connection root; -revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; -revoke all privileges on test.* from mysqltest_1@localhost; - -drop database mysqltest; -drop view test.v1; - -# -# grants per columns -# -# MERGE algorithm ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int); -create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -grant select (c) on mysqltest.v1 to mysqltest_1@localhost; - -connection user1; -select c from mysqltest.v1; -# there are no privileges on column 'd' --- error 1143 -select d from mysqltest.v1; - -connection root; -revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; - -# TEMPORARY TABLE algorithm ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int); -create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -grant select (c) on mysqltest.v1 to mysqltest_1@localhost; - -connection user1; -select c from mysqltest.v1; -# there are no privileges on column 'd' --- error 1143 -select d from mysqltest.v1; - -connection root; -revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; - -# -# EXPLAIN rights -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings -#prepare views and tables -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); -create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1; -create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; -create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; -grant select on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.v2 to mysqltest_1@localhost; -grant select on mysqltest.v3 to mysqltest_1@localhost; -grant select on mysqltest.v4 to mysqltest_1@localhost; - -connection user1; -# all selects works -select c from mysqltest.v1; -select c from mysqltest.v2; -select c from mysqltest.v3; -select c from mysqltest.v4; -# test of show coluns -show columns from mysqltest.v1; -show columns from mysqltest.v2; -# but explain/show do not --- error 1345 -explain select c from mysqltest.v1; --- error 1142 -show create view mysqltest.v1; --- error 1345 -explain select c from mysqltest.v2; --- error 1142 -show create view mysqltest.v2; --- error 1345 -explain select c from mysqltest.v3; --- error 1142 -show create view mysqltest.v3; --- error 1345 -explain select c from mysqltest.v4; --- error 1142 -show create view mysqltest.v4; - -# allow to see one of underlying table -connection root; -grant select on mysqltest.t1 to mysqltest_1@localhost; -connection user1; -# EXPLAIN of view on above table works -explain select c from mysqltest.v1; --- error 1142 -show create view mysqltest.v1; -explain select c from mysqltest.v2; --- error 1142 -show create view mysqltest.v2; -# but other EXPLAINs do not --- error 1345 -explain select c from mysqltest.v3; --- error 1142 -show create view mysqltest.v3; --- error 1345 -explain select c from mysqltest.v4; --- error 1142 -show create view mysqltest.v4; - -# allow to see any view in mysqltest database -connection root; -grant show view on mysqltest.* to mysqltest_1@localhost; -connection user1; -explain select c from mysqltest.v1; -show create view mysqltest.v1; -explain select c from mysqltest.v2; -show create view mysqltest.v2; -explain select c from mysqltest.v3; -show create view mysqltest.v3; -explain select c from mysqltest.v4; -show create view mysqltest.v4; - -connection root; -revoke all privileges on mysqltest.* from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; - -# # DISTINCT option for VIEW # create table t1 (a int); @@ -444,54 +262,6 @@ drop table t1,t2; drop view v1,v2; # -# UPDATE privileges on VIEW columns and whole VIEW -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10); -create table mysqltest.t2 (x int); -insert into mysqltest.t2 values (3), (4), (5), (6); -create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; -create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; - -grant update (a) on mysqltest.v2 to mysqltest_1@localhost; -grant update on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; - -connection user1; -use mysqltest; -# update with rights on VIEW column -update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c; -select * from t1; -update v1 set a=a+c; -select * from t1; -# update with rights on whole VIEW -update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c; -select * from t1; -update v2 set a=a+c; -select * from t1; -# no rights on column --- error 1143 -update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; --- error 1143 -update v2 set c=a+c; -# no rights for view --- error 1142 -update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; --- error 1142 -update v3 set a=a+c; - -use test; -connection root; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; - -# # MERGE VIEW with WHERE clause # create table t1 (a int, b int, primary key(b)); @@ -546,42 +316,6 @@ drop table t1,t2; drop view v1,v2; # -# DELETE privileges on VIEW -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10); -create table mysqltest.t2 (x int); -insert into mysqltest.t2 values (3), (4), (5), (6); -create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1; - -grant delete on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; - -connection user1; -use mysqltest; -# update with rights on VIEW column -delete from v1 where c < 4; -select * from t1; -delete v1 from t2,v1 where t2.x=v1.c; -select * from t1; -# no rights for view --- error 1142 -delete v2 from t2,v2 where t2.x=v2.c; --- error 1142 -delete from v2 where c < 4; - -use test; -connection root; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; - -# # key presence check # create table t1 (a int, b int, c int, primary key(a,b)); @@ -658,42 +392,6 @@ drop table t1, t2; drop view v1,v2,v3,v4,v5; # -# insert privileges on VIEW -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (1,2), (2,3); -create table mysqltest.t2 (x int, y int); -insert into mysqltest.t2 values (3,4); -create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; - -grant insert on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; - -connection user1; -use mysqltest; -# update with rights on VIEW column -insert into v1 values (5,6); -select * from t1; -insert into v1 select x,y from t2; -select * from t1; -# no rights for view --- error 1142 -insert into v2 values (5,6); --- error 1142 -insert into v2 select x,y from t2; - -use test; -connection root; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; - -# # outer join based on VIEW with WHERE clause # create table t1 (a int, primary key(a)); @@ -740,87 +438,6 @@ drop view v1; drop table t1; # -# test of CREATE VIEW privileges if we have limited privileges -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); - -grant update on mysqltest.t1 to mysqltest_1@localhost; -grant update(b) on mysqltest.t2 to mysqltest_1@localhost; -grant create view,update on test.* to mysqltest_1@localhost; - -connection user1; - -create view v1 as select * from mysqltest.t1; -create view v2 as select b from mysqltest.t2; -# There are not rights on mysqltest.v1 --- error 1142 -create view mysqltest.v1 as select * from mysqltest.t1; -# There are not any rights on mysqltest.t2.a --- error 1143 -create view v3 as select a from mysqltest.t2; - -# give CREATE VIEW privileges (without any privileges for result column) -connection root; -create table mysqltest.v3 (b int); -grant create view on mysqltest.v3 to mysqltest_1@localhost; -drop table mysqltest.v3; -connection user1; -create view mysqltest.v3 as select b from mysqltest.t2; - -# give UPDATE privileges -connection root; -grant create view, update on mysqltest.v3 to mysqltest_1@localhost; -drop view mysqltest.v3; -connection user1; -create view mysqltest.v3 as select b from mysqltest.t2; - -# give UPDATE and INSERT privilege (to get more privileges then underlying -# table) -connection root; -grant create view, update, insert on mysqltest.v3 to mysqltest_1@localhost; -drop view mysqltest.v3; -connection user1; --- error 1143 -create view mysqltest.v3 as select b from mysqltest.t2; - - -# If we would get more privileges on VIEW then we have on -# underlying tables => creation prohibited -connection root; -create table mysqltest.v3 (b int); -grant select(b) on mysqltest.v3 to mysqltest_1@localhost; -drop table mysqltest.v3; -connection user1; --- error 1142 -create view mysqltest.v3 as select b from mysqltest.t2; - -# Expression need select privileges --- error 1143 -create view v4 as select b+1 from mysqltest.t2; - -connection root; -grant create view,update,select on test.* to mysqltest_1@localhost; -connection user1; --- error 1143 -create view v4 as select b+1 from mysqltest.t2; - -connection root; -grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost; -connection user1; -create view v4 as select b+1 from mysqltest.t2; - -connection root; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; -drop view v1,v2,v4; - -# # VIEW fields quoting # set sql_mode='ansi'; @@ -1613,24 +1230,6 @@ drop view v1; drop table t1; # -# user with global DB privileges -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings -create table mysqltest.t1 (a int); -grant all privileges on mysqltest.* to mysqltest_1@localhost; - -connection user1; -use mysqltest; -create view v1 as select * from t1; - -connection root; -revoke all privileges on mysqltest.* from mysqltest_1@localhost; -drop database mysqltest; - -# # Trys update table from which we select using views and subqueries # create table t1 (s1 smallint); diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test new file mode 100644 index 00000000000..2fe7a420e66 --- /dev/null +++ b/mysql-test/t/view_grant.test @@ -0,0 +1,401 @@ + +# simple test of grants +grant create view on test.* to test@localhost; +show grants for test@localhost; +revoke create view on test.* from test@localhost; +show grants for test@localhost; + +# grant create view test +# +connect (root,localhost,root,,test); +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +create table mysqltest.t1 (a int, b int); +create table mysqltest.t2 (a int, b int); + +grant select on mysqltest.t1 to mysqltest_1@localhost; +grant create view,select on test.* to mysqltest_1@localhost; + +connect (user1,localhost,mysqltest_1,,test); +connection user1; + +create view v1 as select * from mysqltest.t1; +# try to modify view without DROP privilege on it +-- error 1142 +alter view v1 as select * from mysqltest.t1; +-- error 1142 +create or replace view v1 as select * from mysqltest.t1; +# no CRETE VIEW privilege +-- error 1142 +create view mysqltest.v2 as select * from mysqltest.t1; +# no SELECT privilege +-- error 1142 +create view v2 as select * from mysqltest.t2; + +connection root; +grant create view,drop,select on test.* to mysqltest_1@localhost; + +connection user1; +# following 'use' command is workaround of bug #9582 and should be removed +# when that bug will be fixed +use test; +alter view v1 as select * from mysqltest.t1; +create or replace view v1 as select * from mysqltest.t1; + +connection root; +revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; +revoke all privileges on test.* from mysqltest_1@localhost; + +drop database mysqltest; +drop view test.v1; + +# +# grants per columns +# +# MERGE algorithm +--disable_warnings +create database mysqltest; +--enable_warnings + +create table mysqltest.t1 (a int, b int); +create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; +grant select (c) on mysqltest.v1 to mysqltest_1@localhost; + +connection user1; +select c from mysqltest.v1; +# there are no privileges on column 'd' +-- error 1143 +select d from mysqltest.v1; + +connection root; +revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +drop database mysqltest; + +# TEMPORARY TABLE algorithm +--disable_warnings +create database mysqltest; +--enable_warnings + +create table mysqltest.t1 (a int, b int); +create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; +grant select (c) on mysqltest.v1 to mysqltest_1@localhost; + +connection user1; +select c from mysqltest.v1; +# there are no privileges on column 'd' +-- error 1143 +select d from mysqltest.v1; + +connection root; +revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +drop database mysqltest; + +# +# EXPLAIN rights +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings +#prepare views and tables +create table mysqltest.t1 (a int, b int); +create table mysqltest.t2 (a int, b int); +create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; +create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1; +create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; +create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; +grant select on mysqltest.v1 to mysqltest_1@localhost; +grant select on mysqltest.v2 to mysqltest_1@localhost; +grant select on mysqltest.v3 to mysqltest_1@localhost; +grant select on mysqltest.v4 to mysqltest_1@localhost; + +connection user1; +# all selects works +select c from mysqltest.v1; +select c from mysqltest.v2; +select c from mysqltest.v3; +select c from mysqltest.v4; +# test of show coluns +show columns from mysqltest.v1; +show columns from mysqltest.v2; +# but explain/show do not +-- error 1345 +explain select c from mysqltest.v1; +-- error 1142 +show create view mysqltest.v1; +-- error 1345 +explain select c from mysqltest.v2; +-- error 1142 +show create view mysqltest.v2; +-- error 1345 +explain select c from mysqltest.v3; +-- error 1142 +show create view mysqltest.v3; +-- error 1345 +explain select c from mysqltest.v4; +-- error 1142 +show create view mysqltest.v4; + +# allow to see one of underlying table +connection root; +grant select on mysqltest.t1 to mysqltest_1@localhost; +connection user1; +# EXPLAIN of view on above table works +explain select c from mysqltest.v1; +-- error 1142 +show create view mysqltest.v1; +explain select c from mysqltest.v2; +-- error 1142 +show create view mysqltest.v2; +# but other EXPLAINs do not +-- error 1345 +explain select c from mysqltest.v3; +-- error 1142 +show create view mysqltest.v3; +-- error 1345 +explain select c from mysqltest.v4; +-- error 1142 +show create view mysqltest.v4; + +# allow to see any view in mysqltest database +connection root; +grant show view on mysqltest.* to mysqltest_1@localhost; +connection user1; +explain select c from mysqltest.v1; +show create view mysqltest.v1; +explain select c from mysqltest.v2; +show create view mysqltest.v2; +explain select c from mysqltest.v3; +show create view mysqltest.v3; +explain select c from mysqltest.v4; +show create view mysqltest.v4; + +connection root; +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +drop database mysqltest; + +# +# UPDATE privileges on VIEW columns and whole VIEW +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +create table mysqltest.t1 (a int, b int, primary key(a)); +insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10); +create table mysqltest.t2 (x int); +insert into mysqltest.t2 values (3), (4), (5), (6); +create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; +create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; +create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; + +grant update (a) on mysqltest.v2 to mysqltest_1@localhost; +grant update on mysqltest.v1 to mysqltest_1@localhost; +grant select on mysqltest.* to mysqltest_1@localhost; + +connection user1; +use mysqltest; +# update with rights on VIEW column +update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c; +select * from t1; +update v1 set a=a+c; +select * from t1; +# update with rights on whole VIEW +update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c; +select * from t1; +update v2 set a=a+c; +select * from t1; +# no rights on column +-- error 1143 +update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; +-- error 1143 +update v2 set c=a+c; +# no rights for view +-- error 1142 +update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; +-- error 1142 +update v3 set a=a+c; + +use test; +connection root; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; + +# +# DELETE privileges on VIEW +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +create table mysqltest.t1 (a int, b int, primary key(a)); +insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10); +create table mysqltest.t2 (x int); +insert into mysqltest.t2 values (3), (4), (5), (6); +create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; +create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1; + +grant delete on mysqltest.v1 to mysqltest_1@localhost; +grant select on mysqltest.* to mysqltest_1@localhost; + +connection user1; +use mysqltest; +# update with rights on VIEW column +delete from v1 where c < 4; +select * from t1; +delete v1 from t2,v1 where t2.x=v1.c; +select * from t1; +# no rights for view +-- error 1142 +delete v2 from t2,v2 where t2.x=v2.c; +-- error 1142 +delete from v2 where c < 4; + +use test; +connection root; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; + +# +# insert privileges on VIEW +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +create table mysqltest.t1 (a int, b int, primary key(a)); +insert into mysqltest.t1 values (1,2), (2,3); +create table mysqltest.t2 (x int, y int); +insert into mysqltest.t2 values (3,4); +create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1; +create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; + +grant insert on mysqltest.v1 to mysqltest_1@localhost; +grant select on mysqltest.* to mysqltest_1@localhost; + +connection user1; +use mysqltest; +# update with rights on VIEW column +insert into v1 values (5,6); +select * from t1; +insert into v1 select x,y from t2; +select * from t1; +# no rights for view +-- error 1142 +insert into v2 values (5,6); +-- error 1142 +insert into v2 select x,y from t2; + +use test; +connection root; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; + +# +# test of CREATE VIEW privileges if we have limited privileges +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +create table mysqltest.t1 (a int, b int); +create table mysqltest.t2 (a int, b int); + +grant update on mysqltest.t1 to mysqltest_1@localhost; +grant update(b) on mysqltest.t2 to mysqltest_1@localhost; +grant create view,update on test.* to mysqltest_1@localhost; + +connection user1; + +create view v1 as select * from mysqltest.t1; +create view v2 as select b from mysqltest.t2; +# There are not rights on mysqltest.v1 +-- error 1142 +create view mysqltest.v1 as select * from mysqltest.t1; +# There are not any rights on mysqltest.t2.a +-- error 1143 +create view v3 as select a from mysqltest.t2; + +# give CREATE VIEW privileges (without any privileges for result column) +connection root; +create table mysqltest.v3 (b int); +grant create view on mysqltest.v3 to mysqltest_1@localhost; +drop table mysqltest.v3; +connection user1; +create view mysqltest.v3 as select b from mysqltest.t2; + +# give UPDATE privileges +connection root; +grant create view, update on mysqltest.v3 to mysqltest_1@localhost; +drop view mysqltest.v3; +connection user1; +create view mysqltest.v3 as select b from mysqltest.t2; + +# give UPDATE and INSERT privilege (to get more privileges then underlying +# table) +connection root; +grant create view, update, insert on mysqltest.v3 to mysqltest_1@localhost; +drop view mysqltest.v3; +connection user1; +-- error 1143 +create view mysqltest.v3 as select b from mysqltest.t2; + + +# If we would get more privileges on VIEW then we have on +# underlying tables => creation prohibited +connection root; +create table mysqltest.v3 (b int); +grant select(b) on mysqltest.v3 to mysqltest_1@localhost; +drop table mysqltest.v3; +connection user1; +-- error 1142 +create view mysqltest.v3 as select b from mysqltest.t2; + +# Expression need select privileges +-- error 1143 +create view v4 as select b+1 from mysqltest.t2; + +connection root; +grant create view,update,select on test.* to mysqltest_1@localhost; +connection user1; +-- error 1143 +create view v4 as select b+1 from mysqltest.t2; + +connection root; +grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost; +connection user1; +create view v4 as select b+1 from mysqltest.t2; + +connection root; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; +drop view v1,v2,v4; + +# +# user with global DB privileges +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings +create table mysqltest.t1 (a int); +grant all privileges on mysqltest.* to mysqltest_1@localhost; + +connection user1; +use mysqltest; +create view v1 as select * from t1; + +connection root; +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +drop database mysqltest; + |