diff options
5 files changed, 193 insertions, 113 deletions
diff --git a/storage/connect/mysql-test/connect/r/odbc_sqlite3.result b/storage/connect/mysql-test/connect/r/odbc_sqlite3.result index 87f7803166c..339dbb6a53d 100644 --- a/storage/connect/mysql-test/connect/r/odbc_sqlite3.result +++ b/storage/connect/mysql-test/connect/r/odbc_sqlite3.result @@ -4,20 +4,6 @@ t1 CREATE TABLE `t1` ( `Attributes` varchar(256) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='ODBC' `CATFUNC`='Drivers' SET NAMES utf8; -GRANT ALL PRIVILEGES ON *.* TO user@localhost; -REVOKE FILE ON *.* FROM user@localhost; -SELECT user(); -user() -user@localhost -CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Drivers; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Sources; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -SELECT user(); -user() -root@localhost CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='Driver=SQLite3 ODBC Driver;Database=MTR_SUITE_DIR/std_data/test.sqlite3;NoWCHAR=yes' CHARSET=utf8 DATA_CHARSET=utf8;; SHOW CREATE TABLE t1; Table Create Table @@ -31,42 +17,27 @@ test2 тест1 тест2 ÆÇÈÉË -SELECT user(); -user() -user@localhost -SELECT * FROM t1; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -INSERT INTO t1 VALUES ('xxx'); -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -DELETE FROM t1 WHERE a='xxx'; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -UPDATE t1 SET a='yyy' WHERE a='xxx'; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -TRUNCATE TABLE t1; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -ALTER TABLE t1 READONLY=1; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -CREATE VIEW v1 AS SELECT * FROM t1; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -# Testing a VIEW created with FILE privileges but accessed with no FILE -SELECT user(); -user() -root@localhost +CREATE TABLE t2 AS SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(64) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +a +test1 +test2 +тест1 +тест2 +ÆÇÈÉË +DROP TABLE t2; CREATE VIEW v1 AS SELECT * FROM t1; -SELECT user(); -user() -user@localhost SELECT * FROM v1; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -INSERT INTO v1 VALUES (2); -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -UPDATE v1 SET a=123; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -DELETE FROM v1; -ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) -SELECT user(); -user() -root@localhost +a +test1 +test2 +тест1 +тест2 +ÆÇÈÉË DROP VIEW v1; DROP TABLE t1; -DROP USER user@localhost; diff --git a/storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result b/storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result new file mode 100644 index 00000000000..87f7803166c --- /dev/null +++ b/storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result @@ -0,0 +1,72 @@ +Table Create Table +t1 CREATE TABLE `t1` ( + `Description` varchar(128) NOT NULL, + `Attributes` varchar(256) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='ODBC' `CATFUNC`='Drivers' +SET NAMES utf8; +GRANT ALL PRIVILEGES ON *.* TO user@localhost; +REVOKE FILE ON *.* FROM user@localhost; +SELECT user(); +user() +user@localhost +CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Drivers; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Sources; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +SELECT user(); +user() +root@localhost +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='Driver=SQLite3 ODBC Driver;Database=MTR_SUITE_DIR/std_data/test.sqlite3;NoWCHAR=yes' CHARSET=utf8 DATA_CHARSET=utf8;; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(64) DEFAULT NULL +) ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='Driver=SQLite3 ODBC Driver;Database=MTR_SUITE_DIR/std_data/test.sqlite3;NoWCHAR=yes' `TABLE_TYPE`='ODBC' `DATA_CHARSET`='utf8' +SELECT * FROM t1; +a +test1 +test2 +тест1 +тест2 +ÆÇÈÉË +SELECT user(); +user() +user@localhost +SELECT * FROM t1; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +INSERT INTO t1 VALUES ('xxx'); +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +DELETE FROM t1 WHERE a='xxx'; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +UPDATE t1 SET a='yyy' WHERE a='xxx'; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +TRUNCATE TABLE t1; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +ALTER TABLE t1 READONLY=1; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +CREATE VIEW v1 AS SELECT * FROM t1; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +# Testing a VIEW created with FILE privileges but accessed with no FILE +SELECT user(); +user() +root@localhost +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT user(); +user() +user@localhost +SELECT * FROM v1; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +INSERT INTO v1 VALUES (2); +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +UPDATE v1 SET a=123; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +DELETE FROM v1; +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +SELECT user(); +user() +root@localhost +DROP VIEW v1; +DROP TABLE t1; +DROP USER user@localhost; diff --git a/storage/connect/mysql-test/connect/t/have_odbc_sqlite3.inc b/storage/connect/mysql-test/connect/t/have_odbc_sqlite3.inc new file mode 100644 index 00000000000..9528e00ff56 --- /dev/null +++ b/storage/connect/mysql-test/connect/t/have_odbc_sqlite3.inc @@ -0,0 +1,15 @@ +--disable_query_log +--error 0,ER_UNKNOWN_ERROR +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Drivers; +if ($mysql_errno) +{ + Skip No ODBC support; +} +if (!`SELECT count(*) FROM t1 WHERE Description='SQLite3 ODBC Driver'`) +{ + DROP TABLE t1; + Skip Need SQLite3 ODBC Driver; +} +SHOW CREATE TABLE t1; +DROP TABLE t1; +--enable_query_log diff --git a/storage/connect/mysql-test/connect/t/odbc_sqlite3.test b/storage/connect/mysql-test/connect/t/odbc_sqlite3.test index 5f6abb0e2bc..f32196e3f04 100644 --- a/storage/connect/mysql-test/connect/t/odbc_sqlite3.test +++ b/storage/connect/mysql-test/connect/t/odbc_sqlite3.test @@ -1,3 +1,5 @@ +--source have_odbc_sqlite3.inc + # # To run this test, install SQLite3 ODBC Driver from # http://www.ch-werner.de/sqliteodbc/ @@ -18,42 +20,11 @@ # Note, the test does not need a DSN to be created. # ---disable_query_log ---error 0,ER_UNKNOWN_ERROR -CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Drivers; -if ($mysql_errno) -{ - Skip No ODBC support; -} -if (!`SELECT count(*) FROM t1 WHERE Description='SQLite3 ODBC Driver'`) -{ - DROP TABLE t1; - Skip Need SQLite3 ODBC Driver; -} -SHOW CREATE TABLE t1; -DROP TABLE t1; ---enable_query_log - SET NAMES utf8; let $MYSQLD_DATADIR= `select @@datadir`; -GRANT ALL PRIVILEGES ON *.* TO user@localhost; -REVOKE FILE ON *.* FROM user@localhost; ---connect(user,localhost,user,,) ---connection user -SELECT user(); ---error ER_ACCESS_DENIED_ERROR -CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC; ---error ER_ACCESS_DENIED_ERROR -CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Drivers; ---error ER_ACCESS_DENIED_ERROR -CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Sources; ---connection default -SELECT user(); - - # # For some reasons Windows does not allow to remove the data base # file after "DROP TABLE t1". So unlike in odbc_xls.test we won't copy @@ -66,42 +37,14 @@ let $Database=$MTR_SUITE_DIR/std_data/test.sqlite3; --replace_result $MTR_SUITE_DIR MTR_SUITE_DIR SHOW CREATE TABLE t1; SELECT * FROM t1; ---connection user -SELECT user(); ---error ER_ACCESS_DENIED_ERROR -SELECT * FROM t1; ---error ER_ACCESS_DENIED_ERROR -INSERT INTO t1 VALUES ('xxx'); ---error ER_ACCESS_DENIED_ERROR -DELETE FROM t1 WHERE a='xxx'; ---error ER_ACCESS_DENIED_ERROR -UPDATE t1 SET a='yyy' WHERE a='xxx'; ---error ER_ACCESS_DENIED_ERROR -TRUNCATE TABLE t1; ---error ER_ACCESS_DENIED_ERROR -ALTER TABLE t1 READONLY=1; ---error ER_ACCESS_DENIED_ERROR -CREATE VIEW v1 AS SELECT * FROM t1; ---echo # Testing a VIEW created with FILE privileges but accessed with no FILE ---connection default -SELECT user(); +CREATE TABLE t2 AS SELECT * FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; + CREATE VIEW v1 AS SELECT * FROM t1; ---connection user -SELECT user(); ---error ER_ACCESS_DENIED_ERROR SELECT * FROM v1; ---error ER_ACCESS_DENIED_ERROR -INSERT INTO v1 VALUES (2); ---error ER_ACCESS_DENIED_ERROR -UPDATE v1 SET a=123; ---error ER_ACCESS_DENIED_ERROR -DELETE FROM v1; ---disconnect user ---connection default -SELECT user(); DROP VIEW v1; DROP TABLE t1; - -DROP USER user@localhost; diff --git a/storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test b/storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test new file mode 100644 index 00000000000..7664a4473ba --- /dev/null +++ b/storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test @@ -0,0 +1,79 @@ +-- source include/not_embedded.inc +-- source have_odbc_sqlite3.inc + +# +# For the instructions on how to setup SQLite3 ODBC DSN, +# please see odbc_sqlite3.test +# + +SET NAMES utf8; + +let $MYSQLD_DATADIR= `select @@datadir`; + + +GRANT ALL PRIVILEGES ON *.* TO user@localhost; +REVOKE FILE ON *.* FROM user@localhost; +--connect(user,localhost,user,,) +--connection user +SELECT user(); +--error ER_ACCESS_DENIED_ERROR +CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC; +--error ER_ACCESS_DENIED_ERROR +CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Drivers; +--error ER_ACCESS_DENIED_ERROR +CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Sources; +--connection default +SELECT user(); + + +# +# For some reasons Windows does not allow to remove the data base +# file after "DROP TABLE t1". So unlike in odbc_xls.test we won't copy +# the data file, we'll use directly the file in std_data. +# As we do not do any modifications in the database, this should be OK. +# +let $Database=$MTR_SUITE_DIR/std_data/test.sqlite3; +--replace_result $MTR_SUITE_DIR MTR_SUITE_DIR +--eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='Driver=SQLite3 ODBC Driver;Database=$Database;NoWCHAR=yes' CHARSET=utf8 DATA_CHARSET=utf8; +--replace_result $MTR_SUITE_DIR MTR_SUITE_DIR +SHOW CREATE TABLE t1; +SELECT * FROM t1; +--connection user +SELECT user(); +--error ER_ACCESS_DENIED_ERROR +SELECT * FROM t1; +--error ER_ACCESS_DENIED_ERROR +INSERT INTO t1 VALUES ('xxx'); +--error ER_ACCESS_DENIED_ERROR +DELETE FROM t1 WHERE a='xxx'; +--error ER_ACCESS_DENIED_ERROR +UPDATE t1 SET a='yyy' WHERE a='xxx'; +--error ER_ACCESS_DENIED_ERROR +TRUNCATE TABLE t1; +--error ER_ACCESS_DENIED_ERROR +ALTER TABLE t1 READONLY=1; +--error ER_ACCESS_DENIED_ERROR +CREATE VIEW v1 AS SELECT * FROM t1; + +--echo # Testing a VIEW created with FILE privileges but accessed with no FILE +--connection default +SELECT user(); +CREATE VIEW v1 AS SELECT * FROM t1; +--connection user +SELECT user(); +--error ER_ACCESS_DENIED_ERROR +SELECT * FROM v1; +--error ER_ACCESS_DENIED_ERROR +INSERT INTO v1 VALUES (2); +--error ER_ACCESS_DENIED_ERROR +UPDATE v1 SET a=123; +--error ER_ACCESS_DENIED_ERROR +DELETE FROM v1; + +--disconnect user +--connection default +SELECT user(); +DROP VIEW v1; +DROP TABLE t1; + +DROP USER user@localhost; |