#
# Testing FILE privilege
#
GRANT ALL PRIVILEGES ON *.* TO user@localhost;
REVOKE FILE ON *.* FROM user@localhost;
SELECT user();
user()
user@localhost
CREATE TABLE t1 (
path VARCHAR(256) NOT NULL flag=1,
fname VARCHAR(256) NOT NULL,
ftype CHAR(4) NOT NULL,
size DOUBLE(12,0) NOT NULL flag=5
) ENGINE=CONNECT TABLE_TYPE=DIR FILE_NAME='*.*';
ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
SELECT user();
user()
root@localhost
CREATE TABLE t1 (
path VARCHAR(256) NOT NULL flag=1,
fname VARCHAR(256) NOT NULL,
ftype CHAR(4) NOT NULL,
size DOUBLE(12,0) NOT NULL flag=5
) ENGINE=CONNECT TABLE_TYPE=DIR FILE_NAME='*.*';
SELECT fname, ftype, size FROM t1 WHERE size>0;
fname	ftype	size
t1	.frm	8654
SELECT user();
user()
user@localhost
SELECT * FROM t1;
ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
INSERT INTO t1 VALUES ();
ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
DELETE FROM t1 WHERE path='xxx';
ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
UPDATE t1 SET path='yyy' WHERE path='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 (1,1,1,1);
ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
UPDATE v1 SET path=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;
#
# Testing FILE privileges done
#
CREATE TABLE t1 (
path VARCHAR(256) NOT NULL flag=1,
fname VARCHAR(256) NOT NULL,
ftype CHAR(4) NOT NULL,
size DOUBLE(12,0) NOT NULL flag=5
) ENGINE=CONNECT TABLE_TYPE=DIR FILE_NAME='*.txt'
  OPTION_LIST='subdir=1';
SELECT * FROM t1;
path	fname	ftype	size
SELECT fname, ftype, size FROM t1 ORDER BY fname, ftype, size;
fname	ftype	size
boys	.txt	282
boys2	.txt	282
boyswin	.txt	288
ALTER TABLE t1 OPTION_LIST='subdir=0';
Warnings:
Warning	1105	The current version of CONNECT did not check what you changed in ALTER. Use at your own risk
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `path` varchar(256) NOT NULL `flag`=1,
  `fname` varchar(256) NOT NULL,
  `ftype` char(4) NOT NULL,
  `size` double(12,0) NOT NULL `flag`=5
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=DIR `FILE_NAME`='*.txt' `OPTION_LIST`='subdir=0'
SELECT fname, ftype, size FROM t1 ORDER BY fname, ftype, size;
fname	ftype	size
boys	.txt	282
boyswin	.txt	288
INSERT INTO t1 VALUES ('','','','');
ERROR HY000: Got error 174 'COLBLK SetBuffer: undefined Access Method' from CONNECT
DROP TABLE t1;
CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=DIR FILE_NAME='*.txt';
ERROR HY000: Cannot get column info for table type DIR