diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/view_grant.result | 97 | ||||
| -rw-r--r-- | mysql-test/t/view_grant.test | 111 |
2 files changed, 180 insertions, 28 deletions
diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 45cf5076fe1..3ddf4ca979c 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -282,15 +282,6 @@ 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 column 'b' in 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; @@ -773,4 +764,92 @@ DROP DATABASE mysqltest_db1; DROP DATABASE mysqltest_db2; DROP USER mysqltest_u1@localhost; DROP USER mysqltest_u2@localhost; +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +CREATE TABLE mysqltest1.t1(c1 INT); +CREATE TABLE mysqltest1.t2(c2 INT); +CREATE TABLE mysqltest1.t3(c3 INT); +CREATE TABLE mysqltest1.t4(c4 INT); +INSERT INTO mysqltest1.t1 VALUES (11), (12), (13), (14); +INSERT INTO mysqltest1.t2 VALUES (21), (22), (23), (24); +INSERT INTO mysqltest1.t3 VALUES (31), (32), (33), (34); +INSERT INTO mysqltest1.t4 VALUES (41), (42), (43), (44); +GRANT SELECT ON mysqltest1.t1 TO mysqltest_u1@localhost; +GRANT INSERT ON mysqltest1.t2 TO mysqltest_u1@localhost; +GRANT SELECT, UPDATE ON mysqltest1.t3 TO mysqltest_u1@localhost; +GRANT SELECT, DELETE ON mysqltest1.t4 TO mysqltest_u1@localhost; +GRANT ALL PRIVILEGES ON mysqltest2.* TO mysqltest_u1@localhost; + +---> connection: bug24040_con +SELECT * FROM mysqltest1.t1; +c1 +11 +12 +13 +14 +INSERT INTO mysqltest1.t2 VALUES(25); +UPDATE mysqltest1.t3 SET c3 = 331 WHERE c3 = 31; +DELETE FROM mysqltest1.t4 WHERE c4 = 44; +CREATE VIEW v1 AS SELECT * FROM mysqltest1.t1; +CREATE VIEW v2 AS SELECT * FROM mysqltest1.t2; +CREATE VIEW v3 AS SELECT * FROM mysqltest1.t3; +CREATE VIEW v4 AS SELECT * FROM mysqltest1.t4; +SELECT * FROM v1; +c1 +11 +12 +13 +14 +INSERT INTO v2 VALUES(26); +UPDATE v3 SET c3 = 332 WHERE c3 = 32; +DELETE FROM v4 WHERE c4 = 43; +CREATE VIEW v12 AS SELECT c1, c2 FROM mysqltest1.t1, mysqltest1.t2; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v12' +CREATE VIEW v13 AS SELECT c1, c3 FROM mysqltest1.t1, mysqltest1.t3; +CREATE VIEW v14 AS SELECT c1, c4 FROM mysqltest1.t1, mysqltest1.t4; +CREATE VIEW v21 AS SELECT c2, c1 FROM mysqltest1.t2, mysqltest1.t1; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c1' in table 'v21' +CREATE VIEW v23 AS SELECT c2, c3 FROM mysqltest1.t2, mysqltest1.t3; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c3' in table 'v23' +CREATE VIEW v24 AS SELECT c2, c4 FROM mysqltest1.t2, mysqltest1.t4; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c4' in table 'v24' +CREATE VIEW v31 AS SELECT c3, c1 FROM mysqltest1.t3, mysqltest1.t1; +CREATE VIEW v32 AS SELECT c3, c2 FROM mysqltest1.t3, mysqltest1.t2; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v32' +CREATE VIEW v34 AS SELECT c3, c4 FROM mysqltest1.t3, mysqltest1.t4; +CREATE VIEW v41 AS SELECT c4, c1 FROM mysqltest1.t4, mysqltest1.t1; +CREATE VIEW v42 AS SELECT c4, c2 FROM mysqltest1.t4, mysqltest1.t2; +ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v42' +CREATE VIEW v43 AS SELECT c4, c3 FROM mysqltest1.t4, mysqltest1.t3; + +---> connection: default +SELECT * FROM mysqltest1.t1; +c1 +11 +12 +13 +14 +SELECT * FROM mysqltest1.t2; +c2 +21 +22 +23 +24 +25 +26 +SELECT * FROM mysqltest1.t3; +c3 +331 +332 +33 +34 +SELECT * FROM mysqltest1.t4; +c4 +41 +42 +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +DROP USER mysqltest_u1@localhost; End of 5.0 tests. diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 0785b74dd47..815b07badf8 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -350,25 +350,6 @@ 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 1143 -create view mysqltest.v3 as select b from mysqltest.t2; # Expression need select privileges -- error 1143 @@ -1035,4 +1016,96 @@ DROP USER mysqltest_u1@localhost; DROP USER mysqltest_u2@localhost; +# +# BUG#24040: Create View don't succed with "all privileges" on a database. +# + +# Prepare. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +--enable_warnings + +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; + +# Test. + +CREATE TABLE mysqltest1.t1(c1 INT); +CREATE TABLE mysqltest1.t2(c2 INT); +CREATE TABLE mysqltest1.t3(c3 INT); +CREATE TABLE mysqltest1.t4(c4 INT); + +INSERT INTO mysqltest1.t1 VALUES (11), (12), (13), (14); +INSERT INTO mysqltest1.t2 VALUES (21), (22), (23), (24); +INSERT INTO mysqltest1.t3 VALUES (31), (32), (33), (34); +INSERT INTO mysqltest1.t4 VALUES (41), (42), (43), (44); + +GRANT SELECT ON mysqltest1.t1 TO mysqltest_u1@localhost; +GRANT INSERT ON mysqltest1.t2 TO mysqltest_u1@localhost; +GRANT SELECT, UPDATE ON mysqltest1.t3 TO mysqltest_u1@localhost; +GRANT SELECT, DELETE ON mysqltest1.t4 TO mysqltest_u1@localhost; + +GRANT ALL PRIVILEGES ON mysqltest2.* TO mysqltest_u1@localhost; + +--connect (bug24040_con,localhost,mysqltest_u1,,mysqltest2) +--echo +--echo ---> connection: bug24040_con + +SELECT * FROM mysqltest1.t1; +INSERT INTO mysqltest1.t2 VALUES(25); +UPDATE mysqltest1.t3 SET c3 = 331 WHERE c3 = 31; +DELETE FROM mysqltest1.t4 WHERE c4 = 44; + +CREATE VIEW v1 AS SELECT * FROM mysqltest1.t1; +CREATE VIEW v2 AS SELECT * FROM mysqltest1.t2; +CREATE VIEW v3 AS SELECT * FROM mysqltest1.t3; +CREATE VIEW v4 AS SELECT * FROM mysqltest1.t4; + +SELECT * FROM v1; +INSERT INTO v2 VALUES(26); +UPDATE v3 SET c3 = 332 WHERE c3 = 32; +DELETE FROM v4 WHERE c4 = 43; + +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v12 AS SELECT c1, c2 FROM mysqltest1.t1, mysqltest1.t2; +CREATE VIEW v13 AS SELECT c1, c3 FROM mysqltest1.t1, mysqltest1.t3; +CREATE VIEW v14 AS SELECT c1, c4 FROM mysqltest1.t1, mysqltest1.t4; + +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v21 AS SELECT c2, c1 FROM mysqltest1.t2, mysqltest1.t1; +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v23 AS SELECT c2, c3 FROM mysqltest1.t2, mysqltest1.t3; +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v24 AS SELECT c2, c4 FROM mysqltest1.t2, mysqltest1.t4; + +CREATE VIEW v31 AS SELECT c3, c1 FROM mysqltest1.t3, mysqltest1.t1; +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v32 AS SELECT c3, c2 FROM mysqltest1.t3, mysqltest1.t2; +CREATE VIEW v34 AS SELECT c3, c4 FROM mysqltest1.t3, mysqltest1.t4; + +CREATE VIEW v41 AS SELECT c4, c1 FROM mysqltest1.t4, mysqltest1.t1; +--error ER_COLUMNACCESS_DENIED_ERROR +CREATE VIEW v42 AS SELECT c4, c2 FROM mysqltest1.t4, mysqltest1.t2; +CREATE VIEW v43 AS SELECT c4, c3 FROM mysqltest1.t4, mysqltest1.t3; + +--connection default +--echo +--echo ---> connection: default + +SELECT * FROM mysqltest1.t1; +SELECT * FROM mysqltest1.t2; +SELECT * FROM mysqltest1.t3; +SELECT * FROM mysqltest1.t4; + +# Cleanup. + +-- disconnect bug24040_con + +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +DROP USER mysqltest_u1@localhost; + + --echo End of 5.0 tests. |